Update with subselect sometimes returns wrong result

From: Oliver Seemann <oseemann(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Update with subselect sometimes returns wrong result
Date: 2013-11-29 23:08:14
Message-ID: CANCipfpfzoYnOz5jj=UZ70_R=CwDHv36dqWSpwsi27vpm1z5sA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

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.

Oliver

Attachment Content-Type Size
pgbug.py text/x-python 1.9 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Oliver Seemann 2013-11-30 07:32:44 Re: Update with subselect sometimes returns wrong result
Previous Message smixap 2013-11-29 17:54:53 BUG #8641: sspi language (or codepage) problem