Re: Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers

On Wed, Nov 9, 2011 at 4:56 AM, Nikhil Sontakke <nikkhils(at)gmail(dot)com> wrote:
> 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.
>
> Am sure other CREATE commands in this namespace will have similar issues
> when faced with a concurrent DROP SCHEMA.
>
> We definitely need some interlocking to handle this. For lack of better
> APIs, we could do a LockDatabaseObject() call in AccessShareLock mode on the
> namespace and release the same on completion of the creation of the object.
>
> Thoughts?

In general, we've been reluctant to add locking on non-table objects
for reasons of overhead. You can, for example, drop a type or
function while a query is running that depends on it (which is not
true for tables). But I think it is sensible to do it for DDL
commands, which shouldn't be frequent enough for the overhead to
matter much. When I rewrote the comment code for 9.1, I added locking
that works just this way, to prevent pg_description entries from being
orphaned; see the end of get_object_address().

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company