Re: [PATCH] Proposal: Allow reads to proceed during FK/trigger drops by reducing relation-level lock from AccessExclusive to ShareRowExclusive

Shayon Mukherjee <shayonj@gmail.com> writes:
> Following up on the previous thread - I took a stab at trying to see what a
> full patch for the proposal to reduce lock levels during FK/trigger drops
> would look like, and this is what I ended up with.

I don't think this is safe, at least not for FK removal.  There's a
comment in AlterTableGetLockLevel explaining why:

                /*
                 * Removing constraints can affect SELECTs that have been
                 * optimized assuming the constraint holds true. See also
                 * CloneFkReferenced.
                 */

Adding a foreign key can (and I think does) take a lesser lock,
because the additional constraint won't invalidate any proofs the
optimizer may have made beforehand.  But dropping one seems
problematic.

Another issue is that the proposed patch looks like it reduces
the locking level for more types of constraints than just FKs.
That would require further analysis, but I believe that (for
example) dropping a unique constraint likewise risks breaking
existing query plans, even when they aren't directly using that
index.