Is it really necessary to block reads on a table that is affected by
adding a foreign key constraint? I can see why you wouldn't want UPDATES
or INSERTS on the child table or DELETEs on the parent, but select
should be fine on both tables, no?
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.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?"

Wed, 11 Jun 2003 13:39:08 -0500
"Jim C. Nasby" <jim@nasby.net>
SELECT blocking on ALTER TABLE ADD FOREIGN KEY
"Jim C. Nasby" <jim@nasby.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

Wed, 11 Jun 2003 15:19:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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@nasby.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@nasby.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?"

Thu, 12 Jun 2003 14:24:57 -0500
"Jim C. Nasby" <jim@nasby.net>
Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY
"Jim C. Nasby" <jim@nasby.net> writes:
> Is there any ALTER that would require blocking selects?

DROP INDEX, for certain.

> Even stuff like
> drop and rename should be protected by versioning, no?

No.  System-catalog changes are always READ COMMITTED mode.

			regards, tom lane

Thu, 12 Jun 2003 18:23:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY 
On Thu, Jun 12, 2003 at 06:23:12PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > Is there any ALTER that would require blocking selects?
> 
> DROP INDEX, for certain.

Sure, but that's usually trivially fast.

> > Even stuff like
> > drop and rename should be protected by versioning, no?
> 
> No.  System-catalog changes are always READ COMMITTED mode.
 
Yeah, so the catalog changes shouldn't be visible to anyone until after
the ALTER is complete, right? Even if a transaction is set to read
uncommitted, I assume it will always read only committed data from the
catalogs...
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.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?"

Mon, 16 Jun 2003 01:17:38 -0500
"Jim C. Nasby" <jim@nasby.net>
Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY
On Mon, Jun 16, 2003 at 01:17:38 -0500,
  "Jim C. Nasby" <jim@nasby.net> wrote:
>  
> Yeah, so the catalog changes shouldn't be visible to anyone until after
> the ALTER is complete, right? Even if a transaction is set to read
> uncommitted, I assume it will always read only committed data from the
> catalogs...

Postgres doesn't have a read uncommitted mode for transactions.

Mon, 16 Jun 2003 03:14:29 -0500
Bruno Wolff III <bruno@wolff.to>
Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY
"Jim C. Nasby" <jim@nasby.net> writes:
> On Thu, Jun 12, 2003 at 06:23:12PM -0400, Tom Lane wrote:
>>> Even stuff like
>>> drop and rename should be protected by versioning, no?
>> 
>> No.  System-catalog changes are always READ COMMITTED mode.
 
> Yeah, so the catalog changes shouldn't be visible to anyone until after
> the ALTER is complete, right?

The point is that they become visible *immediately* when the ALTER
commits; if the other transaction is in the midst of some operation on
the table, it's likely to fail badly.  Locking is what we have to do
to prevent that.

An example of the sort of problem I'm afraid of is that any change in
the tuple descriptor of a table (adding or renaming a column, flipping
the NOT NULL constraint, etc) will cause replacement of the tuple
descriptor in the table's relcache entry as soon as the other backend
notices the cache-inval message from the altering backend.  This would
break any code that has a pointer to the tuple descriptor.  Now with
sufficiently draconian programming rules we could probably avoid holding
references to cached tuple descriptors anywhere ... but it would be
mighty fragile, and mistakes would lead to failures that would be nigh
impossible to replicate or debug.  Right now the rule is "you can use a
relcache entry as long as you have some kind of lock on the relation".
This is relatively easy to ensure.

			regards, tom lane

Mon, 16 Jun 2003 10:07:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SELECT blocking on ALTER TABLE ADD FOREIGN KEY 