Re: LISTEN and "tuple concurrently updated"

Gavin Sherry <swm@linuxworld.com.au> writes:
> ERROR:  tuple concurrently updated

> A brief look into this:

> heap_update() in T3 (called by AtCommit_Notify()) calls
> HeapTupleSatisfiesUpdate(). This returns HeapTupleBeingUpdated. Once we
> issue COMMIT; in T1 updates pg_listen and the tuple T3 is trying to
> update no longer exists.

Ugh.

> I've attached a patch which solves this problem. Basically, T1 will now
> just hold AccessExclusiveLock on pg_listen for the rest of the
> transaction.

That seems quite unworkable --- it creates the potential for deadlock,
and in any case the exclusive lock could be held for an unreasonably
long time.

> I've also modified AsyncExistsPendingNotify() to step through
> pg_listen which allows T3's NOTIFY to block until T1 commits. This is not
> really necessary due to the semantics of LISTEN/NOTIFY -- it is not an
> error if a record exists in pg_listen already.

This appears to turn AtCommit_Notify into an O(N^2) operation, which
doesn't strike me as a pleasant answer at all.  I think it also breaks
the semantics of the other caller, Async_Notify.

What we probably need to do instead of this is not use
simple_heap_update in AtCommit_Notify; instead we have to use
heap_update directly and cope with concurrent-update situations.
The simple_heap_delete calls may need work too, now that I think
about it ...
        regards, tom lane