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(dot)albe(at)wien(dot)gv(dot)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(at)commandprompt(dot)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