From: | Andres Freund <andres(at)2ndquadrant(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, Simon Riggs <simon(at)2ndquadrant(dot)com> |
Subject: | DROP INDEX CONCURRENTLY is not really concurrency safe & leaves around undroppable indexes |
Date: | 2012-09-24 11:27:54 |
Message-ID: | 201209241327.54702.andres@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2012-09-24 11:37:59 | Re: DROP INDEX CONCURRENTLY is not really concurrency safe & leaves around undroppable indexes |
Previous Message | Amit Kapila | 2012-09-24 11:10:26 | Re: Proof of concept: auto updatable views [Review of Patch] |