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
Attachments:
create_trigger_locking-1.patchtext/x-patch; charset=UTF-8; name=create_trigger_locking-1.patchDownload+12-12
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