Hello all,
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)
UPDATE 1
Kind regards,