You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Each sub-query in "WITH" runs on the same "snapshot" of the table in postgresql, but not in pg-mem
I have a table that stores user balances.
When user balance is subtracted it should first subtract from balance.bonus.
Only when balance.bonus became 0 balance.main should be decreased.
And I also must know how many was subtracted.
And I must do it in one query to avoid data races.
I solve this using "WITH", but this query give a different result in postgres and pg-mem.
UPDATE part work well in both cases, but result of SELECT differ.
In postgres it perform calculation using old balances (before UPDATE operation is run), in pg-mem however it perform calculation using new balances (after UPDATE operation is run) .
To Reproduce
result in postgress
{ main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}
result in pg-mem
{ main: 140 bonus: 0, mainDiff: 100 , bonusDiff: 0}
CREATETABLE "balance" (
"id"SERIALNOT NULL,
"main"integerNOT NULL DEFAULT 0,
"bonus"integerNOT NULL DEFAULT 0,
"userId"integer,
CONSTRAINT"REL_9297a70b26dc787156fa49de26" UNIQUE ("userId"),
CONSTRAINT"PK_079dddd31a81672e8143a649ca0"PRIMARY KEY ("id")
);
INSERT INTO"balance" ("main", "bonus", "userId") VALUES (200, 60, 1);
WITH
sel AS (
SELECT
(CASE
WHEN 100<= balance."bonus" THEN 0
ELSE 100- balance."bonus"
END) AS"mainDiff",
(CASE
WHEN 100<= balance."bonus" THEN 100
ELSE balance."bonus"
END) AS"bonusDiff"FROM"balance"WHERE"userId"=1
),
upd AS (
UPDATE"balance"SET"main"=
(CASE
WHEN 100<= balance."bonus" THEN balance."main"
ELSE balance."main"-100+ balance."bonus"
END),
"bonus"=
(CASE
WHEN 100<= balance."bonus" THEN balance."bonus"-100
ELSE 0
END)
WHERE"userId"=1
RETURNING "main", "bonus"
)
SELECT*FROM sel, upd LIMIT1;
But if i change last line to FROM upd, sel LIMIT 1, then pg-mem will give me correct result (the same as postgresql)
result in postgress
{ main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}
result in pg-mem
{ main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}
CREATETABLE "balance" (
"id"SERIALNOT NULL,
"main"integerNOT NULL DEFAULT 0,
"bonus"integerNOT NULL DEFAULT 0,
"userId"integer,
CONSTRAINT"REL_9297a70b26dc787156fa49de26" UNIQUE ("userId"),
CONSTRAINT"PK_079dddd31a81672e8143a649ca0"PRIMARY KEY ("id")
);
INSERT INTO"balance" ("main", "bonus", "userId") VALUES (200, 60, 1);
WITH
sel AS (
SELECT
(CASE
WHEN 100<= balance."bonus" THEN 0
ELSE 100- balance."bonus"
END) AS"mainDiff",
(CASE
WHEN 100<= balance."bonus" THEN 100
ELSE balance."bonus"
END) AS"bonusDiff"FROM"balance"WHERE"userId"=1
),
upd AS (
UPDATE"balance"SET"main"=
(CASE
WHEN 100<= balance."bonus" THEN balance."main"
ELSE balance."main"-100+ balance."bonus"
END),
"bonus"=
(CASE
WHEN 100<= balance."bonus" THEN balance."bonus"-100
ELSE 0
END)
WHERE"userId"=1
RETURNING "main", "bonus"
)
SELECT*FROM upd, sel LIMIT1;
pg-mem version
2.7.4
The text was updated successfully, but these errors were encountered:
Igggr
changed the title
pg-mem behaves differently from postgres
The result of a WITH-query in pg-mum depends on the sequence of queries
Apr 23, 2024
Igggr
changed the title
The result of a WITH-query in pg-mum depends on the sequence of queries
The result of a WITH-query in pg-mem depends on the sequence of queries
Apr 23, 2024
Each sub-query in "WITH" runs on the same "snapshot" of the table in postgresql, but not in pg-mem
I have a table that stores user balances.
When user balance is subtracted it should first subtract from balance.bonus.
Only when balance.bonus became 0 balance.main should be decreased.
And I also must know how many was subtracted.
And I must do it in one query to avoid data races.
I solve this using "WITH", but this query give a different result in postgres and pg-mem.
UPDATE part work well in both cases, but result of SELECT differ.
In postgres it perform calculation using old balances (before UPDATE operation is run), in pg-mem however it perform calculation using new balances (after UPDATE operation is run) .
To Reproduce
result in postgress
{ main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}
result in pg-mem
{ main: 140 bonus: 0, mainDiff: 100 , bonusDiff: 0}
But if i change last line to
FROM upd, sel LIMIT 1
, then pg-mem will give me correct result (the same as postgresql)result in postgress
{ main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}
result in pg-mem
{ main: 140 bonus: 0, mainDiff: 40 , bonusDiff: 60}
pg-mem version
2.7.4
The text was updated successfully, but these errors were encountered: