v20-only-part-3-0003-Track-and-drop-auxiliary-indexes-in-.patch_
application/octet-stream
Filename: v20-only-part-3-0003-Track-and-drop-auxiliary-indexes-in-.patch_
Type: application/octet-stream
Part: 3
From 5ff66966a141ef775ad27161fe989955ab683ea3 Mon Sep 17 00:00:00 2001
From: Mikhail Nikalayeu <mihailnikalayeu@gmail.com>
Date: Tue, 31 Dec 2024 14:36:31 +0100
Subject: [PATCH v20-only-part-3 3/5] Track and drop auxiliary indexes in
DROP/REINDEX
During concurrent index operations, auxiliary indexes may be left as orphaned objects when errors occur (junk auxiliary indexes).
This patch improves the handling of such auxiliary indexes:
- add auxiliaryForIndexId parameter to index_create() to track dependencies between main and auxiliary indexes
- automatically drop auxiliary indexes when the main index is dropped
- delete junk auxiliary indexes properly during REINDEX operations
---
doc/src/sgml/ref/create_index.sgml | 14 ++-
doc/src/sgml/ref/reindex.sgml | 8 +-
src/backend/catalog/dependency.c | 2 +-
src/backend/catalog/index.c | 64 ++++++++++---
src/backend/catalog/pg_depend.c | 57 +++++++++++
src/backend/catalog/toasting.c | 2 +-
src/backend/commands/indexcmds.c | 35 ++++++-
src/backend/commands/tablecmds.c | 48 +++++++++-
src/include/catalog/dependency.h | 1 +
src/include/catalog/index.h | 1 +
src/test/regress/expected/create_index.out | 105 +++++++++++++++++++--
src/test/regress/sql/create_index.sql | 57 ++++++++++-
12 files changed, 358 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index e7a7a160742..298a093f554 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -668,10 +668,16 @@ Indexes:
"idx_ccaux" stir (col) INVALID
</programlisting>
- The recommended recovery
- method in such cases is to drop these indexes and try again to perform
- <command>CREATE INDEX CONCURRENTLY</command>. (Another possibility is
- to rebuild the index with <command>REINDEX INDEX CONCURRENTLY</command>).
+ The recommended recovery method in such cases is to drop the index with
+ <command>DROP INDEX</command>. The auxiliary index (suffixed with
+ <literal>ccaux</literal>) will be automatically dropped when the main
+ index is dropped. After dropping the indexes, you can try again to perform
+ <command>CREATE INDEX CONCURRENTLY</command>. (Another possibility is to
+ rebuild the index with <command>REINDEX INDEX CONCURRENTLY</command>,
+ which will also handle cleanup of any invalid auxiliary indexes.)
+ If the only invalid index is one suffixed <literal>ccaux</literal>
+ recommended recovery method is just <literal>DROP INDEX</literal>
+ for that index.
</para>
<para>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 4ed3c969012..d62791ff9c3 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -477,11 +477,15 @@ Indexes:
<literal>_ccnew</literal> or <literal>_ccaux</literal>, then it corresponds to the transient or auxiliary
index created during the concurrent operation, and the recommended
recovery method is to drop these indexes using <literal>DROP INDEX</literal>,
- then attempt <command>REINDEX CONCURRENTLY</command> again.
+ then attempt <command>REINDEX CONCURRENTLY</command> again. The auxiliary index
+ (suffixed with <literal>_ccaux</literal>) will be automatically dropped
+ along with its main index.
If the invalid index is instead suffixed <literal>_ccold</literal>,
it corresponds to the original index which could not be dropped;
the recommended recovery method is to just drop said index, since the
- rebuild proper has been successful.
+ rebuild proper has been successful. If the only
+ invalid index is one suffixed <literal>ccaux</literal> recommended
+ recovery method is just <literal>DROP INDEX</literal> for that index.
A nonzero number may be appended to the suffix of the invalid index
names to keep them unique, like <literal>_ccnew1</literal>,
<literal>_ccold2</literal>, etc.
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 18316a3968b..ab4c3e2fb4a 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -286,7 +286,7 @@ performDeletion(const ObjectAddress *object,
* Acquire deletion lock on the target object. (Ideally the caller has
* done this already, but many places are sloppy about it.)
*/
- AcquireDeletionLock(object, 0);
+ AcquireDeletionLock(object, flags);
/*
* Construct a list of objects to delete (ie, the given object plus
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 85d2b204f4d..07ca3dbc71f 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -687,6 +687,8 @@ UpdateIndexRelation(Oid indexoid,
* parent index; otherwise InvalidOid.
* parentConstraintId: if creating a constraint on a partition, the OID
* of the constraint in the parent; otherwise InvalidOid.
+ * auxiliaryForIndexId: if creating auxiliary index, the OID of the main
+ * index; otherwise InvalidOid.
* relFileNumber: normally, pass InvalidRelFileNumber to get new storage.
* May be nonzero to attach an existing valid build.
* indexInfo: same info executor uses to insert into the index
@@ -733,6 +735,7 @@ index_create(Relation heapRelation,
Oid indexRelationId,
Oid parentIndexRelid,
Oid parentConstraintId,
+ Oid auxiliaryForIndexId,
RelFileNumber relFileNumber,
IndexInfo *indexInfo,
const List *indexColNames,
@@ -775,6 +778,8 @@ index_create(Relation heapRelation,
((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0));
/* partitioned indexes must never be "built" by themselves */
Assert(!partitioned || (flags & INDEX_CREATE_SKIP_BUILD));
+ /* auxiliaryForIndexId and INDEX_CREATE_AUXILIARY are required both or neither */
+ Assert(OidIsValid(auxiliaryForIndexId) == auxiliary);
relkind = partitioned ? RELKIND_PARTITIONED_INDEX : RELKIND_INDEX;
is_exclusion = (indexInfo->ii_ExclusionOps != NULL);
@@ -1176,6 +1181,15 @@ index_create(Relation heapRelation,
recordDependencyOn(&myself, &referenced, DEPENDENCY_PARTITION_SEC);
}
+ /*
+ * Record dependency on the main index in case of auxiliary index.
+ */
+ if (OidIsValid(auxiliaryForIndexId))
+ {
+ ObjectAddressSet(referenced, RelationRelationId, auxiliaryForIndexId);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
+ }
+
/* placeholder for normal dependencies */
addrs = new_object_addresses();
@@ -1458,6 +1472,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
InvalidOid, /* indexRelationId */
InvalidOid, /* parentIndexRelid */
InvalidOid, /* parentConstraintId */
+ InvalidOid, /* auxiliaryForIndexId */
InvalidRelFileNumber, /* relFileNumber */
newInfo,
indexColNames,
@@ -1608,6 +1623,7 @@ index_concurrently_create_aux(Relation heapRelation, Oid mainIndexId,
InvalidOid, /* indexRelationId */
InvalidOid, /* parentIndexRelid */
InvalidOid, /* parentConstraintId */
+ mainIndexId, /* auxiliaryForIndexId */
InvalidRelFileNumber, /* relFileNumber */
newInfo,
indexColNames,
@@ -3820,6 +3836,7 @@ reindex_index(const ReindexStmt *stmt, Oid indexId,
heapRelation;
Oid heapId;
Oid save_userid;
+ Oid junkAuxIndexId;
int save_sec_context;
int save_nestlevel;
IndexInfo *indexInfo;
@@ -3876,6 +3893,19 @@ reindex_index(const ReindexStmt *stmt, Oid indexId,
pgstat_progress_update_multi_param(2, progress_cols, progress_vals);
}
+ /* Check for the auxiliary index for that index, it needs to dropped */
+ junkAuxIndexId = get_auxiliary_index(indexId);
+ if (OidIsValid(junkAuxIndexId))
+ {
+ ObjectAddress object;
+ object.classId = RelationRelationId;
+ object.objectId = junkAuxIndexId;
+ object.objectSubId = 0;
+ performDeletion(&object, DROP_RESTRICT,
+ PERFORM_DELETION_INTERNAL |
+ PERFORM_DELETION_QUIETLY);
+ }
+
/*
* Open the target index relation and get an exclusive lock on it, to
* ensure that no one else is touching this particular index.
@@ -4164,7 +4194,8 @@ reindex_relation(const ReindexStmt *stmt, Oid relid, int flags,
{
Relation rel;
Oid toast_relid;
- List *indexIds;
+ List *indexIds,
+ *auxIndexIds = NIL;
char persistence;
bool result = false;
ListCell *indexId;
@@ -4253,13 +4284,30 @@ reindex_relation(const ReindexStmt *stmt, Oid relid, int flags,
else
persistence = rel->rd_rel->relpersistence;
+ foreach(indexId, indexIds)
+ {
+ Oid indexOid = lfirst_oid(indexId);
+ Oid indexAm = get_rel_relam(indexOid);
+
+ /* All STIR indexes are auxiliary indexes */
+ if (indexAm == STIR_AM_OID)
+ {
+ if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
+ RemoveReindexPending(indexOid);
+ auxIndexIds = lappend_oid(auxIndexIds, indexOid);
+ }
+ }
+
/* Reindex all the indexes. */
i = 1;
foreach(indexId, indexIds)
{
Oid indexOid = lfirst_oid(indexId);
Oid indexNamespaceId = get_rel_namespace(indexOid);
- Oid indexAm = get_rel_relam(indexOid);
+
+ /* Auxiliary indexes are going to be dropped during main index rebuild */
+ if (list_member_oid(auxIndexIds, indexOid))
+ continue;
/*
* Skip any invalid indexes on a TOAST table. These can only be
@@ -4285,18 +4333,6 @@ reindex_relation(const ReindexStmt *stmt, Oid relid, int flags,
continue;
}
- if (indexAm == STIR_AM_OID)
- {
- ereport(WARNING,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("skipping reindex of auxiliary index \"%s.%s\"",
- get_namespace_name(indexNamespaceId),
- get_rel_name(indexOid))));
- if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
- RemoveReindexPending(indexOid);
- continue;
- }
-
reindex_index(stmt, indexOid, !(flags & REINDEX_REL_CHECK_CONSTRAINTS),
persistence, params);
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index c8b11f887e2..1c275ef373f 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -1035,6 +1035,63 @@ get_index_constraint(Oid indexId)
return constraintId;
}
+/*
+ * get_auxiliary_index
+ * Given the OID of an index, return the OID of its auxiliary
+ * index, or InvalidOid if there is no auxiliary index.
+ */
+Oid
+get_auxiliary_index(Oid indexId)
+{
+ Oid auxiliaryIndexOid = InvalidOid;
+ Relation depRel;
+ ScanKeyData key[3];
+ SysScanDesc scan;
+ HeapTuple tup;
+
+ /* Search the dependency table for the index */
+ depRel = table_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_refclassid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(indexId));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_refobjsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(0));
+
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ NULL, 3, key);
+
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
+
+ /*
+ * We assume any AUTO dependency on index with rel_kind
+ * of RELKIND_INDEX is that we are looking for.
+ */
+ if (deprec->classid == RelationRelationId &&
+ (deprec->deptype == DEPENDENCY_AUTO) &&
+ get_rel_relkind(deprec->objid) == RELKIND_INDEX)
+ {
+ auxiliaryIndexOid = deprec->objid;
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+ table_close(depRel, AccessShareLock);
+
+ return auxiliaryIndexOid;
+}
+
/*
* get_index_ref_constraints
* Given the OID of an index, return the OID of all foreign key
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 0ee2fd5e7de..0ee8cbf4ca6 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -319,7 +319,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
coloptions[1] = 0;
index_create(toast_rel, toast_idxname, toastIndexOid, InvalidOid,
- InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid,
indexInfo,
list_make2("chunk_id", "chunk_seq"),
BTREE_AM_OID,
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index d2b23c44e1a..e91c8f29e5b 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1260,7 +1260,7 @@ DefineIndex(Oid tableId,
indexRelationId =
index_create(rel, indexRelationName, indexRelationId, parentIndexId,
- parentConstraintId,
+ parentConstraintId, InvalidOid,
stmt->oldNumber, indexInfo, indexColNames,
accessMethodId, tablespaceId,
collationIds, opclassIds, opclassOptions,
@@ -3651,6 +3651,7 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
{
Oid indexId;
Oid auxIndexId;
+ Oid junkAuxIndexId;
Oid tableId;
Oid amId;
bool safe; /* for set_indexsafe_procflags */
@@ -4000,6 +4001,7 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
ReindexIndexInfo *newidx;
Oid newIndexId;
Oid auxIndexId;
+ Oid junkAuxIndexId;
Relation indexRel;
Relation heapRel;
Oid save_userid;
@@ -4007,6 +4009,7 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
int save_nestlevel;
Relation newIndexRel;
Relation auxIndexRel;
+ Relation junkAuxIndexRel;
LockRelId *lockrelid;
Oid tablespaceid;
@@ -4080,12 +4083,17 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
tablespaceid,
auxConcurrentName);
+ /* Search for auxiliary index for reindexed index, to drop it */
+ junkAuxIndexId = get_auxiliary_index(idx->indexId);
+
/*
* Now open the relation of the new index, a session-level lock is
* also needed on it.
*/
newIndexRel = index_open(newIndexId, ShareUpdateExclusiveLock);
auxIndexRel = index_open(auxIndexId, ShareUpdateExclusiveLock);
+ if (OidIsValid(junkAuxIndexId))
+ junkAuxIndexRel = index_open(junkAuxIndexId, ShareUpdateExclusiveLock);
/*
* Save the list of OIDs and locks in private context
@@ -4095,6 +4103,7 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
newidx = palloc_object(ReindexIndexInfo);
newidx->indexId = newIndexId;
newidx->auxIndexId = auxIndexId;
+ newidx->junkAuxIndexId = junkAuxIndexId;
newidx->safe = idx->safe;
newidx->tableId = idx->tableId;
newidx->amId = idx->amId;
@@ -4116,10 +4125,18 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
lockrelid = palloc_object(LockRelId);
*lockrelid = auxIndexRel->rd_lockInfo.lockRelId;
relationLocks = lappend(relationLocks, lockrelid);
+ if (OidIsValid(junkAuxIndexId))
+ {
+ lockrelid = palloc_object(LockRelId);
+ *lockrelid = junkAuxIndexRel->rd_lockInfo.lockRelId;
+ relationLocks = lappend(relationLocks, lockrelid);
+ }
MemoryContextSwitchTo(oldcontext);
index_close(indexRel, NoLock);
+ if (OidIsValid(junkAuxIndexId))
+ index_close(junkAuxIndexRel, NoLock);
index_close(auxIndexRel, NoLock);
index_close(newIndexRel, NoLock);
@@ -4308,7 +4325,8 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
/*
* At this moment all target indexes are marked as "ready-to-insert". So,
- * we are free to start process of dropping auxiliary indexes.
+ * we are free to start process of dropping auxiliary indexes - including
+ * just indexes detected earlier.
*/
foreach(lc, newIndexIds)
{
@@ -4331,6 +4349,9 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
*/
PushActiveSnapshot(GetTransactionSnapshot());
index_set_state_flags(newidx->auxIndexId, INDEX_DROP_CLEAR_READY);
+ /* Ensure just index is marked as non-ready */
+ if (OidIsValid(newidx->junkAuxIndexId))
+ index_set_state_flags(newidx->junkAuxIndexId, INDEX_DROP_CLEAR_READY);
PopActiveSnapshot();
CommitTransactionCommand();
@@ -4549,6 +4570,8 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
PushActiveSnapshot(GetTransactionSnapshot());
index_concurrently_set_dead(newidx->tableId, newidx->auxIndexId);
+ if (OidIsValid(newidx->junkAuxIndexId))
+ index_concurrently_set_dead(newidx->tableId, newidx->junkAuxIndexId);
PopActiveSnapshot();
}
@@ -4600,6 +4623,14 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein
object.objectSubId = 0;
add_exact_object_address(&object, objects);
+
+ if (OidIsValid(idx->junkAuxIndexId))
+ {
+
+ object.objectId = idx->junkAuxIndexId;
+ object.objectSubId = 0;
+ add_exact_object_address(&object, objects);
+ }
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 54ad38247aa..a1043c183f0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1532,6 +1532,8 @@ RemoveRelations(DropStmt *drop)
ListCell *cell;
int flags = 0;
LOCKMODE lockmode = AccessExclusiveLock;
+ MemoryContext private_context,
+ oldcontext;
/* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */
if (drop->concurrent)
@@ -1592,9 +1594,20 @@ RemoveRelations(DropStmt *drop)
relkind = 0; /* keep compiler quiet */
break;
}
+ /*
+ * Create a memory context that will survive forced transaction commits we
+ * may need to do below (in case of concurrent index drop).
+ * Since it is a child of PortalContext, it will go away eventually even if
+ * we suffer an error; there's no need for special abort cleanup logic.
+ */
+ private_context = AllocSetContextCreate(PortalContext,
+ "RemoveRelations",
+ ALLOCSET_SMALL_SIZES);
+ oldcontext = MemoryContextSwitchTo(private_context);
/* Lock and validate each relation; build a list of object addresses */
objects = new_object_addresses();
+ MemoryContextSwitchTo(oldcontext);
foreach(cell, drop->objects)
{
@@ -1646,6 +1659,34 @@ RemoveRelations(DropStmt *drop)
flags |= PERFORM_DELETION_CONCURRENTLY;
}
+ /*
+ * Concurrent index drop requires to be the first transaction. But in
+ * case we have junk auxiliary index - we want to drop it too (and also
+ * in a concurrent way). In this case perform silent internal deletion
+ * of auxiliary index, and restore transaction state. It is fine to do it
+ * in the loop because there is only single element in drop->objects.
+ */
+ if ((flags & PERFORM_DELETION_CONCURRENTLY) != 0 &&
+ state.actual_relkind == RELKIND_INDEX)
+ {
+ Oid junkAuxIndexOid = get_auxiliary_index(relOid);
+ if (OidIsValid(junkAuxIndexOid))
+ {
+ ObjectAddress object;
+ object.classId = RelationRelationId;
+ object.objectId = junkAuxIndexOid;
+ object.objectSubId = 0;
+ performDeletion(&object, DROP_RESTRICT,
+ PERFORM_DELETION_CONCURRENTLY |
+ PERFORM_DELETION_INTERNAL |
+ PERFORM_DELETION_QUIETLY);
+ CommitTransactionCommand();
+ StartTransactionCommand();
+ PushActiveSnapshot(GetTransactionSnapshot());
+ PreventInTransactionBlock(true, "DROP INDEX CONCURRENTLY");
+ }
+ }
+
/*
* Concurrent index drop cannot be used with partitioned indexes,
* either.
@@ -1674,12 +1715,17 @@ RemoveRelations(DropStmt *drop)
obj.objectId = relOid;
obj.objectSubId = 0;
+ oldcontext = MemoryContextSwitchTo(private_context);
add_exact_object_address(&obj, objects);
+ MemoryContextSwitchTo(oldcontext);
}
+ /* Deletion may involve multiple commits, so, switch to memory context */
+ oldcontext = MemoryContextSwitchTo(private_context);
performMultipleDeletions(objects, drop->behavior, flags);
+ MemoryContextSwitchTo(oldcontext);
- free_object_addresses(objects);
+ MemoryContextDelete(private_context);
}
/*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf5243..02bcf5e9315 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -180,6 +180,7 @@ extern List *getOwnedSequences(Oid relid);
extern Oid getIdentitySequence(Relation rel, AttrNumber attnum, bool missing_ok);
extern Oid get_index_constraint(Oid indexId);
+extern Oid get_auxiliary_index(Oid indexId);
extern List *get_index_ref_constraints(Oid indexId);
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 4713f18e68d..53b2b13efc3 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -73,6 +73,7 @@ extern Oid index_create(Relation heapRelation,
Oid indexRelationId,
Oid parentIndexRelid,
Oid parentConstraintId,
+ Oid auxiliaryForIndexId,
RelFileNumber relFileNumber,
IndexInfo *indexInfo,
const List *indexColNames,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index ca74844b5c6..aca6ec57ad7 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -3265,20 +3265,109 @@ ERROR: reindex of auxiliary index "aux_index_ind6_ccaux" not supported
REINDEX INDEX CONCURRENTLY aux_index_ind6_ccaux;
ERROR: reindex of auxiliary index "aux_index_ind6_ccaux" not supported
-- This makes the previous failure go away, so the index can become valid.
-DELETE FROM concur_reindex_tab4 WHERE c1 = 1;
-ERROR: relation "concur_reindex_tab4" does not exist
-LINE 1: DELETE FROM concur_reindex_tab4 WHERE c1 = 1;
- ^
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
+-- Should be skipped during reindex and dropped
+REINDEX INDEX aux_index_ind6;
+-- Make sure aux index is dropped
+\d aux_index_tab5
+ Table "public.aux_index_tab5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | |
+Indexes:
+ "aux_index_ind6" UNIQUE, btree (c1)
+
+DROP INDEX aux_index_ind6;
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+ERROR: could not create unique index "aux_index_ind6"
+DETAIL: Key (c1)=(1) is duplicated.
+-- This makes the previous failure go away, so the index can become valid.
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
-- Should be skipped during reindex
-REINDEX TABLE aux_index_tab5;
-ERROR: could not create unique index "aux_index_ind6"
-DETAIL: Key (c1)=(1) is duplicated.
--- Should be skipped during concurrent reindex
REINDEX TABLE CONCURRENTLY aux_index_tab5;
WARNING: skipping reindex of invalid index "public.aux_index_ind6"
HINT: Use DROP INDEX or REINDEX INDEX.
WARNING: skipping reindex of auxiliary index "public.aux_index_ind6_ccaux"
NOTICE: table "aux_index_tab5" has no indexes that can be reindexed concurrently
+-- Make sure it is still exists
+\d aux_index_tab5
+ Table "public.aux_index_tab5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | |
+Indexes:
+ "aux_index_ind6" UNIQUE, btree (c1) INVALID
+ "aux_index_ind6_ccaux" stir (c1) INVALID
+
+-- Should be skipped during reindex and dropped
+REINDEX TABLE aux_index_tab5;
+-- Make sure aux index is dropped
+\d aux_index_tab5
+ Table "public.aux_index_tab5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | |
+Indexes:
+ "aux_index_ind6" UNIQUE, btree (c1)
+
+DROP INDEX aux_index_ind6;
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+ERROR: could not create unique index "aux_index_ind6"
+DETAIL: Key (c1)=(1) is duplicated.
+-- This makes the previous failure go away, so the index can become valid.
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
+-- Should be skipped during reindex and dropped
+REINDEX INDEX CONCURRENTLY aux_index_ind6;
+-- Make sure aux index is dropped
+\d aux_index_tab5
+ Table "public.aux_index_tab5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | |
+Indexes:
+ "aux_index_ind6" UNIQUE, btree (c1)
+
+DROP INDEX aux_index_ind6;
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+ERROR: could not create unique index "aux_index_ind6"
+DETAIL: Key (c1)=(1) is duplicated.
+-- This makes the previous failure go away, so the index can become valid.
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
+-- Drop main index CONCURRENTLY
+DROP INDEX CONCURRENTLY aux_index_ind6;
+-- Make sure auxiliary index dropped too
+\d aux_index_tab5
+ Table "public.aux_index_tab5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | |
+
+DROP INDEX aux_index_ind6;
+ERROR: index "aux_index_ind6" does not exist
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+ERROR: could not create unique index "aux_index_ind6"
+DETAIL: Key (c1)=(1) is duplicated.
+-- Drop main index
+DROP INDEX aux_index_ind6;
+-- Make sure auxiliary index dropped too
+\d aux_index_tab5
+ Table "public.aux_index_tab5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c1 | integer | | |
+
DROP TABLE aux_index_tab5;
-- Check handling of indexes with expressions and predicates. The
-- definitions of the rebuilt indexes should match the original
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 2cff1ac29be..e1464eaa67c 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1340,11 +1340,62 @@ REINDEX INDEX aux_index_ind6_ccaux;
-- Concurrently also
REINDEX INDEX CONCURRENTLY aux_index_ind6_ccaux;
-- This makes the previous failure go away, so the index can become valid.
-DELETE FROM concur_reindex_tab4 WHERE c1 = 1;
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
+-- Should be skipped during reindex and dropped
+REINDEX INDEX aux_index_ind6;
+-- Make sure aux index is dropped
+\d aux_index_tab5
+DROP INDEX aux_index_ind6;
+
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+-- This makes the previous failure go away, so the index can become valid.
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
-- Should be skipped during reindex
-REINDEX TABLE aux_index_tab5;
--- Should be skipped during concurrent reindex
REINDEX TABLE CONCURRENTLY aux_index_tab5;
+-- Make sure it is still exists
+\d aux_index_tab5
+-- Should be skipped during reindex and dropped
+REINDEX TABLE aux_index_tab5;
+-- Make sure aux index is dropped
+\d aux_index_tab5
+DROP INDEX aux_index_ind6;
+
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+-- This makes the previous failure go away, so the index can become valid.
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
+-- Should be skipped during reindex and dropped
+REINDEX INDEX CONCURRENTLY aux_index_ind6;
+-- Make sure aux index is dropped
+\d aux_index_tab5
+DROP INDEX aux_index_ind6;
+
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+-- This makes the previous failure go away, so the index can become valid.
+DELETE FROM aux_index_tab5 WHERE c1 = 1;
+-- Drop main index CONCURRENTLY
+DROP INDEX CONCURRENTLY aux_index_ind6;
+-- Make sure auxiliary index dropped too
+\d aux_index_tab5
+DROP INDEX aux_index_ind6;
+
+-- Insert duplicates again
+INSERT INTO aux_index_tab5 VALUES (1), (1);
+-- Create invalid index again
+CREATE UNIQUE INDEX CONCURRENTLY aux_index_ind6 ON aux_index_tab5 (c1);
+-- Drop main index
+DROP INDEX aux_index_ind6;
+-- Make sure auxiliary index dropped too
+\d aux_index_tab5
+
DROP TABLE aux_index_tab5;
-- Check handling of indexes with expressions and predicates. The
--
2.43.0