BUG #3833: Index remains when table is dropped

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

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