Re: CREATE TRIGGER locking

Neil Conway <neilc(at)samurai(dot)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