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

On Tuesday, September 25, 2012 01:58:31 AM Andres Freund wrote:
> 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...
Patch attached. Review appreciated, there might be consequences of moving the
deletion of dependencies I didn't forsee.

Greetings,

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