Re: DROP INDEX CONCURRENTLY is not really concurrency safe & leaves around undroppable indexes

On Monday, September 24, 2012 01:37:59 PM Andres Freund wrote:
> On Monday, September 24, 2012 01:27:54 PM Andres Freund wrote:
> > Problem 2: undroppable indexes:
> > 
> >
> > Session 1:
> > CREATE TABLE test_drop_concurrently(id serial primary key, data int);
> > CREATE INDEX test_drop_concurrently_data ON test_drop_concurrently(data);
> > BEGIN;
> > EXPLAIN ANALYZE SELECT * FROM test_drop_concurrently WHERE data = 34343;
> >
> > 
> >
> > Session 2:
> > DROP INDEX CONCURRENTLY test_drop_concurrently_data;
> > <waiting>
> > ^CCancel request sent
> > ERROR:  canceling statement due to user request
> >
> > 
> >
> > Session 1:
> > ROLLBACK;
> > DROP TABLE test_drop_concurrently;
> > SELECT indexrelid, indrelid, indexrelid::regclass, indrelid::regclass,
> > indisvalid, indisready FROM pg_index WHERE indexrelid =
> > 'test_drop_concurrently_data'::regclass;
> >
> >  indexrelid | indrelid |         indexrelid          | indrelid |
> >
> > indisvalid | indisready
> > ------------+----------+-----------------------------+----------+--------
> > -- --+------------ 24703 |    24697 | test_drop_concurrently_data |
> > 24697    | f          | f
> > (1 row)
> >
> > 
> >
> > DROP INDEX test_drop_concurrently_data;
> > ERROR:  could not open relation with OID 24697
> >
> > 
> >
> > Haven't looked at this one at all.
> 
> Thats because it has to commit transactions inbetween to make the catalog 
> changes visible. Unfortunately at that point it already deleted the 
> dependencies in deleteOneObject...
Seems to be solvable with some minor reshuffling in deleteOneObject. We can 
only perform the deletion of outgoing pg_depend records *after* we have dropped 
the object with doDeletion in the concurrent case. As the actual drop of the 
relation happens in the same transaction that will then go on to drop the 
dependency records that seems to be fine.
I don't see any problems with that right now, will write a patch tomorrow. We 
will see if thats problematic...

Greetings,

Andres
-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services