Re: Update with subselect sometimes returns wrong result

On 2013-12-02 22:55:30 +0100, Oliver Seemann wrote:
> 2013/12/1 Andres Freund <andres(at)2ndquadrant(dot)com>:
> > To get rid of that ambiguity, I suggest rewriting the query to look
> > like:
> > WITH locked_row AS (
> > SELECT id FROM t1 LIMIT 1 FOR UPDATE
> > )
> > UPDATE t1 SET id = t1.id
> > FROM (SELECT * FROM locked_row) locked
> > WHERE t1.id = locked.id
> > RETURNING t1.id;
>
> Thanks for looking into this and even providing a workaround!
>
> The patch you posted previously is incomplete, right? Because I can
> still trigger the problem with the patch applied on top of git master.
> (I use autovacuum_naptime = 1s to reliably trigger within 1-5 seconds).

The patch isn't for this issue, it's for something Tom noticed while
investigating it. Purely a performance optimization/fix for a
performance regression - albeit a noticeable one.
I'd judge that there's about zero chance that the issue can be fixed in
the stable branches, the likelihood of breaking other working code due
to the require semantic changes are far too great.

Greetings,

Andres Freund

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