Thread: CREATE TRIGGER locking

Last year, I questioned why CREATE TRIGGER acquires an
AccessExclusiveLock on its target table:

    http://archives.postgresql.org/pgsql-hackers/2005-03/msg00764.php

Acquiring an ExclusiveLock should be sufficient: we can safely allow
concurrent SELECTs on the table. (The -hackers thread discusses both
CREATE TRIGGER and ALTER TABLE ADD FK; the latter might require some
more consideration, so I'll tackle that later.)

This patch implements this change, and updates the documentation.

Barring any objections, I'll apply this in a day or two.

-Neil

Neil Conway <neilc@samurai.com> writes:
> Last year, I questioned why CREATE TRIGGER acquires an
> AccessExclusiveLock on its target table:
>     http://archives.postgresql.org/pgsql-hackers/2005-03/msg00764.php
> Acquiring an ExclusiveLock should be sufficient: we can safely allow
> concurrent SELECTs on the table.

After re-reading that whole thread, I remain just as uncomfortable as
I was here:
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00819.php

You haven't proposed any use-case at all that justifies taking risks
with insufficient locking of DDL changes.  It's too early in the morning
for me to think of a specific counterexample, but I think the general
line of "TX A starts to work with a table, and then the schema changes
while it's still active" is relevant.  Another problem is that a
transaction that issues CREATE TRIGGER and then some other
schema-changing operation on the same table would likely find itself
trying to upgrade lock from ExclusiveLock to AccessExclusiveLock,
with a very strong chance of deadlock.  This latter scenario seems more
probable to me than the case of "I need to add a trigger while allowing
SELECTs to proceed".

            regards, tom lane