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