AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are adding triggers to (the PK table, in the case of ALTER TABLE). Is this necessary? I don't see why we can't allow SELECT queries on the table to proceed while the trigger is being added.
Attached is a patch that changes both to use ShareRowExclusiveLock, and updates the documentation accordingly. I'll apply this later today, barring any objections.
-Neil
Index: doc/src/sgml/mvcc.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/mvcc.sgml,v
retrieving revision 2.47
diff -c -r2.47 mvcc.sgml
*** doc/src/sgml/mvcc.sgml 26 Feb 2005 18:37:17 -0000 2.47
--- doc/src/sgml/mvcc.sgml 23 Mar 2005 00:46:36 -0000
***************
*** 654,661 ****
</para>
<para>
! This lock mode is not automatically acquired by any
! <productname>PostgreSQL</productname> command.
</para>
</listitem>
</varlistentry>
--- 654,661 ----
</para>
<para>
! Acquired by <command>CREATE TRIGGER</command> and <command>ALTER
! TABLE ADD FOREIGN KEY</command>.
</para>
</listitem>
</varlistentry>
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.148
diff -c -r1.148 tablecmds.c
*** src/backend/commands/tablecmds.c 20 Mar 2005 22:00:52 -0000 1.148
--- src/backend/commands/tablecmds.c 23 Mar 2005 00:49:08 -0000
***************
*** 3829,3841 ****
Oid constrOid;
/*
! * Grab an exclusive lock on the pk table, so that someone doesn't
! * delete rows out from under us. (Although a lesser lock would do for
! * that purpose, we'll need exclusive lock anyway to add triggers to
! * the pk table; trying to start with a lesser lock will just create a
! * risk of deadlock.)
*/
! pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock);
/*
* Validity and permissions checks
--- 3829,3839 ----
Oid constrOid;
/*
! * Grab a lock on the pk table, so that someone doesn't delete
! * rows out from under us; ShareRowExclusive should be good
! * enough.
*/
! pkrel = heap_openrv(fkconstraint->pktable, ShareRowExclusiveLock);
/*
* Validity and permissions checks
Index: src/backend/commands/trigger.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.178
diff -c -r1.178 trigger.c
*** src/backend/commands/trigger.c 20 Mar 2005 23:40:24 -0000 1.178
--- src/backend/commands/trigger.c 23 Mar 2005 00:49:53 -0000
***************
*** 87,93 ****
ObjectAddress myself,
referenced;
! rel = heap_openrv(stmt->relation, AccessExclusiveLock);
if (stmt->constrrel != NULL)
constrrelid = RangeVarGetRelid(stmt->constrrel, false);
--- 87,98 ----
ObjectAddress myself,
referenced;
! /*
! * We need to prevent concurrent CREATE TRIGGER commands, as well
! * as concurrent table modifications (INSERT, DELETE, UPDATE), so
! * ShareRowExclusive is sufficient.
! */
! rel = heap_openrv(stmt->relation, ShareRowExclusiveLock);
if (stmt->constrrel != NULL)
constrrelid = RangeVarGetRelid(stmt->constrrel, false);
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
