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
Index: src/backend/commands/async.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/async.c,v
retrieving revision 1.99
diff -2 -c -r1.99 async.c
*** src/backend/commands/async.c 15 Sep 2003 00:26:31 -0000 1.99
--- src/backend/commands/async.c 16 Sep 2003 00:16:27 -0000
***************
*** 317,321 ****
heap_endscan(scan);
! heap_close(lRel, AccessExclusiveLock);
/*
--- 317,321 ----
heap_endscan(scan);
! heap_close(lRel, NoLock);
/*
***************
*** 869,878 ****
}
! /* Does pendingNotifies include the given relname? */
static bool
AsyncExistsPendingNotify(const char *relname)
{
! List *p;
foreach(p, pendingNotifies)
{
--- 869,903 ----
}
! /* Does pendingNotifies and pg_listen include the given relname? */
static bool
AsyncExistsPendingNotify(const char *relname)
{
! List *p;
! Relation lRel;
! bool found = false;
! HeapScanDesc scan;
! HeapTuple tuple;
!
! lRel = heap_openr(ListenerRelationName, AccessExclusiveLock);
!
! scan = heap_beginscan(lRel, SnapshotNow, 0, (ScanKey) NULL);
! while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
! {
! Form_pg_listener listener = (Form_pg_listener) GETSTRUCT(tuple);
!
! if(strncmp(NameStr(listener->relname), relname, NAMEDATALEN) == 0)
! {
! /* Found the matching tuple */
! found = true;
! break;
! }
! }
!
! heap_endscan(scan);
!
! heap_close(lRel, AccessExclusiveLock);
+ if(!found)
+ return(false);
foreach(p, pendingNotifies)
{
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
