Re: Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers

On Wed, Nov 9, 2011 at 1:56 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:
> Hi,
>
> Consider the following sequence of events:
>
> s1 #> CREATE SCHEMA test_schema;
>
> s1 #> CREATE TABLE test_schema.c1(x int);
>
> Now open another session s2 and via gdb issue a breakpoint on
> heap_create_with_catalog() which is called by DefineRelation().
>
> s2 #> CREATE TABLE test_schema.c2(y int);
>
> The above will break on the function. Now issue a drop schema in session s1
>
> s1 #> DROP SCHEMA test_schema CASCADE;
> NOTICE:  drop cascades to table test_schema.c1
> DROP SCHEMA
>
> Continuing in gdb, also completes the creation of c2 table without any
> errors. We are now left with a dangling entry in pg_class along with all the
> corresponding data files in our data directory. The problem becomes worse if
> c2 was created using a TABLESPACE. Now dropping of that tablespace does not
> work at all. Am sure we can come up with myriad such other issues.

Hmm. Does this break pg_dump? I have reported a bug whereby dangling
pg_class entries point to a namespace that has since been dropped in
the past (and has been reported many times before that, even).

The bug report is here, whereby I also aggregate other similar bug
reports that have taken place over a very long period of time:

http://archives.postgresql.org/pgsql-bugs/2011-02/msg00185.php

Given that the schema is successfully dropped, yet another table is
created presumably using this already-resolved schema OID, it seems
like it would run into this...

You could run this query, which should return 0, but may not in your case:

select count(distinct typnamespace) from pg_type where not exists
(select 1 from pg_namespace where oid = pg_type.typnamespace);

--
fdr