Re: Update with subselect sometimes returns wrong result

Robert Haas escribió:
> On Thu, Dec 19, 2013 at 9:44 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > Alvaro Herrera escribió:
> >> Actually, the original coding is better because it enables easier
> >> writing of other optimizations, such as in the attached which should
> >> also cure the performance regression noted in bug #8470.
> >
> > While trying to apply the second bit of this patch (where we try to skip
> > acquiring a lock that an ancestor subxact already holds), I noticed that
> > it doesn't work at all; moreover, the whole idea of locking a tuple
> > twice by another subaxt of the same transaction doesn't work. For
> > example:
> >
> > BEGIN;
> > SELECT tuple FOR NO KEY UPDATE;
> > SAVEPOINT f;
> > SELECT tuple FOR UPDATE;
> > ...
> >
> > This fails to acquire the second lock completely; only the NO KEY UPDATE
> > lock is stored in the tuple. The reason this happens is that
> > HeapTupleSatisfiesUpdate returns MayBeUpdated if the Xmax is a plain Xid
> > LOCK_ONLY by our own transaction. We already commented in some other
> > thread that maybe we should change this bit of HTSU behavior; but when I
> > tried to do so to enable this optimization, I found that other routines
> > die while trying to apply XactLockTableWait to the current transaction.
> > This sorta makes sense --- it means that if we want to change this, we
> > will need further surgery to callers of HTSU.
> >
> > There's another problem which is that this optimization would be
> > applicable to locks only, not updates. Given this limitation I think it
> > would be rather pointless to try to do this.
> >
> > I will keep working at the other part, involving multixacts.
>
> Did anything come of this?

I have paged out the details of all this stuff by now, but (as quoted
above) this is closely related to bug #8470. I had a patch which was
supposed to fix the performance problem, but at some point I noticed
that there was a serious problem with it, so I put it aside. (Of
course, there was also the small matter that I needed to focus on other
patches.) Now that I skim that patch again, I *think* there's a portion
of it that should be applied to 9.3 and HEAD.

I see that in bug #8470's thread I didn't post the latest version I had.
This is it (including the serious problem I mentioned above, which is
related to acquiring a lock we already own in a previos subxact, i.e.
more or less exactly the case we're trying to optimize.)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services