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

Hi,

Problem 1: concurrency:

Testcase:

Session 1:
CREATE TABLE test_drop_concurrently(id serial primary key, data int);
INSERT INTO test_drop_concurrently(data) SELECT * FROM generate_series(1, 
100000);
CREATE INDEX test_drop_concurrently_data ON test_drop_concurrently(data);
BEGIN;
EXPLAIN ANALYZE SELECT * FROM test_drop_concurrently WHERE data = 34343;
SELECT * FROM test_drop_concurrently WHERE data = 34343;
(1 row)

Session 2:
BEGIN;
SELECT * FROM test_drop_concurrently WHERE data = 34343;

Session 3:
DROP INDEX CONCURRENTLY test_drop_concurrently_data;
(in-progress)

Session 2:
INSERT INTO test_drop_concurrently(data) SELECT * FROM generate_series(1, 
100000);
COMMIT;

Session 1:
SELECT * FROM test_drop_concurrently WHERE data = 34343;
(1 row)
SET enable_bitmapscan = false;
SET enable_indexscan = false;
SELECT * FROM test_drop_concurrently WHERE data = 34343;
(2 rows)

Explanation:
index_drop does:    indexForm->indisvalid = false;    /* make unusable for queries */    indexForm->indisready = false;
  /* make invisible to changes */
 

Setting indisready = false is problematic because that prevents index updates 
which in turn breaks READ COMMITTED semantics. I think there need to be one 
more phase that waits for concurrent users of the index to finish before 
setting indisready = false.


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.

Greetings,

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