Re: locks in CREATE TRIGGER, ADD FK

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

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? One may suppose that someone
doing SELECT FOR UPDATE intends an UPDATE. (No, don't tell me about
foreign keys. Alvaro is going to fix that.)

As Chris suggests nearby, this is really only the tip of the iceberg.
I would prefer to see someone do a survey of all our DDL commands and
put forward a coherent proposal for minimum required locks for all of
them.

regards, tom lane