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
|
| Список | 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
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)
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 по дате отправления: