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

Hi Andrei,

On Tue, Oct 24, 2017 at 6:53 PM, <andreigorita@gmail.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