Re: Update with subselect sometimes returns wrong result

This looks like the same issue:
http://www.postgresql.org/message-id/E1VN53g-0002Iy-Il@wrigleys.postgresql.org


2013/11/30 Oliver Seemann <oseemann@gmail.com>

> 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
>
>