Re: Update with subselect sometimes returns wrong result

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?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company