From: | "Jim C(dot) Nasby" <jim(at)nasby(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY |
Date: | 2003-06-12 19:24:57 |
Message-ID: | 20030612192457.GO40542@flake.decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?"
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2003-06-12 20:57:55 | Re: [HACKERS] SAP and MySQL ... [and Benchmark] |
Previous Message | Sean Chittenden | 2003-06-12 19:01:00 | Re: CVS -Tip compile issue -- FreeBSD 4.8 |