Re: NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.

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,
Wilco Kruijer

Wilco Kruijer <wilcokruijer(at)gmail(dot)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