LISTEN and "tuple concurrently updated"

Hi all,

A user on IRC came across the following "tuple concurrently updated" error
when using LISTEN/NOTIFY intensively. The user managed to isolate the
problem and SQL generating the problem. A few sessions are required to
generate the error.

T1:
---
begin;
listen test;
commit;

T2:
---
begin;
notify test;
commit;

T1:
---
begin;
-- got notify
unlisten test;

T3:
---
begin;
notify test;
commit;
-- blocks

T1:
---
commit;

T3 then receives:

WARNING: AbortTransaction and not in in-progress state
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.

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. 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.

Thanks,

Gavin