Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY

On Wed, Jun 11, 2003 at 03:19:14PM -0400, Tom Lane wrote:
> "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.

Is there any ALTER that would require blocking selects? Even stuff like
drop and rename should be protected by versioning, no?
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"