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
