Re: Update with subselect sometimes returns wrong result

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.

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