test=> WITH updated AS (UPDATE tmp_test SET test = 'test' WHERE id = 1 RETURNING id), inserted AS (INSERT INTO tmp_test2 (id, test) SELECT 1, 'test' WHERE NOT EXISTS (SELECT 1 FROM updated) RETURNING id) SELECT * FROM updated; id ---- 1 (1 row)
This is the expected result, but when another session is executing in parallel:
the result is:
id ---- 1 1 (2 rows)
Right. We don't actually need tmp_test2 to trigger the bug, we simply need an InitPlan on the CTE. This query:
WITH updated AS ( UPDATE tmp_test SET test = 'test' WHERE id = 1 RETURNING id ) SELECT (SELECT 1 FROM updated), * FROM updated
has the same problem. The query plan looks like this:
CTE Scan on updated (actual rows=2 loops=1) CTE updated -> Update on tmp_test (actual rows=1 loops=1) -> Index Scan using tmp_test_id_idx on tmp_test (actual rows=1 loops=1) Index Cond: (id = 1) InitPlan 2 (returns $2) -> CTE Scan on updated updated_1 (actual rows=1 loops=1)
As you said, this problem only occurs when the row changed by the CTE is concurrently UPDATEd, so I'm guessing something goes wrong with EPQ here.
