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
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 |