Re: locks in CREATE TRIGGER, ADD FK - Mailing list pgsql-hackers

Neil Conway wrote:
> 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);