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

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...

Greetings,

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