SELECT FOR UPDATE returns zero rows with CTE

[Roman Guryanov]

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