Re: Update with subselect sometimes returns wrong result

On 2013-11-30 12:57:44 -0500, Tom Lane wrote:
> Oliver Seemann <oseemann(at)gmail(dot)com> writes:
> > 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 failed to reproduce the claimed misbehavior in git tip of any active
> branch. I'd like to think this means we fixed the problem in the last
> two months, but I don't see anything that looks like a promising candidate
> in the commit logs. Perhaps there is some important contributing factor
> you've not mentioned --- nondefault postgresql.conf settings, for
> instance.

Looks reproducable here as well, manually executing VACUUMs on the table
greatly speeds things up. Fails within seconds when doing so.

So, it looks like the limit returns more than one row, it's not updating
the same row twice.

Slightly hacked up (probably python 2 only) version of the test script
attached. I'll get to trying to write the release stuff rather then
playing with more interesting things ;)

new row at: (0,4)
updated row from (0,2) to (0,1) iter 400
deleted row at: (0,1)
deleted row at: (0,5)
new row at: (0,1)
new row at: (0,5)
updated row from (0,1) to (0,3) iter 401
deleted row at: (0,2)
deleted row at: (0,3)
new row at: (0,2)
new row at: (0,3)
updated row from (0,1) to (0,3) iter 402
deleted row at: (0,2)
deleted row at: (0,3)
new row at: (0,2)
new row at: (0,3)
updated row from (0,4) to (0,1) iter 403
deleted row at: (0,1)
deleted row at: (0,5)
new row at: (0,1)
new row at: (0,5)
updated row from (0,1) to (0,3) iter 404
deleted row at: (0,2)
deleted row at: (0,3)
new row at: (0,2)
new row at: (0,3)
updated row from (0,1) to (0,3) iter 405
deleted row at: (0,2)
deleted row at: (0,3)
new row at: (0,2)
new row at: (0,3)
updated row from (0,4) to (0,6) iter 406
...
deleted row at: (0,2)
deleted row at: (0,3)
new row at: (0,2)
new row at: (0,3)
updated row from (0,4) to (0,1) iter 447
updated row from (0,5) to (0,2) iter 447
Traceback (most recent call last):
File "/tmp/pgbug.py", line 80, in <module>
test_bug()
File "/tmp/pgbug.py", line 51, in test_bug
update(cur, i)
File "/tmp/pgbug.py", line 76, in update
assert(len(rows) == 1)
AssertionError

There's clearly something wrong. (0,4) has been updated several times,
but seems to still be visible.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services