Re: BUG #14870: wrong query results when using WITH with UPDATE

Hi Andrei,

On Tue, Oct 24, 2017 at 6:53 PM, <andreigorita(at)gmail(dot)com> wrote:

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

.m