SELECT FOR UPDATE returns zero rows with CTE

From: Roman Guryanov <r(dot)guryanov(dot)integrix(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: SELECT FOR UPDATE returns zero rows with CTE
Date: 2021-09-17 06:32:42
Message-ID: CAJaMrZaLovOobUj3TX_ONCW=-vmj1BD-3OvOpj+-4Wr3KKkZNg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello, could you check my problem.

Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution
in transaction)

If delete 'FOR UPDATE', 1 row returned
Test case:

DROP TABLE IF EXISTS t1;DROP TABLE IF EXISTS t2;CREATE TABLE t1 (_pk
serial, t1c1 integer, t1c2 integer, t1c3 text);CREATE TABLE t2 (_pk
serial, t2c1 text, t2c2 integer);insert into t1 (t1c1, t1c2, t1c3)
values(123456789, 100, 'string_value_1');insert into t2 (t2c1, t2c2)
values('string_value_2', 100);

WITH
cte1 AS (
UPDATE t1SET t1c3 = 'string_value_1'WHERE t1c1 = 123456789
returning t1c1, t1c2
),
cte2 AS (
SELECT * FROM t1
WHERE
t1c1 = 123456789
AND t1c2 = (SELECT t1c2 FROM cte1)
FOR UPDATE
)
SELECT * FROM cte2;

https://stackoverflow.com/questions/69217940/select-for-update-returns-zero-rows-with-cte

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2021-09-17 09:36:31 Re: SELECT FOR UPDATE returns zero rows with CTE
Previous Message Stacey Haysler 2021-09-17 04:56:58 Proposed French Translation of Code of Conduct Policy