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

Поиск
Список
Период
Сортировка
От Marko Tiikkaja
Тема Re: [BUGS] BUG #14870: wrong query results when using WITH with UPDATE
Дата
Msg-id CAL9smLBOO8w9j2Y0zrRcdt-Kw2hiC8Ntdwro1FDq=j+z8Q9LqA@mail.gmail.com
обсуждение исходный текст
Ответ на [BUGS] BUG #14870: wrong query results when using WITH with UPDATE  (andreigorita@gmail.com)
Ответы Re: [BUGS] BUG #14870: wrong query results when using WITH with UPDATE  (Marko Tiikkaja <marko@joh.to>)
Список pgsql-bugs
Дерево обсуждения
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Kotadiya Dhrupesh
Дата:
Сообщение: Re: [BUGS] Help me plz
Следующее
От: Vincent Lachenal
Дата:
Сообщение: Re: [BUGS] BUG #14897: Segfault on statitics SQL request