Thread: BUG #3833: Index remains when table is dropped
The following bug has been logged online: Bug reference: 3833 Logged by: Laurenz Albe Email address: laurenz.albe@wien.gv.at PostgreSQL version: 8.2.5 Operating system: RedHat Enterprise Linux 3 Description: Index remains when table is dropped Details: Two concurrent sessions perform statements against one database. The sessions are named s1 and s2 in this example. s1=> CREATE TABLE x(i integer); s2=> BEGIN; s2=> CREATE UNIQUE INDEX x_pkey ON x(i); s1=> DROP TABLE x; (Session hangs) s2=> COMMIT; Now Session s1 will unblock and succeed in dropping the table. The index, however, remains in pg_class, pg_depend, and pg_index. "DROP INDEX x_pkey" will lead to an error like this: ERROR: could not open relation with OID 65615 The OID here is the one of the dropped table.
Laurenz Albe wrote: > Two concurrent sessions perform statements against one database. The > sessions are named s1 and s2 in this example. > > s1=> CREATE TABLE x(i integer); > > s2=> BEGIN; > s2=> CREATE UNIQUE INDEX x_pkey ON x(i); > > s1=> DROP TABLE x; > (Session hangs) > > s2=> COMMIT; > > Now Session s1 will unblock and succeed in dropping the table. The index, > however, remains in pg_class, pg_depend, and pg_index. Hmm. So this is the bug that Tom mentioned on race conditions on pg_depend. Interesting. I think the solution is to add more strict locking when registering stuff in pg_depend, like the pg_shdepend code does. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes: > Hmm. So this is the bug that Tom mentioned on race conditions on > pg_depend. Yeah, I think it's a variant of the problem we've seen before: http://archives.postgresql.org/pgsql-bugs/2007-03/msg00144.php Still no nice ideas about how to fix it. regards, tom lane
I encountered this bug recently - and thought I'd have a try at seeing what might fix it. Taking an exclusive lock on the to-be-dropped table immediately (i.e in RemoveRel) seems to be enough to prevent the drop starting while an index is being created in another session. So it "fixes" the issue - possible objections that I can think of are: 1/ Not a general solution to multi session dependent drop/create of objects other than tables (unless we do 2/) 2/ Using this approach in all object dropping code may result in deadlocks (but is this worse than dangling/mangled objects?) Now, I'm conscious that there could be other show stopper reasons for *not* doing this that I have not thought of, but figured I'd post in case the idea was useful. Thoughts? Cheers Mark *** src/backend/commands/tablecmds.c.orig Wed Jan 2 13:58:05 2008 --- src/backend/commands/tablecmds.c Wed Jan 2 13:46:43 2008 *************** *** 514,519 **** --- 514,522 ---- object.objectId = relOid; object.objectSubId = 0; + //Try a lock here! + LockRelationOid(relOid, ExclusiveLock); + performDeletion(&object, behavior); }
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --------------------------------------------------------------------------- Mark Kirkwood wrote: > I encountered this bug recently - and thought I'd have a try at seeing > what might fix it. > > Taking an exclusive lock on the to-be-dropped table immediately (i.e in > RemoveRel) seems to be enough to prevent the drop starting while an > index is being created in another session. So it "fixes" the issue - > possible objections that I can think of are: > > 1/ Not a general solution to multi session dependent drop/create of > objects other than tables (unless we do 2/) > 2/ Using this approach in all object dropping code may result in > deadlocks (but is this worse than dangling/mangled objects?) > > Now, I'm conscious that there could be other show stopper reasons for > *not* doing this that I have not thought of, but figured I'd post in > case the idea was useful. Thoughts? > > Cheers > > Mark > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
The comment I have from Tom Lane on this patch is: band-aid solution to just one aspect of problem ... so I am afraid I am going to have to reject it. Sorry. --------------------------------------------------------------------------- Mark Kirkwood wrote: > I encountered this bug recently - and thought I'd have a try at seeing > what might fix it. > > Taking an exclusive lock on the to-be-dropped table immediately (i.e in > RemoveRel) seems to be enough to prevent the drop starting while an > index is being created in another session. So it "fixes" the issue - > possible objections that I can think of are: > > 1/ Not a general solution to multi session dependent drop/create of > objects other than tables (unless we do 2/) > 2/ Using this approach in all object dropping code may result in > deadlocks (but is this worse than dangling/mangled objects?) > > Now, I'm conscious that there could be other show stopper reasons for > *not* doing this that I have not thought of, but figured I'd post in > case the idea was useful. Thoughts? > > Cheers > > Mark [ text/x-patch is unsupported, treating like TEXT/PLAIN ] > *** src/backend/commands/tablecmds.c.orig Wed Jan 2 13:58:05 2008 > --- src/backend/commands/tablecmds.c Wed Jan 2 13:46:43 2008 > *************** > *** 514,519 **** > --- 514,522 ---- > object.objectId = relOid; > object.objectSubId = 0; > > + //Try a lock here! > + LockRelationOid(relOid, ExclusiveLock); > + > performDeletion(&object, behavior); > } > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > The comment I have from Tom Lane on this patch is: > > band-aid solution to just one aspect of problem ... > > so I am afraid I am going to have to reject it. Sorry. > No problem, thanks for passing along the feedback - I was primarily interested in that (as I figured there was probably a reason why this had not been tried!). Best wishes Mark