Update with subselect sometimes returns wrong result
| От | Oliver Seemann |
|---|---|
| Тема | Update with subselect sometimes returns wrong result |
| Дата | |
| Msg-id | CANCipfpfzoYnOz5jj=UZ70_R=CwDHv36dqWSpwsi27vpm1z5sA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Update with subselect sometimes returns wrong result
Re: Update with subselect sometimes returns wrong result Re: Update with subselect sometimes returns wrong result |
| Список | pgsql-bugs |
Hi!
Given the following table:
CREATE TABLE t1 (id INTEGER);
INSERT INTO t1 VALUES (0), (1);
Then the following UPDATE should return exactly one row:
UPDATE t1 SET id = t1.id
FROM (SELECT id FROM t1 LIMIT 1 FOR UPDATE) AS subset
WHERE t1.id = subset.id
RETURNING t1.id
And it does so, most of of the time. But when run repeatedly in a loop like in the attached script, then it will occasionally return 2 rows with two different id values, something the LIMIT 1 should prevent. In my tests it took from anywhere between 0 to 10 minutes and on average 1 to 2 minutes to trigger the problem.Given the following table:
CREATE TABLE t1 (id INTEGER);
INSERT INTO t1 VALUES (0), (1);
Then the following UPDATE should return exactly one row:
UPDATE t1 SET id = t1.id
FROM (SELECT id FROM t1 LIMIT 1 FOR UPDATE) AS subset
WHERE t1.id = subset.id
RETURNING t1.id
I have reproduced the issue on different machines and platforms with PG 9.3.1, 9.1.10, 9.0.14. (See file).
Interesting, and perhaps telling:
When autovacuum=off in postgresql.conf then I could not trigger the problem.
When autovacuum=off in postgresql.conf then I could not trigger the problem.
Oliver
Вложения
В списке pgsql-bugs по дате отправления: