Thread: NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.
NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.
From
Wilco Kruijer
Date:
Hello all,
Kind regards,
I found a possible bug or lacking documentation regarding the statement mentioned in the subject. When using a CTE to SELECT a row before an update, whilst locking this row so it may not change before the update is finished, then RETURNING the row before updating using a sub-query on the CTE will result in NULL values.
I cannot find documentation regarding the combination of a locking CTE and UPDATE RETURNING (Only documentation about using RETURNING inside of the CTE).
Below is a full reproduction. The last statement returns two NULL values, unlike the query before it. I personally expect the following row to be returned: (100, 200, 300).
wilco=> select version();
version
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 row)
wilco=> CREATE TABLE acc_balance (
acc_name VARCHAR NOT NULL,
balance BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (acc_name)
);
CREATE TABLE
wilco=> INSERT INTO acc_balance (acc_name, balance) VALUES ('Wilco', 100);
INSERT 0 1
wilco=> with before as (
select balance from acc_balance where acc_name = 'Wilco'
)
update acc_balance set balance = 200 where acc_name = 'Wilco'
returning balance - (select balance from before) as delta, (select balance from before limit 1) as before, balance;
delta | before | balance
-------+--------+---------
100 | 100 | 200
(1 row)
UPDATE 1
wilco=> with before as (
select balance from acc_balance where acc_name = 'Wilco' FOR UPDATE
)
update acc_balance set balance = 300 where acc_name = 'Wilco'
returning balance - (select balance from before) as delta, (select balance from before limit 1) as before, balance;
delta | before | balance
-------+--------+---------
| | 300
(1 row)
acc_name VARCHAR NOT NULL,
balance BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (acc_name)
);
CREATE TABLE
wilco=> INSERT INTO acc_balance (acc_name, balance) VALUES ('Wilco', 100);
INSERT 0 1
wilco=> with before as (
select balance from acc_balance where acc_name = 'Wilco'
)
update acc_balance set balance = 200 where acc_name = 'Wilco'
returning balance - (select balance from before) as delta, (select balance from before limit 1) as before, balance;
delta | before | balance
-------+--------+---------
100 | 100 | 200
(1 row)
UPDATE 1
wilco=> with before as (
select balance from acc_balance where acc_name = 'Wilco' FOR UPDATE
)
update acc_balance set balance = 300 where acc_name = 'Wilco'
returning balance - (select balance from before) as delta, (select balance from before limit 1) as before, balance;
delta | before | balance
-------+--------+---------
| | 300
(1 row)
UPDATE 1
Wilco Kruijer
Re: NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.
From
Tom Lane
Date:
Wilco Kruijer <wilcokruijer@gmail.com> writes:
> wilco=> with before as (
> select balance from acc_balance where acc_name = 'Wilco' FOR UPDATE
> )
> update acc_balance set balance = 300 where acc_name = 'Wilco'
> returning balance - (select balance from before) as delta, (select balance
> from before limit 1) as before, balance;
You seem to be assuming that the WITH query will run before the UPDATE
happens. As formulated, it will not, because the UPDATE proper
does not use its value. So we don't get around to executing it until
the RETURNING clause demands its value. I don't recall exactly why
FOR UPDATE causes the already-modified row to not be visible, but
if it were visible you'd get the updated balance not the original.
So you really want no-FOR-UPDATE semantics here, to see the balance
from before the query started.
regards, tom lane