Hi,
On 2012-10-31 11:41:37 +0530, Amit Kapila wrote:
> There seems to be a problem in behavior of Create Index Concurrently and Hot
> Update in HEAD code .
At pgcon.it I had a chance to discuss with Simon how to fix this
bug. Please check the attached patches - and their commit messages - for
the fix and some regression tests.
The fix contains at least one FIXME where I am not sure if we need to do
something additional and the locking behaviour deserves some close
review.
Opinions?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
>From e2ec19d8567a238bea617afc41f65dbf4501d315 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Sat, 24 Nov 2012 13:42:49 +0100
Subject: [PATCH 1/2] Don't ignore !indisvalid && !indisready indexes in
RelationGetIndexList
The previous behaviour - introduced in 8cb53654dbdb4c386369eb988062d0bbb6de725e
- was incorrect because it caused caused RelationGetIndexAttrBitmap to ignore
indexes which are currently being built with CREATE INDEX CONCURRENTLY. Due to
this HOT updates were being used even when columns in the currently-building
index differed. As validate_index_heapscan always inserts the root tuple of a
HOT chain this leads to the update being missed.
Instead add code in all callers of RelationGetIndexList to check whether the
index is in a suitable state. Some of those checks should have been made anyway
because indexes where only skipped if not valid *and* not ready. So, parts of
this commit should be backpatched even to branches that don't have DROP INDEX
CONCURRENTLY.
To keep DROP INDEX CONCURRENTLY working we need to take care that we don't lock
indexes during routine operations before testing ->indisready. It is safe to
lock the index checking this as D.I.C takes care to wait for all backends
possibly seeing an incorrect state and normal operations acquire a conflicting
lock on the heap relation itself.
This fixes the bug found by Amit Kapila that updates can be missing from an
index build with CREATE INDEX CONCURRENTLY.
---
contrib/tcn/tcn.c | 3 ++-
src/backend/catalog/index.c | 4 +++-
src/backend/commands/cluster.c | 7 +++++--
src/backend/commands/indexcmds.c | 1 +
src/backend/commands/tablecmds.c | 4 +++-
src/backend/commands/vacuum.c | 25 ++++++++++++++++++++++++-
src/backend/executor/execUtils.c | 17 ++++++++++++++---
src/backend/optimizer/util/plancat.c | 7 ++++++-
src/backend/parser/parse_utilcmd.c | 10 ++++++++++
src/backend/utils/cache/relcache.c | 23 +++++++++++++++--------
10 files changed, 83 insertions(+), 18 deletions(-)
diff --git a/contrib/tcn/tcn.c b/contrib/tcn/tcn.c
index 6a8a96f..4b194bc 100644
--- a/contrib/tcn/tcn.c
+++ b/contrib/tcn/tcn.c
@@ -142,7 +142,8 @@ triggered_change_notification(PG_FUNCTION_ARGS)
elog(ERROR, "cache lookup failed for index %u", indexoid);
index = (Form_pg_index) GETSTRUCT(indexTuple);
/* we're only interested if it is the primary key */
- if (index->indisprimary)
+
+ if (index->indisvalid && index->indisprimary)
{
int numatts = index->indnatts;
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index d2d91c1..2d298de 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -144,11 +144,13 @@ relationHasPrimaryKey(Relation rel)
{
Oid indexoid = lfirst_oid(indexoidscan);
HeapTuple indexTuple;
+ Form_pg_index indexform;
indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
if (!HeapTupleIsValid(indexTuple)) /* should not happen */
elog(ERROR, "cache lookup failed for index %u", indexoid);
- result = ((Form_pg_index) GETSTRUCT(indexTuple))->indisprimary;
+ indexform = (Form_pg_index) GETSTRUCT(indexTuple);
+ result = indexform->indisprimary && indexform->indisvalid;
ReleaseSysCache(indexTuple);
if (result)
break;
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index de71a35..9bb47d2 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -139,7 +139,7 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
if (!HeapTupleIsValid(idxtuple))
elog(ERROR, "cache lookup failed for index %u", indexOid);
indexForm = (Form_pg_index) GETSTRUCT(idxtuple);
- if (indexForm->indisclustered)
+ if (indexForm->indisvalid && indexForm->indisclustered)
{
ReleaseSysCache(idxtuple);
break;
@@ -477,7 +477,7 @@ mark_index_clustered(Relation rel, Oid indexOid)
elog(ERROR, "cache lookup failed for index %u", indexOid);
indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
- if (indexForm->indisclustered)
+ if (indexForm->indisvalid && indexForm->indisclustered)
{
ReleaseSysCache(indexTuple);
return;
@@ -513,6 +513,9 @@ mark_index_clustered(Relation rel, Oid indexOid)
}
else if (thisIndexOid == indexOid)
{
+ /* should be checked before, be sure */
+ if (!indexForm->indisvalid)
+ elog(ERROR, "cannot cluster on invalid index %u", indexOid);
indexForm->indisclustered = true;
simple_heap_update(pg_index, &indexTuple->t_self, indexTuple);
CatalogUpdateIndexes(pg_index, indexTuple);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index dd46cf9..0568061 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -226,6 +226,7 @@ CheckIndexCompatible(Oid oldId,
/* For polymorphic opcintype, column type changes break compatibility. */
irel = index_open(oldId, AccessShareLock); /* caller probably has a lock */
+ /* FIXME: verify index is ready & valid? */
for (i = 0; i < old_natts; i++)
{
if (IsPolymorphicType(get_opclass_input_type(classObjectId[i])) &&
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f88bf79..1136007 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6430,10 +6430,12 @@ transformFkeyCheckAttrs(Relation pkrel,
/*
* Must have the right number of columns; must be unique and not a
- * partial index; forget it if there are any expressions, too
+ * partial index; forget it if there are any expressions, too. Invalid
+ * indexes are out as well.
*/
if (indexStruct->indnatts == numattrs &&
indexStruct->indisunique &&
+ indexStruct->indisvalid &&
heap_attisnull(indexTuple, Anum_pg_index_indpred) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs))
{
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 14d1c08..d156d6b 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1113,6 +1113,7 @@ vac_open_indexes(Relation relation, LOCKMODE lockmode,
indexoidlist = RelationGetIndexList(relation);
+ /* allocate memory for all indexes */
*nindexes = list_length(indexoidlist);
if (*nindexes > 0)
@@ -1120,12 +1121,34 @@ vac_open_indexes(Relation relation, LOCKMODE lockmode,
else
*Irel = NULL;
+ /* count valid indexes */
+ *nindexes = 0;
+
i = 0;
foreach(indexoidscan, indexoidlist)
{
Oid indexoid = lfirst_oid(indexoidscan);
+ Relation indrel;
+
+ /*
+ * Don't lock index before we checked we can use it, dangerous schema
+ * changes are prevented by the relation level lock.
+ */
+ indrel = index_open(indexoid, NoLock);
+
+ /* check whether index is ready for writes */
+ if (!indrel->rd_index->indisready)
+ {
+ index_close(indrel, NoLock);
+ continue;
+ }
+
+ /* acquire appropriate lock */
+ LockRelationOid(indexoid, lockmode);
- (*Irel)[i++] = index_open(indexoid, lockmode);
+ /* return values */
+ (*Irel)[i++] = indrel;
+ (*nindexes)++;
}
list_free(indexoidlist);
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 0bbd0d4..e5cae6b 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -914,11 +914,14 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo)
Relation indexDesc;
IndexInfo *ii;
- indexDesc = index_open(indexOid, RowExclusiveLock);
+ indexDesc = index_open(indexOid, NoLock);
/* extract index key information from the index's pg_index info */
ii = BuildIndexInfo(indexDesc);
+ if (ii->ii_ReadyForInserts)
+ LockRelationOid(indexOid, RowExclusiveLock);
+
relationDescs[i] = indexDesc;
indexInfoArray[i] = ii;
i++;
@@ -939,17 +942,25 @@ ExecCloseIndices(ResultRelInfo *resultRelInfo)
int i;
int numIndices;
RelationPtr indexDescs;
+ IndexInfo **indexInfo;
numIndices = resultRelInfo->ri_NumIndices;
indexDescs = resultRelInfo->ri_IndexRelationDescs;
+ indexInfo = resultRelInfo->ri_IndexRelationInfo;
for (i = 0; i < numIndices; i++)
{
if (indexDescs[i] == NULL)
continue; /* shouldn't happen? */
- /* Drop lock acquired by ExecOpenIndices */
- index_close(indexDescs[i], RowExclusiveLock);
+ /*
+ * Drop lock acquired by ExecOpenIndices with the same strength it was
+ * opened with.
+ */
+ if (indexInfo[i]->ii_ReadyForInserts)
+ index_close(indexDescs[i], RowExclusiveLock);
+ else
+ index_close(indexDescs[i], NoLock);
}
/*
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index abcd0ee..61f9e07 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -162,8 +162,10 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
/*
* Extract info from the relation descriptor for the index.
+ *
+ * Don't lock the table before checking it's valid.
*/
- indexRelation = index_open(indexoid, lmode);
+ indexRelation = index_open(indexoid, NoLock);
index = indexRelation->rd_index;
/*
@@ -178,6 +180,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
continue;
}
+ /* Now lock the index */
+ LockRelationOid(indexoid, lmode);
+
/*
* If the index is valid, but cannot yet be used, ignore it; but
* mark the plan we are generating as transient. See
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 95c57e8..29012d4 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -868,6 +868,16 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
parent_index = index_open(parent_index_oid, AccessShareLock);
+ /*
+ * Don't clone invalid indexes, they probably are leftovers from a
+ * failed concurrent creation.
+ */
+ if (!parent_index->rd_index->indisvalid)
+ {
+ index_close(parent_index, AccessShareLock);
+ continue;
+ }
+
/* Build CREATE INDEX statement to recreate the parent_index */
index_stmt = generateClonedIndexStmt(cxt, parent_index,
attmap, tupleDesc->natts);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 8c9ebe0..abacc4a 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3357,12 +3357,6 @@ RelationGetIndexList(Relation relation)
oidvector *indclass;
bool isnull;
- /*
- * Ignore any indexes that are currently being dropped
- */
- if (!index->indisvalid && !index->indisready)
- continue;
-
/* Add index's OID to result list in the proper order */
result = insert_ordered_oid(result, index->indexrelid);
@@ -3683,7 +3677,20 @@ RelationGetIndexAttrBitmap(Relation relation)
IndexInfo *indexInfo;
int i;
- indexDesc = index_open(indexOid, AccessShareLock);
+ /*
+ * We use NoLock to open the index so we don't interfere with
+ * concurrent drops. All code manipulating indexes either needs a
+ * stronger lock on the relation anyway or takes special care not to
+ * interfere with concurrent operations like DROP INDEX CONCURRENTLY
+ * does. Also, the locks taken here are way much shorter lived than the
+ * cached relation->rd_indexattr.
+ */
+ indexDesc = index_open(indexOid, NoLock);
+
+ /*
+ * We must not skip !indisready indexes here, if an index is built
+ * concurrently we cannot allow HOT updates for the covered columns.
+ */
/* Extract index key information from the index's pg_index row */
indexInfo = BuildIndexInfo(indexDesc);
@@ -3704,7 +3711,7 @@ RelationGetIndexAttrBitmap(Relation relation)
/* Collect all attributes in the index predicate, too */
pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
- index_close(indexDesc, AccessShareLock);
+ index_close(indexDesc, NoLock);
}
list_free(indexoidlist);
--
1.7.12.289.g0ce9864.dirty
>From 770b75a5037f28e691c3e4f311d83e4dfa0b4ed2 Mon Sep 17 00:00:00 2001
From: Andres Freund <and...@anarazel.de>
Date: Fri, 23 Nov 2012 13:13:20 +0100
Subject: [PATCH 2/2] Add isolationtester tests for CREATE/DROP INDEX
CONCURRENTLY interactions
---
.../expected/create-index-concurrently-hot.out | 34 ++++++++++++++
.../expected/drop-index-concurrently-1.out | 40 ----------------
.../expected/drop-index-concurrently-block.out | 38 +++++++++++++++
.../expected/drop-index-concurrently-write.out | 40 ++++++++++++++++
src/test/isolation/isolation_schedule | 4 +-
.../specs/create-index-concurrently-hot.spec | 40 ++++++++++++++++
.../isolation/specs/drop-index-concurrently-1.spec | 38 ---------------
.../specs/drop-index-concurrently-block.spec | 54 ++++++++++++++++++++++
.../specs/drop-index-concurrently-write.spec | 38 +++++++++++++++
9 files changed, 247 insertions(+), 79 deletions(-)
create mode 100644 src/test/isolation/expected/create-index-concurrently-hot.out
delete mode 100644 src/test/isolation/expected/drop-index-concurrently-1.out
create mode 100644 src/test/isolation/expected/drop-index-concurrently-block.out
create mode 100644 src/test/isolation/expected/drop-index-concurrently-write.out
create mode 100644 src/test/isolation/specs/create-index-concurrently-hot.spec
delete mode 100644 src/test/isolation/specs/drop-index-concurrently-1.spec
create mode 100644 src/test/isolation/specs/drop-index-concurrently-block.spec
create mode 100644 src/test/isolation/specs/drop-index-concurrently-write.spec
diff --git a/src/test/isolation/expected/create-index-concurrently-hot.out b/src/test/isolation/expected/create-index-concurrently-hot.out
new file mode 100644
index 0000000..af9eab6
--- /dev/null
+++ b/src/test/isolation/expected/create-index-concurrently-hot.out
@@ -0,0 +1,34 @@
+Parsed test spec with 4 sessions
+
+starting permutation: begin insert cic s4_begin s4_lock commit up s4_commit sel sel_s sel_d sel_i_e sel_i
+step begin: BEGIN;
+step insert: INSERT INTO t1 VALUES (2, 2, 2);
+step cic: CREATE INDEX CONCURRENTLY idx_conc_t1 ON t1(c2); <waiting ...>
+step s4_begin: BEGIN;
+step s4_lock: SELECT indrelid::regclass FROM pg_index WHERE indrelid = 't1'::regclass FOR UPDATE;
+indrelid
+
+t1
+step commit: COMMIT;
+step up: UPDATE t1 SET c2=5 WHERE c1=1;
+step s4_commit: COMMIT;
+step cic: <... completed>
+step sel: SELECT * FROM t1
+c1 c2 c3
+
+2 2 2
+1 5 3
+step sel_s: SELECT * FROM t1 WHERE c2=5;
+c1 c2 c3
+
+1 5 3
+step sel_d: SET enable_seqscan = false;
+step sel_i_e: EXPLAIN (COSTS off) SELECT * FROM t1 WHERE c2=5;
+QUERY PLAN
+
+Index Scan using idx_conc_t1 on t1
+ Index Cond: (c2 = 5)
+step sel_i: SELECT * FROM t1 WHERE c2=5;
+c1 c2 c3
+
+1 5 3
diff --git a/src/test/isolation/expected/drop-index-concurrently-1.out b/src/test/isolation/expected/drop-index-concurrently-1.out
deleted file mode 100644
index 8bedf13..0000000
--- a/src/test/isolation/expected/drop-index-concurrently-1.out
+++ /dev/null
@@ -1,40 +0,0 @@
-Parsed test spec with 3 sessions
-
-starting permutation: noseq prepi preps begin explaini explains select2 drop insert2 end2 selecti selects end
-step noseq: SET enable_seqscan = false;
-step prepi: PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
-step preps: PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data;
-step begin: BEGIN;
-step explaini: EXPLAIN (COSTS OFF) EXECUTE getrow_idx;
-QUERY PLAN
-
-Sort
- Sort Key: id
- -> Index Scan using test_dc_data on test_dc
- Index Cond: (data = 34)
-step explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seq;
-QUERY PLAN
-
-Sort
- Sort Key: id, data
- -> Seq Scan on test_dc
- Filter: ((data)::text = '34'::text)
-step select2: SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
-id data
-
-34 34
-step drop: DROP INDEX CONCURRENTLY test_dc_data; <waiting ...>
-step insert2: INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100);
-step end2: COMMIT;
-step selecti: EXECUTE getrow_idx;
-id data
-
-34 34
-134 34
-step selects: EXECUTE getrow_seq;
-id data
-
-34 34
-134 34
-step end: COMMIT;
-step drop: <... completed>
diff --git a/src/test/isolation/expected/drop-index-concurrently-block.out b/src/test/isolation/expected/drop-index-concurrently-block.out
new file mode 100644
index 0000000..b630e70
--- /dev/null
+++ b/src/test/isolation/expected/drop-index-concurrently-block.out
@@ -0,0 +1,38 @@
+Parsed test spec with 6 sessions
+
+starting permutation: s1_begin s1_ds s1_sel s5_drop s2_begin s2_ds s2_sel s1_commit s3_begin s3_ds s3_sel s4_begin s4_ds s4_sel s2_commit s3_up s3_commit s6_up s4_commit
+step s1_begin: BEGIN;
+step s1_ds: SET enable_seqscan = false;
+step s1_sel: SELECT * FROM t1 WHERE c2 = -1 FOR UPDATE;
+c1 c2 c3
+
+step s5_drop: DROP INDEX CONCURRENTLY idx_conc_t1; <waiting ...>
+step s2_begin: BEGIN;
+step s2_ds: SET enable_seqscan = false;
+step s2_sel: SELECT * FROM t1 WHERE c2 = -1 FOR UPDATE;
+c1 c2 c3
+
+step s1_commit: COMMIT;
+step s3_begin: BEGIN;
+step s3_ds: SET enable_seqscan = false;
+step s3_sel: SELECT * FROM t1 WHERE c2 = 1 FOR UPDATE;
+c1 c2 c3
+
+1 1 1
+step s4_begin: BEGIN;
+step s4_ds: SET enable_seqscan = false;
+step s4_sel: SELECT * FROM t1 WHERE c2 = -1 FOR UPDATE;
+c1 c2 c3
+
+step s2_commit: COMMIT;
+step s5_drop: <... completed>
+step s3_up: UPDATE t1 SET c3 = 3 WHERE c2 = 1 RETURNING *;
+c1 c2 c3
+
+1 1 3
+step s3_commit: COMMIT;
+step s6_up: UPDATE t1 SET c3 = 3 WHERE c2 = 2 RETURNING *;
+c1 c2 c3
+
+2 2 3
+step s4_commit: COMMIT;
diff --git a/src/test/isolation/expected/drop-index-concurrently-write.out b/src/test/isolation/expected/drop-index-concurrently-write.out
new file mode 100644
index 0000000..8bedf13
--- /dev/null
+++ b/src/test/isolation/expected/drop-index-concurrently-write.out
@@ -0,0 +1,40 @@
+Parsed test spec with 3 sessions
+
+starting permutation: noseq prepi preps begin explaini explains select2 drop insert2 end2 selecti selects end
+step noseq: SET enable_seqscan = false;
+step prepi: PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
+step preps: PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data;
+step begin: BEGIN;
+step explaini: EXPLAIN (COSTS OFF) EXECUTE getrow_idx;
+QUERY PLAN
+
+Sort
+ Sort Key: id
+ -> Index Scan using test_dc_data on test_dc
+ Index Cond: (data = 34)
+step explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seq;
+QUERY PLAN
+
+Sort
+ Sort Key: id, data
+ -> Seq Scan on test_dc
+ Filter: ((data)::text = '34'::text)
+step select2: SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
+id data
+
+34 34
+step drop: DROP INDEX CONCURRENTLY test_dc_data; <waiting ...>
+step insert2: INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100);
+step end2: COMMIT;
+step selecti: EXECUTE getrow_idx;
+id data
+
+34 34
+134 34
+step selects: EXECUTE getrow_seq;
+id data
+
+34 34
+134 34
+step end: COMMIT;
+step drop: <... completed>
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 1d0770c..ce62c36 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -14,4 +14,6 @@ test: fk-contention
test: fk-deadlock
test: fk-deadlock2
test: eval-plan-qual
-test: drop-index-concurrently-1
+test: drop-index-concurrently-write
+test: drop-index-concurrently-block
+test: create-index-concurrently-hot
diff --git a/src/test/isolation/specs/create-index-concurrently-hot.spec b/src/test/isolation/specs/create-index-concurrently-hot.spec
new file mode 100644
index 0000000..3d31cdd
--- /dev/null
+++ b/src/test/isolation/specs/create-index-concurrently-hot.spec
@@ -0,0 +1,40 @@
+# Verify that HOT Updates work correctly during CREATE INDEX CONCURRENTLY.
+#
+# We schedule sessions in a way we block each phase of CIC individually so we
+# can fire an UPDATE in the problematic moment.
+
+setup
+{
+ CREATE TABLE t1(c1 int, c2 int, c3 int);
+ INSERT INTO t1(c1, c2, c3) VALUES(1,2,3);
+}
+
+teardown
+{
+ DROP TABLE t1;
+}
+
+session "s1"
+step "begin" { BEGIN; }
+step "insert" { INSERT INTO t1 VALUES (2, 2, 2); }
+step "commit" { COMMIT; }
+
+session "s2"
+step "cic" { CREATE INDEX CONCURRENTLY idx_conc_t1 ON t1(c2); }
+
+session "s3"
+step "up" { UPDATE t1 SET c2=5 WHERE c1=1; }
+step "sel" { SELECT * FROM t1 }
+step "sel_s" { SELECT * FROM t1 WHERE c2=5;}
+step "sel_d" { SET enable_seqscan = false;}
+step "sel_i_e" { EXPLAIN (COSTS off) SELECT * FROM t1 WHERE c2=5;}
+step "sel_i" { SELECT * FROM t1 WHERE c2=5;}
+
+session "s4"
+step "s4_begin" {BEGIN;}
+# lock pg_index row so inisread=true cannot be written to the heap, so we stop
+# in the right place for s3's "up" step.
+step "s4_lock" {SELECT indrelid::regclass FROM pg_index WHERE indrelid = 't1'::regclass FOR UPDATE;}
+step "s4_commit" {COMMIT;}
+
+permutation "begin" "insert" "cic" "s4_begin" "s4_lock" "commit" "up" "s4_commit" "sel" "sel_s" "sel_d" "sel_i_e" "sel_i"
diff --git a/src/test/isolation/specs/drop-index-concurrently-1.spec b/src/test/isolation/specs/drop-index-concurrently-1.spec
deleted file mode 100644
index 8ebe5e6..0000000
--- a/src/test/isolation/specs/drop-index-concurrently-1.spec
+++ /dev/null
@@ -1,38 +0,0 @@
-# DROP INDEX CONCURRENTLY
-#
-# This test shows that the concurrent write behaviour works correctly
-# with the expected output being 2 rows.
-#
-setup
-{
- CREATE TABLE test_dc(id serial primary key, data int);
- INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100);
- CREATE INDEX test_dc_data ON test_dc(data);
-}
-
-teardown
-{
- DROP TABLE test_dc;
-}
-
-session "s1"
-step "noseq" { SET enable_seqscan = false; }
-step "prepi" { PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data; }
-step "preps" { PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data; }
-step "begin" { BEGIN; }
-step "explaini" { EXPLAIN (COSTS OFF) EXECUTE getrow_idx; }
-step "explains" { EXPLAIN (COSTS OFF) EXECUTE getrow_seq; }
-step "selecti" { EXECUTE getrow_idx; }
-step "selects" { EXECUTE getrow_seq; }
-step "end" { COMMIT; }
-
-session "s2"
-setup { BEGIN; }
-step "select2" { SELECT * FROM test_dc WHERE data=34 ORDER BY id,data; }
-step "insert2" { INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100); }
-step "end2" { COMMIT; }
-
-session "s3"
-step "drop" { DROP INDEX CONCURRENTLY test_dc_data; }
-
-permutation "noseq" "prepi" "preps" "begin" "explaini" "explains" "select2" "drop" "insert2" "end2" "selecti" "selects" "end"
diff --git a/src/test/isolation/specs/drop-index-concurrently-block.spec b/src/test/isolation/specs/drop-index-concurrently-block.spec
new file mode 100644
index 0000000..c41fc8d
--- /dev/null
+++ b/src/test/isolation/specs/drop-index-concurrently-block.spec
@@ -0,0 +1,54 @@
+# Test whether DROP INDEX CONCURRENTLY actually works without block concurrent
+# changes to tables.
+#
+# FIXME: Unfortunately this blocks indefinitely if "s6_up" is blocked because
+# isolationtester currently can't wait for two sessions at the same time and we
+# are already waiting for "s5_drop".
+
+setup
+{
+ CREATE TABLE t1(c1 int, c2 int, c3 int);
+ INSERT INTO t1(c1, c2, c3) VALUES(1,1,1);
+ INSERT INTO t1(c1, c2, c3) VALUES(2,2,2);
+ INSERT INTO t1(c1, c2, c3) VALUES(3,3,3);
+ CREATE INDEX idx_conc_t1 ON t1(c1);
+ CREATE INDEX idx_conc_t2 ON t1(c2);
+}
+
+teardown
+{
+ DROP TABLE t1;
+}
+
+session "s1"
+step "s1_begin" { BEGIN; }
+step "s1_ds" { SET enable_seqscan = false;}
+step "s1_sel" { SELECT * FROM t1 WHERE c2 = -1 FOR UPDATE;}
+step "s1_commit" { COMMIT; }
+
+session "s2"
+step "s2_begin" { BEGIN; }
+step "s2_ds" { SET enable_seqscan = false;}
+step "s2_sel" { SELECT * FROM t1 WHERE c2 = -1 FOR UPDATE;}
+step "s2_commit" { COMMIT; }
+
+session "s3"
+step "s3_begin" { BEGIN; }
+step "s3_ds" { SET enable_seqscan = false;}
+step "s3_sel" { SELECT * FROM t1 WHERE c2 = 1 FOR UPDATE;}
+step "s3_up" { UPDATE t1 SET c3 = 3 WHERE c2 = 1 RETURNING *;}
+step "s3_commit" { COMMIT; }
+
+session "s4"
+step "s4_begin" { BEGIN; }
+step "s4_ds" { SET enable_seqscan = false;}
+step "s4_sel" { SELECT * FROM t1 WHERE c2 = -1 FOR UPDATE;}
+step "s4_commit" { COMMIT; }
+
+session "s5"
+step "s5_drop" { DROP INDEX CONCURRENTLY idx_conc_t1; }
+
+session "s6"
+step "s6_up" { UPDATE t1 SET c3 = 3 WHERE c2 = 2 RETURNING *;}
+
+permutation "s1_begin" "s1_ds" "s1_sel" "s5_drop" "s2_begin" "s2_ds" "s2_sel" "s1_commit" "s3_begin" "s3_ds" "s3_sel" "s4_begin" "s4_ds" "s4_sel" "s2_commit" "s3_up" "s3_commit" "s6_up""s4_commit"
diff --git a/src/test/isolation/specs/drop-index-concurrently-write.spec b/src/test/isolation/specs/drop-index-concurrently-write.spec
new file mode 100644
index 0000000..8ebe5e6
--- /dev/null
+++ b/src/test/isolation/specs/drop-index-concurrently-write.spec
@@ -0,0 +1,38 @@
+# DROP INDEX CONCURRENTLY
+#
+# This test shows that the concurrent write behaviour works correctly
+# with the expected output being 2 rows.
+#
+setup
+{
+ CREATE TABLE test_dc(id serial primary key, data int);
+ INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100);
+ CREATE INDEX test_dc_data ON test_dc(data);
+}
+
+teardown
+{
+ DROP TABLE test_dc;
+}
+
+session "s1"
+step "noseq" { SET enable_seqscan = false; }
+step "prepi" { PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data; }
+step "preps" { PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data; }
+step "begin" { BEGIN; }
+step "explaini" { EXPLAIN (COSTS OFF) EXECUTE getrow_idx; }
+step "explains" { EXPLAIN (COSTS OFF) EXECUTE getrow_seq; }
+step "selecti" { EXECUTE getrow_idx; }
+step "selects" { EXECUTE getrow_seq; }
+step "end" { COMMIT; }
+
+session "s2"
+setup { BEGIN; }
+step "select2" { SELECT * FROM test_dc WHERE data=34 ORDER BY id,data; }
+step "insert2" { INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100); }
+step "end2" { COMMIT; }
+
+session "s3"
+step "drop" { DROP INDEX CONCURRENTLY test_dc_data; }
+
+permutation "noseq" "prepi" "preps" "begin" "explaini" "explains" "select2" "drop" "insert2" "end2" "selecti" "selects" "end"
--
1.7.12.289.g0ce9864.dirty
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
