Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> Is it really necessary to block reads on a table that is affected by
> adding a foreign key constraint?

It's trickier than you seem to think. The command is adding an index,
which at some point is going to affect plans for SELECTs on the table.
It might be safe --- I don't think other processes can see the index
until the ALTER commits --- but in general we do not risk doing schema
modifications on tables with less than exclusive lock.

You'd also have to think about whether this wouldn't increase the risk
of deadlocks. For example, if you are doing several ALTERs in a
transaction, what happens when a later ALTER of the same table *does*
need exclusive lock? Upgrading a lock is a sure ticket to deadlock
problems.

regards, tom lane