Обсуждение: SELECT blocking on ALTER TABLE ADD FOREIGN KEY
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?"
"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
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?"
"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
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?"
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.
"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