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

Tom Lane wrote:
> I don't think this has been adequately thought through at all ... but
> at least make it ExclusiveLock.  What is the use-case for allowing
> SELECT FOR UPDATE in parallel with this?

Ok, patch applied -- I adjusted it to use ExclusiveLock, and fleshed out
some of the comments.

-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 07:33:36 -0000
***************
*** 677,685 ****
      </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.
      </para>
         </listitem>
        </varlistentry>
--- 677,686 ----
      </para>

      <para>
!          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>
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 07:27:09 -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,3841 ----
      Oid            constrOid;

      /*
!      * 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, ExclusiveLock);

      /*
       * 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 07:25:21 -0000
***************
*** 87,93 ****
      ObjectAddress myself,
                  referenced;

!     rel = heap_openrv(stmt->relation, AccessExclusiveLock);

      if (stmt->constrrel != NULL)
          constrrelid = RangeVarGetRelid(stmt->constrrel, false);
--- 87,100 ----
      ObjectAddress myself,
                  referenced;

!     /*
!      * 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);