Re: [HACKERS] SELECT blocking on ALTER TABLE ADD FOREIGN KEY
On Thu, Jun 12, 2003 at 06:23:12PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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!)[EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT blocking on ALTER TABLE ADD FOREIGN KEY
On Mon, Jun 16, 2003 at 01:17:38 -0500, Jim C. Nasby [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SELECT blocking on ALTER TABLE ADD FOREIGN KEY
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SELECT blocking on ALTER TABLE ADD FOREIGN KEY
On Wed, Jun 11, 2003 at 03:19:14PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] 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!)[EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SELECT blocking on ALTER TABLE ADD FOREIGN KEY
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 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!)[EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SELECT blocking on ALTER TABLE ADD FOREIGN KEY
Jim C. Nasby [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
