From f30c76ce8de6a3d9e79367112a9287b576c9e9cd Mon Sep 17 00:00:00 2001
From: Neil Conway <neilc@samurai.com>
Date: Wed, 23 Mar 2005 07:44:57 +0000
Subject: [PATCH] Adjust CREATE TRIGGER and ALTER TABLE ... ADD FOREIGN KEY to
 acquire ExclusiveLock rather than AccessExclusiveLock. This will allow
 concurrent SELECT queries to proceed on the table. Per discussion with Andrew
 at SuperNews.

---
 doc/src/sgml/mvcc.sgml           |  9 +++++----
 src/backend/commands/tablecmds.c | 14 +++++++-------
 src/backend/commands/trigger.c   | 11 +++++++++--
 3 files changed, 21 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 1aeec22b66..5982c22bd3 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/mvcc.sgml,v 2.47 2005/02/26 18:37:17 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/mvcc.sgml,v 2.48 2005/03/23 07:44:56 neilc Exp $
 -->
 
  <chapter id="mvcc">
@@ -677,9 +677,10 @@ SELECT SUM(value) FROM mytab WHERE class = 2;
 	</para>
 
 	<para>
-         This lock mode is not automatically acquired on user tables by any
-         <productname>PostgreSQL</productname> command.  However it is
-         acquired on certain system catalogs in some operations.
+         Acquired by <command>CREATE TRIGGER</command> and
+         <command>ALTER TABLE ADD FOREIGN KEY</command>. This lock
+         mode can also be acquired on certain system catalogs in some
+         operations.
 	</para>
        </listitem>
       </varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0b11976db8..1a6c1f8262 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.148 2005/03/20 22:00:52 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.149 2005/03/23 07:44:57 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -3829,13 +3829,13 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
 	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.)
+	 * Grab a lock on the pk table, so that someone doesn't delete
+	 * rows out from under us. We will eventually need to add triggers
+	 * to the table, at which point we'll need to an ExclusiveLock --
+	 * therefore we grab an ExclusiveLock now to prevent possible
+	 * deadlock.
 	 */
-	pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock);
+	pkrel = heap_openrv(fkconstraint->pktable, ExclusiveLock);
 
 	/*
 	 * Validity and permissions checks
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index fd7d9afb83..55333e1f4f 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.178 2005/03/20 23:40:24 neilc Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.179 2005/03/23 07:44:57 neilc Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -87,7 +87,14 @@ CreateTrigger(CreateTrigStmt *stmt, bool forConstraint)
 	ObjectAddress myself,
 				referenced;
 
-	rel = heap_openrv(stmt->relation, AccessExclusiveLock);
+    /*
+     * We need to prevent concurrent CREATE TRIGGER commands, as well
+     * as concurrent table modifications (INSERT, DELETE, UPDATE), so
+     * acquire an ExclusiveLock -- it should be fine to allow SELECTs
+     * to proceed. We could perhaps acquire ShareRowExclusiveLock, but
+     * there seems little gain in allowing SELECT FOR UPDATE.
+     */
+	rel = heap_openrv(stmt->relation, ExclusiveLock);
 
 	if (stmt->constrrel != NULL)
 		constrrelid = RangeVarGetRelid(stmt->constrrel, false);
-- 
2.40.0