NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE. - Mailing list pgsql-bugs

From Wilco Kruijer
Subject NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.
Date
Msg-id CAHtNzahx=5mCG0+jxWL1hBxhZqcG_NCHsCvy6nQG8itnLeyYyQ@mail.gmail.com
Whole thread Raw
Responses Re: NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Dmitriy Kuzmin
Date:
Subject: Re: Startup process on a hot standby crashes with an error "invalid memory alloc request size 1073741824" while replaying "Standby/LOCK" records
Next
From: Tom Lane
Date:
Subject: Re: NULL returned when using RETURNING in main query in combination with a CTE containing FOR UPDATE.