Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
Started by Anna Akentevaover 5 years ago14 messages
Hello, hackers!
I'd like to propose a feature for changing a constraint's index. The
provided patch allows to do it for EXCLUDE, UNIQUE, PRIMARY KEY and
FOREIGN KEY constraints.
Feature description:
ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
Replace a constraint's index with another sufficiently similar index.
Use cases:
- Removing index bloat [1]/messages/by-id/CABwTF4UxTg+kERo1Nd4dt+H2miJoLPcASMFecS1-XHijABOpPg@mail.gmail.com (now also achieved by REINDEX
CONCURRENTLY)
- Swapping a normal index for an index with INCLUDED columns, or vice
versa
Example of use:
CREATE TABLE target_tbl (
id integer PRIMARY KEY,
info text
);
CREATE TABLE referencing_tbl (
id_ref integer REFERENCES target_tbl (id)
);
-- Swapping primary key's index for an equivalent index,
-- but with INCLUDE-d attributes.
CREATE UNIQUE INDEX new_idx ON target_tbl (id) INCLUDE (info);
ALTER TABLE target_tbl ALTER CONSTRAINT target_tbl_pkey USING INDEX
new_idx;
ALTER TABLE referencing_tbl ALTER CONSTRAINT referencing_tbl_id_ref_fkey
USING INDEX new_idx;
DROP INDEX target_tbl_pkey;
I'd like to hear your feedback on this feature.
Also, some questions:
1) If the index supporting a UNIQUE or PRIMARY KEY constraint is
changed, should foreign keys also automatically switch to the new index?
Or should the user switch it manually, by using ALTER CONSTRAINT USING
INDEX on the foreign key?
2) Whose name should change to fit the other - constraint's or index's?
[1]: /messages/by-id/CABwTF4UxTg+kERo1Nd4dt+H2miJoLPcASMFecS1-XHijABOpPg@mail.gmail.com
/messages/by-id/CABwTF4UxTg+kERo1Nd4dt+H2miJoLPcASMFecS1-XHijABOpPg@mail.gmail.com
P.S. I apologize for resending the email, the previous one was sent as a
response to another thread by mistake.
--
Anna Akenteva
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
alter_con_idx_v1.patchtext/x-diff; name=alter_con_idx_v1.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 991323d3471..341b5631ecc 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -745,6 +745,13 @@ CREATE TABLE products (
<para>
Adding a unique constraint will automatically create a unique B-tree
index on the column or group of columns listed in the constraint.
+ If required, you can later use the <literal>ALTER CONSTRAINT ... USING INDEX</literal>
+ clause of the <literal>ALTER TABLE</literal> command to replace this index
+ with another unique B-tree index that enforces this constraint over the
+ same key columns. In this case, the constraint name is changed accordingly.
+ </para>
+
+ <para>
A uniqueness restriction covering only some rows cannot be written as
a unique constraint, but it is possible to enforce such a restriction by
creating a unique <link linkend="indexes-partial">partial index</link>.
@@ -821,6 +828,10 @@ CREATE TABLE example (
Adding a primary key will automatically create a unique B-tree index
on the column or group of columns listed in the primary key, and will
force the column(s) to be marked <literal>NOT NULL</literal>.
+ If required, you can later use the <literal>ALTER CONSTRAINT ... USING INDEX</literal>
+ clause of the <literal>ALTER TABLE</literal> command to replace this index
+ with another unique B-tree index that enforces this constraint over the
+ same key columns. In this case, the constraint name is changed accordingly.
</para>
<para>
@@ -1112,6 +1123,10 @@ CREATE TABLE circles (
<para>
Adding an exclusion constraint will automatically create an index
of the type specified in the constraint declaration.
+ If required, you can later use the <literal>ALTER CONSTRAINT ... USING INDEX</literal>
+ clause of the <literal>ALTER TABLE</literal> command to replace this index
+ with another index of the same type that enforces this constraint over the
+ same key columns. In this case, the constraint name is changed accordingly.
</para>
</sect2>
</sect1>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index b2eb7097a95..49b6deef4d4 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -57,6 +57,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]
ADD <replaceable class="parameter">table_constraint_using_index</replaceable>
ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+ ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [USING INDEX <replaceable class="parameter">index_name</replaceable>]
VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ]
@@ -486,7 +487,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
- <term><literal>ALTER CONSTRAINT</literal></term>
+ <term><literal>ALTER CONSTRAINT</literal> <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]</term>
<listitem>
<para>
This form alters the attributes of a constraint that was previously
@@ -495,6 +496,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ALTER CONSTRAINT</literal> <replaceable class="parameter">constraint_name</replaceable> [USING INDEX <replaceable class="parameter">index_name</replaceable>]</term>
+ <listitem>
+ <para>
+ For uniqueness, primary key, and exclusion constraints, this form
+ replaces the original index and renames the constraint accordingly.
+ The new index must use the same access method and enforce the constraint
+ over the same key columns as the original index.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
@@ -1539,6 +1552,31 @@ ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
</programlisting>
</para>
+ <para>
+ To add a unique constraint to a table:
+<programlisting>
+CREATE TABLE products (
+ product_no integer CONSTRAINT must_be_different UNIQUE,
+ name text,
+ price numeric
+);
+</programlisting>
+ </para>
+
+ <para>
+ To create a different index on the same column as the original
+ index and alter the constraint to use the new index:
+
+<programlisting>
+CREATE UNIQUE INDEX must_be_different_new
+ ON products USING BTREE(product_no);
+
+ALTER TABLE products
+ ALTER CONSTRAINT must_be_different
+ USING INDEX must_be_different_new;
+</programlisting>
+ </para>
+
<para>
To move a table to a different tablespace:
<programlisting>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f79044f39fc..e27c62ee389 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -328,6 +328,7 @@ static void AlterSeqNamespaces(Relation classRel, Relation rel,
LOCKMODE lockmode);
static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
bool recurse, bool recursing, LOCKMODE lockmode);
+static ObjectAddress ATExecAlterConstraintUsingIndex(Relation rel, AlterTableCmd *cmd);
static ObjectAddress ATExecValidateConstraint(Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
static int transformColumnNameList(Oid relId, List *colList,
@@ -3782,6 +3783,7 @@ AlterTableGetLockLevel(List *cmds)
*/
case AT_ColumnDefault:
case AT_AlterConstraint:
+ case AT_AlterConstraintUsingIndex:
case AT_AddIndex: /* from ADD CONSTRAINT */
case AT_AddIndexConstraint:
case AT_ReplicaIdentity:
@@ -4228,6 +4230,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
+ case AT_AlterConstraintUsingIndex: /* ALTER CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
break;
@@ -4499,6 +4502,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_AlterConstraint: /* ALTER CONSTRAINT */
address = ATExecAlterConstraint(rel, cmd, false, false, lockmode);
break;
+ case AT_AlterConstraintUsingIndex:
+ address = ATExecAlterConstraintUsingIndex(rel, cmd);
+ break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
address = ATExecValidateConstraint(rel, cmd->name, false, false,
lockmode);
@@ -9546,6 +9552,484 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
return true;
}
+/*
+ * RelationGetIndexClass -- get OIDs of operator classes for each index column
+ */
+static oidvector *
+RelationGetIndexClass(Relation index)
+{
+ Datum indclassDatum;
+ bool isnull;
+
+ Assert(index != NULL);
+ Assert(index->rd_indextuple != NULL);
+
+ /*
+ * indclass cannot be referenced directly through the C struct, because it
+ * comes after the variable-width indkey field. Must extract the datum
+ * the hard way...
+ */
+ indclassDatum = SysCacheGetAttr(INDEXRELID, index->rd_indextuple,
+ Anum_pg_index_indclass, &isnull);
+ Assert(!isnull);
+
+ return (oidvector *) DatumGetPointer(indclassDatum);
+}
+
+/*
+ * Check if oldIndex can be replaced by newIndex in a constraint
+ * Returns NULL if indexes are compatible or a string with a
+ * description of the incompatibility reasons
+ */
+static const char *
+indexExchangeabilityError(Relation oldIndex, Relation newIndex)
+{
+ Form_pg_index oldIndexForm = oldIndex->rd_index,
+ newIndexForm = newIndex->rd_index;
+ List *oldPredicate = RelationGetIndexPredicate(oldIndex),
+ *newPredicate = RelationGetIndexPredicate(newIndex);
+ oidvector *oldIndexClass = RelationGetIndexClass(oldIndex),
+ *newIndexClass = RelationGetIndexClass(newIndex);
+ int i;
+
+ /* This function might need modificatoins if pg_index gets new fields */
+ Assert(Natts_pg_index == 20);
+
+ if (RelationGetForm(oldIndex)->relam != RelationGetForm(newIndex)->relam)
+ return "Indexes must have the same access methods";
+
+ /*
+ * We do not want to replace the corresponding partitioned index and/or
+ * corresponding partition indexes of other partitions.
+ */
+
+ if (RelationGetForm(oldIndex)->relkind == RELKIND_PARTITIONED_INDEX ||
+ RelationGetForm(newIndex)->relkind == RELKIND_PARTITIONED_INDEX)
+ return "One of the indexes is a partitioned index";
+
+ if (RelationGetForm(oldIndex)->relispartition ||
+ RelationGetForm(newIndex)->relispartition)
+ return "One of the indexes is a partition index";
+
+ if (!oldIndexForm->indislive || !newIndexForm->indislive)
+ return "One of the indexes is being dropped";
+ if (!oldIndexForm->indisvalid || !newIndexForm->indisvalid)
+ return "One of the indexes is not valid for queries";
+ if (!oldIndexForm->indisready || !newIndexForm->indisready)
+ return "One of the indexes is not ready for inserts";
+
+ if (oldIndexForm->indisunique != newIndexForm->indisunique)
+ return "Both indexes must be either unique or not";
+
+ if (IndexRelationGetNumberOfKeyAttributes(oldIndex) !=
+ IndexRelationGetNumberOfKeyAttributes(newIndex))
+ return "Indexes must have the same number of key columns";
+
+ for (i = 0; i < IndexRelationGetNumberOfKeyAttributes(oldIndex); i++)
+ {
+ if (oldIndexForm->indkey.values[i] != newIndexForm->indkey.values[i])
+ return "Indexes must have the same key columns";
+
+ /*
+ * A deterministic comparison considers strings that are not byte-wise
+ * equal to be unequal even if they are considered logically equal by
+ * the comparison. Comparison that is not deterministic can make the
+ * collation be, say, case- or accent-insensitive. Therefore indexes
+ * must have the same collation.
+ */
+ if (oldIndex->rd_indcollation[i] != newIndex->rd_indcollation[i])
+ return "Indexes must have the same collation";
+
+ if (oldIndexClass->values[i] != newIndexClass->values[i])
+ return "Indexes must have the same operator class";
+
+ if (oldIndex->rd_indoption[i] != newIndex->rd_indoption[i])
+ return "Indexes must have the same per-column flag bits";
+ }
+
+ if (!equal(RelationGetIndexExpressions(oldIndex),
+ RelationGetIndexExpressions(newIndex)))
+ return "Indexes must have the same non-column attributes";
+
+ if (!equal(oldPredicate, newPredicate))
+ {
+ if (oldPredicate && newPredicate)
+ return "Indexes must have the same partial index predicates";
+ else
+ return "Either none or both indexes must have partial index predicates";
+ }
+
+ /*
+ * Check that the deferrable constraint will not "invalidate" the replica
+ * identity index. (For each constraint index pg_index.indimmediate !=
+ * pg_constraint.condeferrable. Therefore for a deferrable constraint
+ * pg_index.indimmediate = false and such indexes cannot be used as
+ * replica identity indexes.)
+ */
+ if (!oldIndexForm->indimmediate && newIndexForm->indisreplident)
+ return "Deferrable constraint cannot use replica identity index";
+
+ return NULL;
+}
+
+/*
+ * Update all changed properties for the old / new constraint index.
+ */
+static void
+AlterConstraintUpdateIndex(Form_pg_constraint currcon, Relation pg_index,
+ Oid indexOid, bool is_new_constraint_index)
+{
+ HeapTuple indexTuple;
+ Form_pg_index indexForm;
+ bool dirty;
+
+ Assert(currcon != NULL);
+ Assert(pg_index != NULL);
+
+ indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexOid));
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "cache lookup failed for index %u", indexOid);
+ indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ dirty = false;
+
+ /*
+ * If this is an exclusion constraint, set pg_index.indisexclusion to true
+ * for the new index constraint and false for the old index constraint.
+ */
+ if (currcon->contype == CONSTRAINT_EXCLUSION &&
+ (indexForm->indisexclusion != is_new_constraint_index))
+ {
+ indexForm->indisexclusion = is_new_constraint_index;
+ dirty = true;
+ }
+
+ /*
+ * If this is a primary key, set pg_index.indisprimary to true for the new
+ * index constraint and false for the old index constraint.
+ */
+ if (currcon->contype == CONSTRAINT_PRIMARY &&
+ (indexForm->indisprimary != is_new_constraint_index))
+ {
+ indexForm->indisprimary = is_new_constraint_index;
+ dirty = true;
+ }
+
+ /*
+ * If the constraint is deferrable, set pg_index.indimmediate to false for
+ * the new index constraint and true for the old index constraint. (For
+ * each constraint index pg_index.indimmediate !=
+ * pg_constraint.condeferrable. pg_index.indimmediate is true for each
+ * stand-alone index without constraint.)
+ */
+ if (currcon->condeferrable &&
+ (indexForm->indimmediate == is_new_constraint_index))
+ {
+ indexForm->indimmediate = !is_new_constraint_index;
+ dirty = true;
+ }
+
+ if (dirty)
+ {
+ CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
+
+ InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0);
+ }
+
+ heap_freetuple(indexTuple);
+}
+
+/*
+ * For this index:
+ * - create auto dependencies on simply-referenced columns;
+ * - if there are no simply-referenced columns, give the index an auto
+ * dependency on the whole table.
+ *
+ * This function is based on a part of index_create().
+ */
+static void
+AddRelationDependenciesToIndex(Relation index)
+{
+ Form_pg_index indexForm = index->rd_index;
+ ObjectAddress myself,
+ referenced;
+ int i,
+ attrnum;
+ bool have_simple_col = false;
+
+ ObjectAddressSet(myself, RelationRelationId, RelationGetRelid(index));
+
+ /* Create auto dependencies on simply-referenced columns */
+ for (i = 0; i < IndexRelationGetNumberOfAttributes(index); i++)
+ {
+ attrnum = indexForm->indkey.values[i];
+ if (attrnum != 0)
+ {
+ ObjectAddressSubSet(referenced, RelationRelationId,
+ indexForm->indrelid, attrnum);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
+
+ have_simple_col = true;
+ }
+ }
+
+ /*
+ * If there are no simply-referenced columns, give the index an auto
+ * dependency on the whole table.
+ */
+ if (!have_simple_col)
+ {
+ ObjectAddressSet(referenced, RelationRelationId, indexForm->indrelid);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
+ }
+}
+
+/*
+ * ALTER TABLE ALTER CONSTRAINT USING INDEX
+ *
+ * Replace an index of a constraint.
+ *
+ * Currently only works for UNIQUE, EXCLUSION and PRIMARY constraints.
+ * Index can be replaced only with index of same type and with same
+ * configuration (attributes, columns, etc.).
+ *
+ * If the constraint is modified, returns its address; otherwise, return
+ * InvalidObjectAddress.
+ */
+static ObjectAddress
+ATExecAlterConstraintUsingIndex(Relation rel, AlterTableCmd *cmd)
+{
+ Relation conrel;
+ SysScanDesc scan;
+ ScanKeyData key[3];
+ HeapTuple contuple;
+ Form_pg_constraint currcon = NULL;
+ Oid indexOid,
+ oldIndexOid;
+ Relation indexRel,
+ oldIndexRel;
+ VariableShowStmt *indexName;
+ ObjectAddress address;
+ const char *replaceabilityCheckResult;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+ ObjectAddress constraint_addr,
+ index_addr;
+ List *indexprs,
+ *indpred;
+
+ indexName = castNode(VariableShowStmt, cmd->def);
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&key[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&key[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&key[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(cmd->name));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, key);
+
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(contuple = systable_getnext(scan)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ cmd->name, RelationGetRelationName(rel))));
+
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
+ if (currcon->contype != CONSTRAINT_UNIQUE &&
+ currcon->contype != CONSTRAINT_EXCLUSION &&
+ currcon->contype != CONSTRAINT_PRIMARY &&
+ currcon->contype != CONSTRAINT_FOREIGN)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a primary "
+ "key, unique constraint, exclusion constraint "
+ "or foreign constraint",
+ cmd->name, RelationGetRelationName(rel))));
+
+ oldIndexOid = currcon->conindid;
+ oldIndexRel = index_open(oldIndexOid, ShareLock);
+
+ /* Check that the index exists */
+ indexOid = get_relname_relid(indexName->name, rel->rd_rel->relnamespace);
+ if (!OidIsValid(indexOid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("index \"%s\" for table \"%s\" does not exist",
+ indexName->name, RelationGetRelationName(rel))));
+
+ indexRel = index_open(indexOid, ShareLock);
+
+ /* Check that the index is on the relation we're altering. */
+ if ((indexRel->rd_index == NULL ||
+ indexRel->rd_index->indrelid != RelationGetRelid(rel)) &&
+ currcon->contype != CONSTRAINT_FOREIGN)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not an index for table \"%s\"",
+ indexName->name, RelationGetRelationName(rel))));
+
+ /*
+ * Check if our constraint uses this index (and therefore everything is
+ * already in order).
+ */
+ if (oldIndexOid == indexOid)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" already uses index \"%s\", skipping",
+ cmd->name, indexName->name)));
+ address = InvalidObjectAddress;
+ goto cleanup;
+ }
+
+ /* Check if another constraint already uses this index */
+ if (currcon->contype != CONSTRAINT_FOREIGN &&
+ OidIsValid(get_index_constraint(indexOid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("index \"%s\" is already associated with a constraint",
+ indexName->name)));
+
+ /* Check if the new index is compatible with our constraint */
+ if ((replaceabilityCheckResult =
+ indexExchangeabilityError(oldIndexRel, indexRel)))
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("index in constraint \"%s\" cannot be replaced by "
+ "\"%s\"",
+ cmd->name, indexName->name),
+ errdetail("%s.", replaceabilityCheckResult)));
+
+ /* OK, change the index for this constraint */
+ indexprs = RelationGetIndexExpressions(indexRel);
+ indpred = RelationGetIndexPredicate(indexRel);
+
+ /*
+ * Now update the catalog, while we have the door open.
+ */
+ copyTuple = heap_copytuple(contuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->conindid = indexOid;
+ if (currcon->contype == CONSTRAINT_EXCLUSION ||
+ currcon->contype == CONSTRAINT_PRIMARY ||
+ currcon->contype == CONSTRAINT_UNIQUE)
+ {
+ /* Rename the constraint to match the index's name */
+ ereport(NOTICE,
+ (errmsg("ALTER TABLE / ALTER CONSTRAINT USING INDEX will"
+ " rename constraint \"%s\" to \"%s\"",
+ cmd->name, indexName->name)));
+ namestrcpy(&(copy_con->conname), indexName->name);
+
+ /*
+ * If the replaced index is a replica identity index, remind the user
+ * so that he can change table replica identity and only then drop the
+ * "unnecessary" index.
+ */
+ if (oldIndexRel->rd_index->indisreplident)
+ ereport(NOTICE,
+ (errmsg("replaced index \"%s\" is still chosen as replica"
+ " identity",
+ RelationGetRelationName(oldIndexRel))));
+
+ }
+ CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId, currcon->oid, 0);
+
+ heap_freetuple(copyTuple);
+
+ /* Update old and new indexes if necessary */
+ if (currcon->contype == CONSTRAINT_EXCLUSION ||
+ currcon->contype == CONSTRAINT_PRIMARY ||
+ currcon->condeferrable)
+ {
+ Relation pg_index;
+
+ pg_index = table_open(IndexRelationId, RowExclusiveLock);
+ AlterConstraintUpdateIndex(currcon, pg_index, indexOid, true);
+ AlterConstraintUpdateIndex(currcon, pg_index, oldIndexOid, false);
+ table_close(pg_index, RowExclusiveLock);
+ }
+
+ /* Update dependencies */
+
+ /* For foreign constraints */
+ if (currcon->contype == CONSTRAINT_FOREIGN)
+ {
+ changeDependencyFor(ConstraintRelationId, currcon->oid,
+ RelationRelationId, oldIndexOid, indexOid);
+ }
+ /* For exclusion, primary and unique constraints */
+ else
+ {
+ /* The old index is now independent on any constraints */
+ deleteDependencyRecordsForClass(RelationRelationId, oldIndexOid,
+ ConstraintRelationId,
+ DEPENDENCY_INTERNAL);
+
+ /*
+ * The old index now depends on its simply-referenced columns and/or
+ * its table.
+ */
+ AddRelationDependenciesToIndex(oldIndexRel);
+
+ /* The new index now depends on our constraint */
+ ObjectAddressSet(constraint_addr, ConstraintRelationId, currcon->oid);
+ ObjectAddressSet(index_addr, RelationRelationId, indexOid);
+ recordDependencyOn(&index_addr, &constraint_addr, DEPENDENCY_INTERNAL);
+
+ /*
+ * The new index is now independent on its simply-referenced columns
+ * and/or its table.
+ */
+ deleteDependencyRecordsForClass(RelationRelationId, indexOid,
+ RelationRelationId, DEPENDENCY_AUTO);
+
+ /* Restore dependencies on anything mentioned in index expressions */
+ if (indexprs)
+ recordDependencyOnSingleRelExpr(&index_addr,
+ (Node *) indexprs,
+ RelationGetRelid(rel),
+ DEPENDENCY_NORMAL,
+ DEPENDENCY_AUTO, false);
+
+ /* Restore dependencies on anything mentioned in predicate */
+ if (indpred)
+ recordDependencyOnSingleRelExpr(&index_addr,
+ (Node *) indpred,
+ RelationGetRelid(rel),
+ DEPENDENCY_NORMAL,
+ DEPENDENCY_AUTO, false);
+ }
+
+ /* Invalidate relcache so that others see the new attributes */
+ CacheInvalidateRelcache(rel);
+
+ ObjectAddressSet(address, ConstraintRelationId, currcon->oid);
+
+cleanup:
+ systable_endscan(scan);
+ table_close(conrel, RowExclusiveLock);
+
+ index_close(oldIndexRel, NoLock);
+ index_close(indexRel, NoLock);
+
+ return address;
+}
/*
* ALTER TABLE ALTER CONSTRAINT
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4ff35095b85..8ff755df1b1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2337,6 +2337,17 @@ alter_table_cmd:
NULL, NULL, yyscanner);
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER CONSTRAINT ... USING INDEX */
+ | ALTER CONSTRAINT name USING INDEX name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ VariableShowStmt *c = makeNode(VariableShowStmt);
+ n->subtype = AT_AlterConstraintUsingIndex;
+ n->name = $3;
+ n->def = (Node *) c;
+ c->name = $6;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> VALIDATE CONSTRAINT ... */
| VALIDATE CONSTRAINT name
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5e1ffafb91b..68f2c358a65 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1803,6 +1803,7 @@ typedef enum AlterTableType
AT_ReAddConstraint, /* internal to commands/tablecmds.c */
AT_ReAddDomainConstraint, /* internal to commands/tablecmds.c */
AT_AlterConstraint, /* alter constraint */
+ AT_AlterConstraintUsingIndex, /* alter constraint using index */
AT_ValidateConstraint, /* validate constraint */
AT_ValidateConstraintRecurse, /* internal to commands/tablecmds.c */
AT_AddIndexConstraint, /* add constraint using existing index */
diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source
index c325b2753d4..00e5c5c1ae6 100644
--- a/src/test/regress/input/constraints.source
+++ b/src/test/regress/input/constraints.source
@@ -552,3 +552,714 @@ DROP DOMAIN constraint_comments_dom;
DROP ROLE regress_constraint_comments;
DROP ROLE regress_constraint_comments_noaccess;
+
+--
+--
+--
+-- ALTER CONSTRAINT ... USING INDEX
+--
+--
+--
+
+CREATE FUNCTION show_some_indexes_from_relation(
+ searched_relname name,
+ searched_indnames name[]
+)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ index_relkind "char",
+ index_relispartition boolean,
+ indisunique boolean,
+ indisprimary boolean,
+ indisexclusion boolean,
+ indkey int2vector,
+ indislive boolean,
+ indisvalid boolean,
+ indisready boolean,
+ indoption int2vector,
+ indcollation oidvector,
+ indimmediate boolean,
+ indisreplident boolean,
+ depends_on_table boolean,
+ depends_on_simple_columns boolean,
+ depends_on_constraint boolean,
+ conname name
+)
+AS $$
+ SELECT r.relname, i.relname, i.relkind, i.relispartition, indisunique,
+ indisprimary, indisexclusion, indkey, indislive, indisvalid,
+ indisready, indoption, indcollation, indimmediate, indisreplident,
+ EXISTS(SELECT *
+ FROM pg_depend
+ WHERE classid = 'pg_class'::regclass AND
+ objid = indexrelid AND
+ refclassid = 'pg_class'::regclass AND
+ refobjid = indrelid AND
+ refobjsubid = 0),
+ EXISTS(SELECT *
+ FROM pg_depend
+ WHERE classid = 'pg_class'::regclass AND
+ objid = indexrelid AND
+ refclassid = 'pg_class'::regclass AND
+ refobjid = indrelid AND
+ refobjsubid != 0),
+ EXISTS(SELECT *
+ FROM pg_depend
+ WHERE classid = 'pg_class'::regclass AND
+ objid = indexrelid AND
+ refclassid = 'pg_constraint'::regclass AND
+ refobjid = c.oid),
+ conname
+ FROM pg_index
+ JOIN pg_class i ON indexrelid = i.oid
+ JOIN pg_class r ON indrelid = r.oid
+ LEFT JOIN pg_constraint c ON indexrelid = c.conindid
+ WHERE r.relname = searched_relname AND
+ (searched_indnames IS NULL OR i.relname = ANY (searched_indnames))
+ ORDER BY indkey, i.relname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_indexes_from_relation(searched_relname name)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ index_relkind "char",
+ index_relispartition boolean,
+ indisunique boolean,
+ indisprimary boolean,
+ indisexclusion boolean,
+ indkey int2vector,
+ indislive boolean,
+ indisvalid boolean,
+ indisready boolean,
+ indoption int2vector,
+ indcollation oidvector,
+ indimmediate boolean,
+ indisreplident boolean,
+ depends_on_table boolean,
+ depends_on_simple_columns boolean,
+ depends_on_constraint boolean,
+ conname name
+)
+AS $$
+ SELECT * FROM show_some_indexes_from_relation(searched_relname, NULL);
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_some_index_exprs_pred(
+ searched_relname name,
+ searched_indnames name[]
+)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ indpred text,
+ indexprs text
+)
+AS $$
+ SELECT
+ r.relname,
+ i.relname,
+ pg_get_expr(indpred, indrelid, true),
+ pg_get_expr(indexprs, indrelid, true)
+ FROM pg_index
+ JOIN pg_class i ON indexrelid = i.oid
+ JOIN pg_class r ON indrelid = r.oid
+ WHERE r.relname = searched_relname AND
+ (searched_indnames IS NULL OR i.relname = ANY (searched_indnames))
+ ORDER BY indexprs, indpred, indkey, i.relname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_index_exprs_pred(searched_relname name)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ indpred text,
+ indexprs text
+)
+AS $$
+ SELECT * FROM show_some_index_exprs_pred(searched_relname, NULL);
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_constraints_named_like(searched_conname name)
+RETURNS TABLE
+(
+ conname name,
+ contype "char",
+ conkey smallint[],
+ condeferrable boolean
+)
+AS $$
+ SELECT conname, contype, conkey, condeferrable
+ FROM pg_constraint
+ WHERE conname LIKE searched_conname
+ ORDER BY conkey, conname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_index_dependencies_on_table_columns
+(
+ searched_indnames name[]
+)
+RETURNS TABLE
+(
+ indname name,
+ indnatts smallint,
+ indnkeyatts smallint,
+ indkey int2vector,
+ attnum smallint,
+ attname name
+)
+AS $$
+ SELECT relname, indnatts, indnkeyatts, indkey, attnum, attname
+ FROM pg_index
+ JOIN pg_class ON indexrelid = pg_class.oid
+ JOIN pg_depend ON indexrelid = objid
+ JOIN pg_attribute ON attrelid = indrelid
+ WHERE relname = ANY (searched_indnames) AND
+ classid = 'pg_class'::regclass AND
+ refclassid = 'pg_class'::regclass AND
+ refobjid = indrelid AND
+ refobjsubid != 0 AND
+ refobjsubid = attnum
+ ORDER BY relname, refobjsubid;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_alteridx_index_dependencies()
+RETURNS TABLE
+(
+ indname name,
+ referenced_indname name
+)
+AS $$
+ SELECT c.relname, ref_c.relname
+ FROM
+ pg_index AS i
+ JOIN pg_class AS c ON c.oid = i.indexrelid
+ JOIN pg_depend ON objid = i.indexrelid
+ JOIN pg_index AS ref_i ON refobjid = ref_i.indexrelid
+ JOIN pg_class AS ref_c ON ref_c.oid = ref_i.indexrelid
+ WHERE classid = 'pg_class'::regclass
+ AND refclassid = 'pg_class'::regclass
+ AND c.relname like '%alteridx%'
+ ORDER BY c.relname, ref_c.relname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_alteridx_constraint_dependencies()
+RETURNS TABLE
+(
+ conname name,
+ referenced_conname name
+)
+AS $$
+ SELECT con.conname, ref_con.conname
+ FROM
+ pg_constraint AS con
+ JOIN pg_depend ON objid = con.oid
+ JOIN pg_constraint AS ref_con ON refobjid = ref_con.oid
+ WHERE
+ classid = 'pg_constraint'::regclass AND
+ refclassid = 'pg_constraint'::regclass AND
+ con.conname like '%alteridx%'
+ ORDER BY con.conname, ref_con.conname;
+$$ LANGUAGE SQL;
+--
+--
+--
+CREATE TABLE alteridx_orig(
+id int PRIMARY KEY,
+uniq int CONSTRAINT alteridx_orig_uniq_key UNIQUE NOT NULL,
+parity int,
+msg text UNIQUE,
+ir int4range,
+partition_key int,
+EXCLUDE using gist(ir with &&),
+EXCLUDE USING btree((id + uniq) WITH =) WHERE (id > 2),
+EXCLUDE ((id + uniq) WITH =) WHERE (parity < 4),
+CONSTRAINT alteridx_orig_double UNIQUE(id, uniq),
+CONSTRAINT alteridx_id_key_deferrable UNIQUE (id) DEFERRABLE,
+CHECK (parity > -10));
+
+CREATE TABLE partitioned_orig_alteridx(
+id int,
+uniq int,
+parity int,
+msg text,
+ir int4range,
+partition_key int UNIQUE,
+UNIQUE (partition_key, id))
+PARTITION BY RANGE (partition_key);
+
+ALTER TABLE partitioned_orig_alteridx ATTACH PARTITION alteridx_orig
+FOR VALUES FROM (0) TO (20);
+--
+--
+CREATE TABLE another_alteridx(
+id int PRIMARY KEY,
+uniq int UNIQUE,
+parity int,
+msg text,
+ir int4range,
+EXCLUDE USING gist(ir with &&),
+EXCLUDE USING btree((id + 4) WITH =));
+--
+--
+CREATE TABLE third_alteridx(
+ id1 int,
+ id2 int,
+ PRIMARY KEY (id1, id2)
+);
+--
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+SELECT * FROM show_constraints_named_like('alteridx_%');
+
+SELECT * FROM show_indexes_from_relation('partitioned_orig_alteridx');
+SELECT * FROM show_constraints_named_like('partitioned_%');
+
+SELECT * FROM show_indexes_from_relation('another_alteridx');
+SELECT * FROM show_constraints_named_like('another_%');
+--
+--
+-- Checking that constraints work before index replacement
+--
+--
+INSERT INTO alteridx_orig SELECT n, n, n%2, CHR(62+n) || CHR(63+n),
+int4range(2*n,2*n+1), n from generate_series(1,10) as gs(n);
+INSERT INTO another_alteridx SELECT n, n, n%2, CHR(62+n) || CHR(63+n),
+int4range(2*n,2*n+1) from generate_series(1,10) as gs(n);
+--
+INSERT INTO alteridx_orig VALUES(1, 0, 1, 'AA', int4range(102, 103), 15); -- failure here
+INSERT INTO alteridx_orig VALUES(0, 1, 1, 'AA', int4range(104, 105), 15); -- failure here
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(1, 107), 15); -- failure here
+INSERT INTO alteridx_orig VALUES(NULL, 0, 1, 'AA', int4range(102, 107), 15); -- failure here
+INSERT INTO alteridx_orig VALUES(0, NULL, 1, 'AA', int4range(102, 107), 15); -- failure here
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(102, 107), 15);
+SELECT * FROM alteridx_orig;
+--
+--
+CREATE UNIQUE INDEX alteridx_new_uniq_key ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_incl ON alteridx_orig(uniq) INCLUDE (ir);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_incl2 ON alteridx_orig(uniq) INCLUDE (parity);
+CREATE UNIQUE INDEX CONCURRENTLY alteridx_new_uniq_key_back ON alteridx_orig USING BTREE(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_pred ON alteridx_orig(uniq) WHERE parity=1;
+CREATE INDEX alteridx_new_uniq_key_no_unique ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_with_msg ON alteridx_orig(uniq, msg);
+CREATE UNIQUE INDEX alteridx_new_msg_key_ops ON alteridx_orig(msg text_pattern_ops);
+CREATE UNIQUE INDEX alteridx_new_pkey ON alteridx_orig(id);
+CREATE INDEX alteridx_new_ir_excl ON alteridx_orig using gist(ir range_ops);
+CREATE INDEX alteridx_new_expr_excl ON alteridx_orig((id + uniq)) WHERE (id > 2);
+CREATE INDEX alteridx_new_expr_excl_hash ON alteridx_orig USING hash((id + uniq)) WHERE (id > 2);
+CREATE INDEX alteridx_new_expr_excl_pred ON alteridx_orig((id + uniq)) WHERE (id > 3);
+CREATE INDEX alteridx_new_expr_excl_pred2 ON alteridx_orig((id + uniq)) WHERE (id > (3 - 1));
+CREATE INDEX alteridx_new_expr_excl_wrong ON alteridx_orig((id - uniq)) WHERE (id > 2);
+CREATE INDEX alteridx_new_expr_excl1 ON alteridx_orig((id + uniq)) WHERE (parity < 4);
+CREATE INDEX another_alteridx_new_expr_excl ON another_alteridx((id + 4));
+CREATE INDEX another_alteridx_new_expr_excl_different ON another_alteridx((id + 2 + 2));
+CREATE INDEX another_alteridx_new_expr_excl_different2 ON another_alteridx((id + (2 + 2)));
+CREATE UNIQUE INDEX alteridx_new_double ON alteridx_orig(id, uniq);
+CREATE INDEX alteridx_new_double_not_unique ON alteridx_orig(id, uniq);
+CREATE UNIQUE INDEX alteridx_id_key ON alteridx_orig(id);
+--
+CREATE UNIQUE INDEX third_alteridx_pkey_new ON third_alteridx(id1, id2);
+CREATE UNIQUE INDEX third_alteridx_pkey_opp ON third_alteridx(id2, id1);
+CREATE UNIQUE INDEX third_alteridx_pkey_single ON third_alteridx(id1);
+CREATE INDEX third_alteridx_pkey_not_unique ON third_alteridx(id1, id2);
+--
+CREATE UNIQUE INDEX alteridx_new_partition_key_key
+ON alteridx_orig(partition_key);
+CREATE UNIQUE INDEX alteridx_new_partition_key_id_key
+ON alteridx_orig(partition_key,id);
+CREATE UNIQUE INDEX partitioned_new_alteridx_partition_key_key
+ON partitioned_orig_alteridx(partition_key);
+CREATE UNIQUE INDEX partitioned_new_alteridx_partition_key_id_key
+ON partitioned_orig_alteridx(partition_key,id);
+
+--
+CREATE UNIQUE INDEX alteridx_new_uniq_key_opt ON alteridx_orig(uniq);
+UPDATE pg_index SET indoption='1'
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_opt';
+CREATE UNIQUE INDEX alteridx_new_msg_key_coll ON alteridx_orig(msg);
+UPDATE pg_index SET indcollation='12341'
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_msg_key_coll';
+--
+--
+-- Tests for unique constraint --
+--
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_new_uniq_key_incl",
+ "alteridx_new_uniq_key_incl2",
+ "alteridx_orig_expr_excl1",
+ "alteridx_new_expr_excl1"}'::name[]);
+SELECT * FROM show_indexes_from_relation('partitioned_orig_alteridx');
+SELECT * FROM show_indexes_from_relation('another_alteridx');
+SELECT * FROM show_index_exprs_pred('another_alteridx');
+SELECT * FROM show_constraints_named_like('another_%');
+DROP INDEX alteridx_orig_uniq_key; -- failure here
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_parity_check
+USING INDEX alteridx_new_uniq_key; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_uniq_key
+USING INDEX alteridx_orig_uniq_key;
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_uniq_key
+USING INDEX alteridx_new_uniq_key;
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+DROP INDEX alteridx_orig_uniq_key;
+DROP INDEX alteridx_new_uniq_key; -- failure here
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key
+USING INDEX alteridx_new_uniq_key_incl2;
+--
+--
+-- Checking that all dependencies on simply-referenced columns are correctly
+-- added for old constraint index (included columns may differ in the old and
+-- new constraint index).
+--
+--
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_incl2
+USING INDEX alteridx_new_uniq_key_incl;
+
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+--
+DROP INDEX alteridx_new_uniq_key;
+DROP INDEX alteridx_new_uniq_key_incl; -- failure here
+SELECT * FROM show_constraints_named_like('alteridx_%');
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_incl
+USING INDEX alteridx_new_uniq_key_back;
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_pred; -- failure here
+DROP INDEX alteridx_new_uniq_key_pred;
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_no_unique; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_msg_key
+USING INDEX alteridx_new_msg_key_coll; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_msg_key
+USING INDEX alteridx_new_msg_key_ops; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_msg_key
+USING INDEX alteridx_new_uniq_key_incl; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX another_alteridx_uniq_key; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_with_msg; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_opt; -- failure here
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_double
+USING INDEX alteridx_new_uniq_key_incl; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_double
+USING INDEX alteridx_new_double_not_unique; -- failure here
+--
+--
+-- Checking the notification if the replica identity index is no longer used in
+-- the constraint.
+--
+--
+ALTER TABLE alteridx_orig REPLICA IDENTITY USING INDEX alteridx_orig_double;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_double
+USING INDEX alteridx_new_double;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+--
+--
+-- Checking that deferrable constraints cannot use replica identity index
+--
+--
+ALTER TABLE alteridx_orig REPLICA IDENTITY USING INDEX alteridx_id_key;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_id_key_deferrable
+USING INDEX alteridx_id_key; -- failure here
+
+ALTER TABLE alteridx_orig REPLICA IDENTITY DEFAULT;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_id_key_deferrable
+USING INDEX alteridx_id_key;
+--
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ALTER TABLE alteridx_orig DROP CONSTRAINT alteridx_orig_double; -- failure here
+ALTER TABLE alteridx_orig DROP CONSTRAINT alteridx_new_double;
+DROP INDEX alteridx_orig_double;
+DROP INDEX alteridx_new_double_not_unique;
+--
+--
+-- Tests for primary key constraint --
+--
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_pkey
+USING INDEX alteridx_new_pkey;
+SELECT * FROM show_constraints_named_like('alteridx_%');
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_pkey
+USING INDEX another_alteridx_uniq_key; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_pkey
+USING INDEX another_alteridx_pkey; -- failure here
+--
+SELECT * FROM show_indexes_from_relation('third_alteridx');
+SELECT * FROM show_constraints_named_like('third_%');
+
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_opp; -- failure here
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_not_unique; -- failure here
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_single; -- failure here
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_new;
+
+SELECT * FROM show_indexes_from_relation('third_alteridx');
+SELECT * FROM show_constraints_named_like('third_%');
+
+--
+--
+-- Tests for exclusion constraint --
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_ir_excl
+USING INDEX alteridx_new_ir_excl;
+SELECT * FROM show_constraints_named_like('alteridx_%');
+--
+ALTER TABLE alteridx_orig ADD CONSTRAINT alteridx_new_expr_excl2
+EXCLUDE ((id + uniq) WITH =) WHERE (id > 2);
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl2; -- failure here
+ALTER TABLE alteridx_orig DROP CONSTRAINT alteridx_new_expr_excl2;
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_orig_ir_excl; -- failure here
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+--
+--
+-- Checking that after simplifying the constants from index predicates some
+-- indexes are considered equal.
+--
+--
+SELECT * FROM show_some_index_exprs_pred(
+ 'alteridx_orig',
+ '{"alteridx_orig_expr_excl",
+ "alteridx_new_expr_excl_pred",
+ "alteridx_new_expr_excl_pred2"}'::name[]);
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_pred; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_pred2;
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_expr_excl_pred2
+USING INDEX alteridx_new_expr_excl_hash; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_expr_excl_pred2
+USING INDEX alteridx_new_expr_excl;
+--
+--
+-- Checking that all dependencies on columns from index expressions and/or index
+-- predicate are not removed for new constraint index (they always exist both
+-- for standalone and constraint indexes).
+--
+--
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl1
+USING INDEX alteridx_new_expr_excl1;
+
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+--
+--
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+DROP INDEX alteridx_new_expr_excl_wrong;
+ALTER TABLE alteridx_does_not_exist ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+--
+DROP INDEX alteridx_new_expr_excl_pred;
+DROP INDEX alteridx_orig_expr_excl;
+DROP INDEX alteridx_new_expr_excl_hash;
+DROP INDEX alteridx_new_expr_excl; -- failure here
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+
+ALTER TABLE another_alteridx ALTER CONSTRAINT another_alteridx_expr_excl
+USING INDEX another_alteridx_new_expr_excl;
+--
+--
+-- Checking that after simplifying the constants from index expressions some
+-- indexes are considered equal.
+--
+--
+SELECT * FROM show_some_index_exprs_pred(
+ 'another_alteridx',
+ '{"another_alteridx_new_expr_excl",
+ "another_alteridx_new_expr_excl_different",
+ "another_alteridx_new_expr_excl_different2"}'::name[]);
+ALTER TABLE another_alteridx ALTER CONSTRAINT another_alteridx_new_expr_excl
+USING INDEX another_alteridx_new_expr_excl_different; -- failure here
+ALTER TABLE another_alteridx ALTER CONSTRAINT another_alteridx_new_expr_excl
+USING INDEX another_alteridx_new_expr_excl_different2;
+
+SELECT * FROM show_indexes_from_relation('another_alteridx');
+SELECT * FROM show_constraints_named_like('another_%');
+--
+--
+-- Checking that DDL changes can be rolled back
+--
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+
+BEGIN;
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_pkey
+USING INDEX alteridx_orig_pkey;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ROLLBACK;
+
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+--
+--
+-- Checking constraints in partitions and partitioned tables
+--
+--
+SELECT * FROM show_alteridx_index_dependencies();
+SELECT * FROM show_alteridx_constraint_dependencies();
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_key
+USING INDEX alteridx_new_partition_key_key; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_id_key
+USING INDEX alteridx_new_partition_key_id_key; -- failure here
+
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_key
+USING INDEX partitioned_new_alteridx_partition_key_key; -- failure here
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_id_key
+USING INDEX partitioned_new_alteridx_partition_key_id_key; -- failure here
+
+ALTER TABLE partitioned_orig_alteridx DETACH PARTITION alteridx_orig;
+
+SELECT * FROM show_alteridx_index_dependencies();
+SELECT * FROM show_alteridx_constraint_dependencies();
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_key
+USING INDEX alteridx_new_partition_key_key;
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_id_key
+USING INDEX alteridx_new_partition_key_id_key;
+
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_key
+USING INDEX partitioned_new_alteridx_partition_key_key; -- failure here
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_id_key
+USING INDEX partitioned_new_alteridx_partition_key_id_key; -- failure here
+--
+--
+-- Dropping replaced indexes
+--
+--
+DROP INDEX alteridx_orig_ir_excl;
+DROP INDEX alteridx_orig_pkey;
+DROP INDEX alteridx_new_uniq_key_no_unique;
+DROP INDEX alteridx_new_uniq_key_incl;
+DROP INDEX alteridx_new_msg_key_coll;
+DROP INDEX alteridx_new_msg_key_ops;
+--
+-- Trying to drop indexes used in constraints after replacement
+--
+DROP INDEX alteridx_new_pkey; -- failure here
+DROP INDEX alteridx_new_uniq_key_back; -- failure here
+DROP INDEX alteridx_orig_msg_key; -- failure here
+DROP INDEX alteridx_new_ir_excl; -- failure here
+DROP INDEX alteridx_new_uniq_key_opt;
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+SELECT * FROM show_constraints_named_like('alteridx_%');
+--
+--
+-- Checking that indexes unavailable for use can't be picked for replacement
+--
+--
+CREATE UNIQUE INDEX alteridx_new_uniq_key_not_live ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_not_valid ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_not_ready ON alteridx_orig(uniq);
+UPDATE pg_index SET indislive=false
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_not_live';
+UPDATE pg_index SET indisvalid=false
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_not_valid';
+UPDATE pg_index SET indisready=false
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_not_ready';
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_not_live; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_not_valid; -- failure here
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_not_ready; -- failure here
+--
+DROP INDEX alteridx_new_uniq_key_not_live;
+DROP INDEX alteridx_new_uniq_key_not_valid;
+DROP INDEX alteridx_new_uniq_key_not_ready;
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+SELECT * FROM show_constraints_named_like('alteridx_%');
+--
+--
+-- Checking that constraints still work
+--
+--
+INSERT INTO alteridx_orig VALUES(1, 0, 1, 'AA', int4range(102, 103), 17); -- failure here
+INSERT INTO alteridx_orig VALUES(0, 1, 1, 'AA', int4range(104, 105), 17); -- failure here
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(100, 107), 17); -- failure here
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(102, 107), 17); -- failure here
+INSERT INTO alteridx_orig VALUES(NULL, 0, 1, 'AA', int4range(102, 107), 17); -- failure here
+INSERT INTO alteridx_orig VALUES(0, NULL, 1, 'AA', int4range(102, 107), 17); -- failure here
+INSERT INTO alteridx_orig VALUES(-1, -1, 1, 'BB', int4range(108, 110), 17);
+--
+SELECT * FROM alteridx_orig;
+--
+--
+DROP FUNCTION show_indexes_from_relation(searched_relname name);
+DROP FUNCTION show_constraints_named_like(searched_conname name);
+DROP FUNCTION show_index_exprs_pred(searched_relname name);
+DROP TABLE alteridx_orig;
+DROP TABLE partitioned_orig_alteridx;
+DROP TABLE another_alteridx;
+DROP TABLE third_alteridx;
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source
index b727c6150ae..d7fc0fa00aa 100644
--- a/src/test/regress/output/constraints.source
+++ b/src/test/regress/output/constraints.source
@@ -736,3 +736,1882 @@ DROP TABLE constraint_comments_tbl;
DROP DOMAIN constraint_comments_dom;
DROP ROLE regress_constraint_comments;
DROP ROLE regress_constraint_comments_noaccess;
+--
+--
+--
+-- ALTER CONSTRAINT ... USING INDEX
+--
+--
+--
+CREATE FUNCTION show_some_indexes_from_relation(
+ searched_relname name,
+ searched_indnames name[]
+)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ index_relkind "char",
+ index_relispartition boolean,
+ indisunique boolean,
+ indisprimary boolean,
+ indisexclusion boolean,
+ indkey int2vector,
+ indislive boolean,
+ indisvalid boolean,
+ indisready boolean,
+ indoption int2vector,
+ indcollation oidvector,
+ indimmediate boolean,
+ indisreplident boolean,
+ depends_on_table boolean,
+ depends_on_simple_columns boolean,
+ depends_on_constraint boolean,
+ conname name
+)
+AS $$
+ SELECT r.relname, i.relname, i.relkind, i.relispartition, indisunique,
+ indisprimary, indisexclusion, indkey, indislive, indisvalid,
+ indisready, indoption, indcollation, indimmediate, indisreplident,
+ EXISTS(SELECT *
+ FROM pg_depend
+ WHERE classid = 'pg_class'::regclass AND
+ objid = indexrelid AND
+ refclassid = 'pg_class'::regclass AND
+ refobjid = indrelid AND
+ refobjsubid = 0),
+ EXISTS(SELECT *
+ FROM pg_depend
+ WHERE classid = 'pg_class'::regclass AND
+ objid = indexrelid AND
+ refclassid = 'pg_class'::regclass AND
+ refobjid = indrelid AND
+ refobjsubid != 0),
+ EXISTS(SELECT *
+ FROM pg_depend
+ WHERE classid = 'pg_class'::regclass AND
+ objid = indexrelid AND
+ refclassid = 'pg_constraint'::regclass AND
+ refobjid = c.oid),
+ conname
+ FROM pg_index
+ JOIN pg_class i ON indexrelid = i.oid
+ JOIN pg_class r ON indrelid = r.oid
+ LEFT JOIN pg_constraint c ON indexrelid = c.conindid
+ WHERE r.relname = searched_relname AND
+ (searched_indnames IS NULL OR i.relname = ANY (searched_indnames))
+ ORDER BY indkey, i.relname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_indexes_from_relation(searched_relname name)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ index_relkind "char",
+ index_relispartition boolean,
+ indisunique boolean,
+ indisprimary boolean,
+ indisexclusion boolean,
+ indkey int2vector,
+ indislive boolean,
+ indisvalid boolean,
+ indisready boolean,
+ indoption int2vector,
+ indcollation oidvector,
+ indimmediate boolean,
+ indisreplident boolean,
+ depends_on_table boolean,
+ depends_on_simple_columns boolean,
+ depends_on_constraint boolean,
+ conname name
+)
+AS $$
+ SELECT * FROM show_some_indexes_from_relation(searched_relname, NULL);
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_some_index_exprs_pred(
+ searched_relname name,
+ searched_indnames name[]
+)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ indpred text,
+ indexprs text
+)
+AS $$
+ SELECT
+ r.relname,
+ i.relname,
+ pg_get_expr(indpred, indrelid, true),
+ pg_get_expr(indexprs, indrelid, true)
+ FROM pg_index
+ JOIN pg_class i ON indexrelid = i.oid
+ JOIN pg_class r ON indrelid = r.oid
+ WHERE r.relname = searched_relname AND
+ (searched_indnames IS NULL OR i.relname = ANY (searched_indnames))
+ ORDER BY indexprs, indpred, indkey, i.relname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_index_exprs_pred(searched_relname name)
+RETURNS TABLE
+(
+ relname name,
+ indname name,
+ indpred text,
+ indexprs text
+)
+AS $$
+ SELECT * FROM show_some_index_exprs_pred(searched_relname, NULL);
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_constraints_named_like(searched_conname name)
+RETURNS TABLE
+(
+ conname name,
+ contype "char",
+ conkey smallint[],
+ condeferrable boolean
+)
+AS $$
+ SELECT conname, contype, conkey, condeferrable
+ FROM pg_constraint
+ WHERE conname LIKE searched_conname
+ ORDER BY conkey, conname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_index_dependencies_on_table_columns
+(
+ searched_indnames name[]
+)
+RETURNS TABLE
+(
+ indname name,
+ indnatts smallint,
+ indnkeyatts smallint,
+ indkey int2vector,
+ attnum smallint,
+ attname name
+)
+AS $$
+ SELECT relname, indnatts, indnkeyatts, indkey, attnum, attname
+ FROM pg_index
+ JOIN pg_class ON indexrelid = pg_class.oid
+ JOIN pg_depend ON indexrelid = objid
+ JOIN pg_attribute ON attrelid = indrelid
+ WHERE relname = ANY (searched_indnames) AND
+ classid = 'pg_class'::regclass AND
+ refclassid = 'pg_class'::regclass AND
+ refobjid = indrelid AND
+ refobjsubid != 0 AND
+ refobjsubid = attnum
+ ORDER BY relname, refobjsubid;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_alteridx_index_dependencies()
+RETURNS TABLE
+(
+ indname name,
+ referenced_indname name
+)
+AS $$
+ SELECT c.relname, ref_c.relname
+ FROM
+ pg_index AS i
+ JOIN pg_class AS c ON c.oid = i.indexrelid
+ JOIN pg_depend ON objid = i.indexrelid
+ JOIN pg_index AS ref_i ON refobjid = ref_i.indexrelid
+ JOIN pg_class AS ref_c ON ref_c.oid = ref_i.indexrelid
+ WHERE classid = 'pg_class'::regclass
+ AND refclassid = 'pg_class'::regclass
+ AND c.relname like '%alteridx%'
+ ORDER BY c.relname, ref_c.relname;
+$$ LANGUAGE SQL;
+--
+CREATE FUNCTION show_alteridx_constraint_dependencies()
+RETURNS TABLE
+(
+ conname name,
+ referenced_conname name
+)
+AS $$
+ SELECT con.conname, ref_con.conname
+ FROM
+ pg_constraint AS con
+ JOIN pg_depend ON objid = con.oid
+ JOIN pg_constraint AS ref_con ON refobjid = ref_con.oid
+ WHERE
+ classid = 'pg_constraint'::regclass AND
+ refclassid = 'pg_constraint'::regclass AND
+ con.conname like '%alteridx%'
+ ORDER BY con.conname, ref_con.conname;
+$$ LANGUAGE SQL;
+--
+--
+--
+CREATE TABLE alteridx_orig(
+id int PRIMARY KEY,
+uniq int CONSTRAINT alteridx_orig_uniq_key UNIQUE NOT NULL,
+parity int,
+msg text UNIQUE,
+ir int4range,
+partition_key int,
+EXCLUDE using gist(ir with &&),
+EXCLUDE USING btree((id + uniq) WITH =) WHERE (id > 2),
+EXCLUDE ((id + uniq) WITH =) WHERE (parity < 4),
+CONSTRAINT alteridx_orig_double UNIQUE(id, uniq),
+CONSTRAINT alteridx_id_key_deferrable UNIQUE (id) DEFERRABLE,
+CHECK (parity > -10));
+CREATE TABLE partitioned_orig_alteridx(
+id int,
+uniq int,
+parity int,
+msg text,
+ir int4range,
+partition_key int UNIQUE,
+UNIQUE (partition_key, id))
+PARTITION BY RANGE (partition_key);
+ALTER TABLE partitioned_orig_alteridx ATTACH PARTITION alteridx_orig
+FOR VALUES FROM (0) TO (20);
+--
+--
+CREATE TABLE another_alteridx(
+id int PRIMARY KEY,
+uniq int UNIQUE,
+parity int,
+msg text,
+ir int4range,
+EXCLUDE USING gist(ir with &&),
+EXCLUDE USING btree((id + 4) WITH =));
+--
+--
+CREATE TABLE third_alteridx(
+ id1 int,
+ id2 int,
+ PRIMARY KEY (id1, id2)
+);
+--
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_orig_double
+ alteridx_orig | alteridx_orig_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_uniq_key
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(10 rows)
+
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ relname | indname | indpred | indexprs
+---------------+------------------------------------+------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_orig_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_id_key_deferrable | |
+ alteridx_orig | alteridx_orig_pkey | |
+ alteridx_orig | alteridx_orig_double | |
+ alteridx_orig | alteridx_orig_uniq_key | |
+ alteridx_orig | alteridx_orig_msg_key | |
+ alteridx_orig | alteridx_orig_ir_excl | |
+ alteridx_orig | alteridx_orig_partition_key_key | |
+ alteridx_orig | alteridx_orig_partition_key_id_key | |
+(10 rows)
+
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key_deferrable | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_orig_double | u | {1,2} | f
+ alteridx_orig_uniq_key | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(11 rows)
+
+SELECT * FROM show_indexes_from_relation('partitioned_orig_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------------------+------------------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------------------
+ partitioned_orig_alteridx | partitioned_orig_alteridx_partition_key_key | I | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | f | t | partitioned_orig_alteridx_partition_key_key
+ partitioned_orig_alteridx | partitioned_orig_alteridx_partition_key_id_key | I | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | partitioned_orig_alteridx_partition_key_id_key
+(2 rows)
+
+SELECT * FROM show_constraints_named_like('partitioned_%');
+ conname | contype | conkey | condeferrable
+------------------------------------------------+---------+--------+---------------
+ partitioned_orig_alteridx_partition_key_key | u | {6} | f
+ partitioned_orig_alteridx_partition_key_id_key | u | {6,1} | f
+(2 rows)
+
+SELECT * FROM show_indexes_from_relation('another_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+------------------+----------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+----------------------------
+ another_alteridx | another_alteridx_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | another_alteridx_expr_excl
+ another_alteridx | another_alteridx_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_pkey
+ another_alteridx | another_alteridx_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_uniq_key
+ another_alteridx | another_alteridx_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_ir_excl
+(4 rows)
+
+SELECT * FROM show_constraints_named_like('another_%');
+ conname | contype | conkey | condeferrable
+----------------------------+---------+--------+---------------
+ another_alteridx_expr_excl | x | {0} | f
+ another_alteridx_pkey | p | {1} | f
+ another_alteridx_uniq_key | u | {2} | f
+ another_alteridx_ir_excl | x | {5} | f
+(4 rows)
+
+--
+--
+-- Checking that constraints work before index replacement
+--
+--
+INSERT INTO alteridx_orig SELECT n, n, n%2, CHR(62+n) || CHR(63+n),
+int4range(2*n,2*n+1), n from generate_series(1,10) as gs(n);
+INSERT INTO another_alteridx SELECT n, n, n%2, CHR(62+n) || CHR(63+n),
+int4range(2*n,2*n+1) from generate_series(1,10) as gs(n);
+--
+INSERT INTO alteridx_orig VALUES(1, 0, 1, 'AA', int4range(102, 103), 15); -- failure here
+ERROR: duplicate key value violates unique constraint "alteridx_orig_pkey"
+DETAIL: Key (id)=(1) already exists.
+INSERT INTO alteridx_orig VALUES(0, 1, 1, 'AA', int4range(104, 105), 15); -- failure here
+ERROR: duplicate key value violates unique constraint "alteridx_orig_uniq_key"
+DETAIL: Key (uniq)=(1) already exists.
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(1, 107), 15); -- failure here
+ERROR: conflicting key value violates exclusion constraint "alteridx_orig_ir_excl"
+DETAIL: Key (ir)=([1,107)) conflicts with existing key (ir)=([2,3)).
+INSERT INTO alteridx_orig VALUES(NULL, 0, 1, 'AA', int4range(102, 107), 15); -- failure here
+ERROR: null value in column "id" of relation "alteridx_orig" violates not-null constraint
+DETAIL: Failing row contains (null, 0, 1, AA, [102,107), 15).
+INSERT INTO alteridx_orig VALUES(0, NULL, 1, 'AA', int4range(102, 107), 15); -- failure here
+ERROR: null value in column "uniq" of relation "alteridx_orig" violates not-null constraint
+DETAIL: Failing row contains (0, null, 1, AA, [102,107), 15).
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(102, 107), 15);
+SELECT * FROM alteridx_orig;
+ id | uniq | parity | msg | ir | partition_key
+----+------+--------+-----+-----------+---------------
+ 1 | 1 | 1 | ?@ | [2,3) | 1
+ 2 | 2 | 0 | @A | [4,5) | 2
+ 3 | 3 | 1 | AB | [6,7) | 3
+ 4 | 4 | 0 | BC | [8,9) | 4
+ 5 | 5 | 1 | CD | [10,11) | 5
+ 6 | 6 | 0 | DE | [12,13) | 6
+ 7 | 7 | 1 | EF | [14,15) | 7
+ 8 | 8 | 0 | FG | [16,17) | 8
+ 9 | 9 | 1 | GH | [18,19) | 9
+ 10 | 10 | 0 | HI | [20,21) | 10
+ 0 | 0 | 1 | AA | [102,107) | 15
+(11 rows)
+
+--
+--
+CREATE UNIQUE INDEX alteridx_new_uniq_key ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_incl ON alteridx_orig(uniq) INCLUDE (ir);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_incl2 ON alteridx_orig(uniq) INCLUDE (parity);
+CREATE UNIQUE INDEX CONCURRENTLY alteridx_new_uniq_key_back ON alteridx_orig USING BTREE(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_pred ON alteridx_orig(uniq) WHERE parity=1;
+CREATE INDEX alteridx_new_uniq_key_no_unique ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_with_msg ON alteridx_orig(uniq, msg);
+CREATE UNIQUE INDEX alteridx_new_msg_key_ops ON alteridx_orig(msg text_pattern_ops);
+CREATE UNIQUE INDEX alteridx_new_pkey ON alteridx_orig(id);
+CREATE INDEX alteridx_new_ir_excl ON alteridx_orig using gist(ir range_ops);
+CREATE INDEX alteridx_new_expr_excl ON alteridx_orig((id + uniq)) WHERE (id > 2);
+CREATE INDEX alteridx_new_expr_excl_hash ON alteridx_orig USING hash((id + uniq)) WHERE (id > 2);
+CREATE INDEX alteridx_new_expr_excl_pred ON alteridx_orig((id + uniq)) WHERE (id > 3);
+CREATE INDEX alteridx_new_expr_excl_pred2 ON alteridx_orig((id + uniq)) WHERE (id > (3 - 1));
+CREATE INDEX alteridx_new_expr_excl_wrong ON alteridx_orig((id - uniq)) WHERE (id > 2);
+CREATE INDEX alteridx_new_expr_excl1 ON alteridx_orig((id + uniq)) WHERE (parity < 4);
+CREATE INDEX another_alteridx_new_expr_excl ON another_alteridx((id + 4));
+CREATE INDEX another_alteridx_new_expr_excl_different ON another_alteridx((id + 2 + 2));
+CREATE INDEX another_alteridx_new_expr_excl_different2 ON another_alteridx((id + (2 + 2)));
+CREATE UNIQUE INDEX alteridx_new_double ON alteridx_orig(id, uniq);
+CREATE INDEX alteridx_new_double_not_unique ON alteridx_orig(id, uniq);
+CREATE UNIQUE INDEX alteridx_id_key ON alteridx_orig(id);
+--
+CREATE UNIQUE INDEX third_alteridx_pkey_new ON third_alteridx(id1, id2);
+CREATE UNIQUE INDEX third_alteridx_pkey_opp ON third_alteridx(id2, id1);
+CREATE UNIQUE INDEX third_alteridx_pkey_single ON third_alteridx(id1);
+CREATE INDEX third_alteridx_pkey_not_unique ON third_alteridx(id1, id2);
+--
+CREATE UNIQUE INDEX alteridx_new_partition_key_key
+ON alteridx_orig(partition_key);
+CREATE UNIQUE INDEX alteridx_new_partition_key_id_key
+ON alteridx_orig(partition_key,id);
+CREATE UNIQUE INDEX partitioned_new_alteridx_partition_key_key
+ON partitioned_orig_alteridx(partition_key);
+CREATE UNIQUE INDEX partitioned_new_alteridx_partition_key_id_key
+ON partitioned_orig_alteridx(partition_key,id);
+--
+CREATE UNIQUE INDEX alteridx_new_uniq_key_opt ON alteridx_orig(uniq);
+UPDATE pg_index SET indoption='1'
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_opt';
+CREATE UNIQUE INDEX alteridx_new_msg_key_coll ON alteridx_orig(msg);
+UPDATE pg_index SET indcollation='12341'
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_msg_key_coll';
+--
+--
+-- Tests for unique constraint --
+--
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_orig_double
+ alteridx_orig | alteridx_new_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_pred | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_uniq_key
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(33 rows)
+
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ relname | indname | indpred | indexprs
+---------------+------------------------------------+--------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_orig_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred | id > 3 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred2 | id > (3 - 1) | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_hash | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_wrong | id > 2 | id - uniq
+ alteridx_orig | alteridx_new_uniq_key_pred | parity = 1 |
+ alteridx_orig | alteridx_id_key | |
+ alteridx_orig | alteridx_id_key_deferrable | |
+ alteridx_orig | alteridx_new_pkey | |
+ alteridx_orig | alteridx_orig_pkey | |
+ alteridx_orig | alteridx_new_double | |
+ alteridx_orig | alteridx_new_double_not_unique | |
+ alteridx_orig | alteridx_orig_double | |
+ alteridx_orig | alteridx_new_uniq_key | |
+ alteridx_orig | alteridx_new_uniq_key_back | |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | |
+ alteridx_orig | alteridx_new_uniq_key_opt | |
+ alteridx_orig | alteridx_orig_uniq_key | |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | |
+ alteridx_orig | alteridx_new_uniq_key_incl | |
+ alteridx_orig | alteridx_new_msg_key_coll | |
+ alteridx_orig | alteridx_new_msg_key_ops | |
+ alteridx_orig | alteridx_orig_msg_key | |
+ alteridx_orig | alteridx_new_ir_excl | |
+ alteridx_orig | alteridx_orig_ir_excl | |
+ alteridx_orig | alteridx_new_partition_key_key | |
+ alteridx_orig | alteridx_orig_partition_key_key | |
+ alteridx_orig | alteridx_new_partition_key_id_key | |
+ alteridx_orig | alteridx_orig_partition_key_id_key | |
+(33 rows)
+
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_new_uniq_key_incl",
+ "alteridx_new_uniq_key_incl2",
+ "alteridx_orig_expr_excl1",
+ "alteridx_new_expr_excl1"}'::name[]);
+ indname | indnatts | indnkeyatts | indkey | attnum | attname
+-----------------------------+----------+-------------+--------+--------+---------
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 1 | id
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 2 | uniq
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 3 | parity
+ alteridx_new_uniq_key_incl | 2 | 1 | 2 5 | 2 | uniq
+ alteridx_new_uniq_key_incl | 2 | 1 | 2 5 | 5 | ir
+ alteridx_new_uniq_key_incl2 | 2 | 1 | 2 3 | 2 | uniq
+ alteridx_new_uniq_key_incl2 | 2 | 1 | 2 3 | 3 | parity
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 1 | id
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 2 | uniq
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 3 | parity
+(10 rows)
+
+SELECT * FROM show_indexes_from_relation('partitioned_orig_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------------------+------------------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------------------
+ partitioned_orig_alteridx | partitioned_new_alteridx_partition_key_key | I | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | t | f |
+ partitioned_orig_alteridx | partitioned_orig_alteridx_partition_key_key | I | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | f | t | partitioned_orig_alteridx_partition_key_key
+ partitioned_orig_alteridx | partitioned_new_alteridx_partition_key_id_key | I | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ partitioned_orig_alteridx | partitioned_orig_alteridx_partition_key_id_key | I | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | partitioned_orig_alteridx_partition_key_id_key
+(4 rows)
+
+SELECT * FROM show_indexes_from_relation('another_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+------------------+-------------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+----------------------------
+ another_alteridx | another_alteridx_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | another_alteridx_expr_excl
+ another_alteridx | another_alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ another_alteridx | another_alteridx_new_expr_excl_different | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ another_alteridx | another_alteridx_new_expr_excl_different2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ another_alteridx | another_alteridx_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_pkey
+ another_alteridx | another_alteridx_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_uniq_key
+ another_alteridx | another_alteridx_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_ir_excl
+(7 rows)
+
+SELECT * FROM show_index_exprs_pred('another_alteridx');
+ relname | indname | indpred | indexprs
+------------------+-------------------------------------------+---------+--------------
+ another_alteridx | another_alteridx_new_expr_excl_different | | id + 2 + 2
+ another_alteridx | another_alteridx_expr_excl | | id + 4
+ another_alteridx | another_alteridx_new_expr_excl | | id + 4
+ another_alteridx | another_alteridx_new_expr_excl_different2 | | id + (2 + 2)
+ another_alteridx | another_alteridx_pkey | |
+ another_alteridx | another_alteridx_uniq_key | |
+ another_alteridx | another_alteridx_ir_excl | |
+(7 rows)
+
+SELECT * FROM show_constraints_named_like('another_%');
+ conname | contype | conkey | condeferrable
+----------------------------+---------+--------+---------------
+ another_alteridx_expr_excl | x | {0} | f
+ another_alteridx_pkey | p | {1} | f
+ another_alteridx_uniq_key | u | {2} | f
+ another_alteridx_ir_excl | x | {5} | f
+(4 rows)
+
+DROP INDEX alteridx_orig_uniq_key; -- failure here
+ERROR: cannot drop index alteridx_orig_uniq_key because constraint alteridx_orig_uniq_key on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_orig_uniq_key on table alteridx_orig instead.
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key_deferrable | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_orig_double | u | {1,2} | f
+ alteridx_orig_uniq_key | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(11 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_parity_check
+USING INDEX alteridx_new_uniq_key; -- failure here
+ERROR: constraint "alteridx_orig_parity_check" of relation "alteridx_orig" is not a primary key, unique constraint, exclusion constraint or foreign constraint
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_uniq_key
+USING INDEX alteridx_orig_uniq_key;
+NOTICE: constraint "alteridx_orig_uniq_key" already uses index "alteridx_orig_uniq_key", skipping
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_uniq_key
+USING INDEX alteridx_new_uniq_key;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_uniq_key" to "alteridx_new_uniq_key"
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key_deferrable | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_orig_double | u | {1,2} | f
+ alteridx_new_uniq_key | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(11 rows)
+
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_orig_double
+ alteridx_orig | alteridx_new_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_pred | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(33 rows)
+
+DROP INDEX alteridx_orig_uniq_key;
+DROP INDEX alteridx_new_uniq_key; -- failure here
+ERROR: cannot drop index alteridx_new_uniq_key because constraint alteridx_new_uniq_key on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_new_uniq_key on table alteridx_orig instead.
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_orig_double
+ alteridx_orig | alteridx_new_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_pred | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(32 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key
+USING INDEX alteridx_new_uniq_key_incl2;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_new_uniq_key" to "alteridx_new_uniq_key_incl2"
+--
+--
+-- Checking that all dependencies on simply-referenced columns are correctly
+-- added for old constraint index (included columns may differ in the old and
+-- new constraint index).
+--
+--
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+-----------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-----------------------------
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_incl2
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+(2 rows)
+
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+ indname | indnatts | indnkeyatts | indkey | attnum | attname
+----------------------------+----------+-------------+--------+--------+---------
+ alteridx_new_uniq_key_incl | 2 | 1 | 2 5 | 2 | uniq
+ alteridx_new_uniq_key_incl | 2 | 1 | 2 5 | 5 | ir
+(2 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_incl2
+USING INDEX alteridx_new_uniq_key_incl;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_new_uniq_key_incl2" to "alteridx_new_uniq_key_incl"
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+-----------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+----------------------------
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_incl
+(2 rows)
+
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_new_uniq_key_incl", "alteridx_new_uniq_key_incl2"}'::name[]);
+ indname | indnatts | indnkeyatts | indkey | attnum | attname
+-----------------------------+----------+-------------+--------+--------+---------
+ alteridx_new_uniq_key_incl2 | 2 | 1 | 2 3 | 2 | uniq
+ alteridx_new_uniq_key_incl2 | 2 | 1 | 2 3 | 3 | parity
+(2 rows)
+
+--
+DROP INDEX alteridx_new_uniq_key;
+DROP INDEX alteridx_new_uniq_key_incl; -- failure here
+ERROR: cannot drop index alteridx_new_uniq_key_incl because constraint alteridx_new_uniq_key_incl on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_new_uniq_key_incl on table alteridx_orig instead.
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key_deferrable | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_orig_double | u | {1,2} | f
+ alteridx_new_uniq_key_incl | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(11 rows)
+
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_orig_double
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_pred | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_incl
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(31 rows)
+
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ relname | indname | indpred | indexprs
+---------------+------------------------------------+--------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_orig_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred | id > 3 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred2 | id > (3 - 1) | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_hash | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_wrong | id > 2 | id - uniq
+ alteridx_orig | alteridx_new_uniq_key_pred | parity = 1 |
+ alteridx_orig | alteridx_id_key | |
+ alteridx_orig | alteridx_id_key_deferrable | |
+ alteridx_orig | alteridx_new_pkey | |
+ alteridx_orig | alteridx_orig_pkey | |
+ alteridx_orig | alteridx_new_double | |
+ alteridx_orig | alteridx_new_double_not_unique | |
+ alteridx_orig | alteridx_orig_double | |
+ alteridx_orig | alteridx_new_uniq_key_back | |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | |
+ alteridx_orig | alteridx_new_uniq_key_opt | |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | |
+ alteridx_orig | alteridx_new_uniq_key_incl | |
+ alteridx_orig | alteridx_new_msg_key_coll | |
+ alteridx_orig | alteridx_new_msg_key_ops | |
+ alteridx_orig | alteridx_orig_msg_key | |
+ alteridx_orig | alteridx_new_ir_excl | |
+ alteridx_orig | alteridx_orig_ir_excl | |
+ alteridx_orig | alteridx_new_partition_key_key | |
+ alteridx_orig | alteridx_orig_partition_key_key | |
+ alteridx_orig | alteridx_new_partition_key_id_key | |
+ alteridx_orig | alteridx_orig_partition_key_id_key | |
+(31 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_incl
+USING INDEX alteridx_new_uniq_key_back;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_new_uniq_key_incl" to "alteridx_new_uniq_key_back"
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key_deferrable | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_orig_double | u | {1,2} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(11 rows)
+
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_pred; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_pred"
+DETAIL: Either none or both indexes must have partial index predicates.
+DROP INDEX alteridx_new_uniq_key_pred;
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_no_unique; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_no_unique"
+DETAIL: Both indexes must be either unique or not.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_msg_key
+USING INDEX alteridx_new_msg_key_coll; -- failure here
+ERROR: index in constraint "alteridx_orig_msg_key" cannot be replaced by "alteridx_new_msg_key_coll"
+DETAIL: Indexes must have the same collation.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_msg_key
+USING INDEX alteridx_new_msg_key_ops; -- failure here
+ERROR: index in constraint "alteridx_orig_msg_key" cannot be replaced by "alteridx_new_msg_key_ops"
+DETAIL: Indexes must have the same operator class.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_msg_key
+USING INDEX alteridx_new_uniq_key_incl; -- failure here
+ERROR: index in constraint "alteridx_orig_msg_key" cannot be replaced by "alteridx_new_uniq_key_incl"
+DETAIL: Indexes must have the same key columns.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX another_alteridx_uniq_key; -- failure here
+ERROR: "another_alteridx_uniq_key" is not an index for table "alteridx_orig"
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_with_msg; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_with_msg"
+DETAIL: Indexes must have the same number of key columns.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_opt; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_opt"
+DETAIL: Indexes must have the same per-column flag bits.
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_double
+USING INDEX alteridx_new_uniq_key_incl; -- failure here
+ERROR: index in constraint "alteridx_orig_double" cannot be replaced by "alteridx_new_uniq_key_incl"
+DETAIL: Indexes must have the same number of key columns.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_double
+USING INDEX alteridx_new_double_not_unique; -- failure here
+ERROR: index in constraint "alteridx_orig_double" cannot be replaced by "alteridx_new_double_not_unique"
+DETAIL: Both indexes must be either unique or not.
+--
+--
+-- Checking the notification if the replica identity index is no longer used in
+-- the constraint.
+--
+--
+ALTER TABLE alteridx_orig REPLICA IDENTITY USING INDEX alteridx_orig_double;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | t | f | f | t | alteridx_orig_double
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(30 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_double
+USING INDEX alteridx_new_double;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_double" to "alteridx_new_double"
+NOTICE: replaced index "alteridx_orig_double" is still chosen as replica identity
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_double
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | t | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(30 rows)
+
+--
+--
+-- Checking that deferrable constraints cannot use replica identity index
+--
+--
+ALTER TABLE alteridx_orig REPLICA IDENTITY USING INDEX alteridx_id_key;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | t | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_double
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(30 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_id_key_deferrable
+USING INDEX alteridx_id_key; -- failure here
+ERROR: index in constraint "alteridx_id_key_deferrable" cannot be replaced by "alteridx_id_key"
+DETAIL: Deferrable constraint cannot use replica identity index.
+ALTER TABLE alteridx_orig REPLICA IDENTITY DEFAULT;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key_deferrable
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_double
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(30 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_id_key_deferrable
+USING INDEX alteridx_id_key;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_id_key_deferrable" to "alteridx_id_key"
+--
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_new_double | u | {1,2} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(11 rows)
+
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_hash | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_new_expr_excl_wrong | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_double
+ alteridx_orig | alteridx_new_double_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_double | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(30 rows)
+
+ALTER TABLE alteridx_orig DROP CONSTRAINT alteridx_orig_double; -- failure here
+ERROR: constraint "alteridx_orig_double" of relation "alteridx_orig" does not exist
+ALTER TABLE alteridx_orig DROP CONSTRAINT alteridx_new_double;
+DROP INDEX alteridx_orig_double;
+DROP INDEX alteridx_new_double_not_unique;
+--
+--
+-- Tests for primary key constraint --
+--
+--
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key | u | {1} | t
+ alteridx_orig_pkey | p | {1} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(10 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_pkey
+USING INDEX alteridx_new_pkey;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_pkey" to "alteridx_new_pkey"
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key | u | {1} | t
+ alteridx_new_pkey | p | {1} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_orig_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(10 rows)
+
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_pkey
+USING INDEX another_alteridx_uniq_key; -- failure here
+ERROR: "another_alteridx_uniq_key" is not an index for table "alteridx_orig"
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_pkey
+USING INDEX another_alteridx_pkey; -- failure here
+ERROR: "another_alteridx_pkey" is not an index for table "alteridx_orig"
+--
+SELECT * FROM show_indexes_from_relation('third_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+----------------+--------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+---------------------
+ third_alteridx | third_alteridx_pkey_single | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ third_alteridx | third_alteridx_pkey | i | f | t | t | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | third_alteridx_pkey
+ third_alteridx | third_alteridx_pkey_new | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ third_alteridx | third_alteridx_pkey_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ third_alteridx | third_alteridx_pkey_opp | i | f | t | f | f | 2 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+(5 rows)
+
+SELECT * FROM show_constraints_named_like('third_%');
+ conname | contype | conkey | condeferrable
+---------------------+---------+--------+---------------
+ third_alteridx_pkey | p | {1,2} | f
+(1 row)
+
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_opp; -- failure here
+ERROR: index in constraint "third_alteridx_pkey" cannot be replaced by "third_alteridx_pkey_opp"
+DETAIL: Indexes must have the same key columns.
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_not_unique; -- failure here
+ERROR: index in constraint "third_alteridx_pkey" cannot be replaced by "third_alteridx_pkey_not_unique"
+DETAIL: Both indexes must be either unique or not.
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_single; -- failure here
+ERROR: index in constraint "third_alteridx_pkey" cannot be replaced by "third_alteridx_pkey_single"
+DETAIL: Indexes must have the same number of key columns.
+ALTER TABLE third_alteridx ALTER CONSTRAINT third_alteridx_pkey
+USING INDEX third_alteridx_pkey_new;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "third_alteridx_pkey" to "third_alteridx_pkey_new"
+SELECT * FROM show_indexes_from_relation('third_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+----------------+--------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-------------------------
+ third_alteridx | third_alteridx_pkey_single | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ third_alteridx | third_alteridx_pkey | i | f | t | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ third_alteridx | third_alteridx_pkey_new | i | f | t | t | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | third_alteridx_pkey_new
+ third_alteridx | third_alteridx_pkey_not_unique | i | f | f | f | f | 1 2 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ third_alteridx | third_alteridx_pkey_opp | i | f | t | f | f | 2 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+(5 rows)
+
+SELECT * FROM show_constraints_named_like('third_%');
+ conname | contype | conkey | condeferrable
+-------------------------+---------+--------+---------------
+ third_alteridx_pkey_new | p | {1,2} | f
+(1 row)
+
+--
+--
+-- Tests for exclusion constraint --
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_ir_excl
+USING INDEX alteridx_new_ir_excl;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_ir_excl" to "alteridx_new_ir_excl"
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+------------------------------------+---------+--------+---------------
+ alteridx_orig_expr_excl | x | {0} | f
+ alteridx_orig_expr_excl1 | x | {0} | f
+ alteridx_id_key | u | {1} | t
+ alteridx_new_pkey | p | {1} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_new_ir_excl | x | {5} | f
+ alteridx_orig_partition_key_key | u | {6} | f
+ alteridx_orig_partition_key_id_key | u | {6,1} | f
+(10 rows)
+
+--
+ALTER TABLE alteridx_orig ADD CONSTRAINT alteridx_new_expr_excl2
+EXCLUDE ((id + uniq) WITH =) WHERE (id > 2);
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl2; -- failure here
+ERROR: index "alteridx_new_expr_excl2" is already associated with a constraint
+ALTER TABLE alteridx_orig DROP CONSTRAINT alteridx_new_expr_excl2;
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_orig_ir_excl; -- failure here
+ERROR: index in constraint "alteridx_orig_expr_excl" cannot be replaced by "alteridx_orig_ir_excl"
+DETAIL: Indexes must have the same access methods.
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+ERROR: index in constraint "alteridx_orig_expr_excl" cannot be replaced by "alteridx_new_expr_excl_wrong"
+DETAIL: Indexes must have the same non-column attributes.
+--
+--
+-- Checking that after simplifying the constants from index predicates some
+-- indexes are considered equal.
+--
+--
+SELECT * FROM show_some_index_exprs_pred(
+ 'alteridx_orig',
+ '{"alteridx_orig_expr_excl",
+ "alteridx_new_expr_excl_pred",
+ "alteridx_new_expr_excl_pred2"}'::name[]);
+ relname | indname | indpred | indexprs
+---------------+------------------------------+--------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred | id > 3 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred2 | id > (3 - 1) | id + uniq
+(3 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_pred; -- failure here
+ERROR: index in constraint "alteridx_orig_expr_excl" cannot be replaced by "alteridx_new_expr_excl_pred"
+DETAIL: Indexes must have the same partial index predicates.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_pred2;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_expr_excl" to "alteridx_new_expr_excl_pred2"
+--
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_expr_excl_pred2
+USING INDEX alteridx_new_expr_excl_hash; -- failure here
+ERROR: index in constraint "alteridx_new_expr_excl_pred2" cannot be replaced by "alteridx_new_expr_excl_hash"
+DETAIL: Indexes must have the same access methods.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_expr_excl_pred2
+USING INDEX alteridx_new_expr_excl;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_new_expr_excl_pred2" to "alteridx_new_expr_excl"
+--
+--
+-- Checking that all dependencies on columns from index expressions and/or index
+-- predicate are not removed for new constraint index (they always exist both
+-- for standalone and constraint indexes).
+--
+--
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+--------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+--------------------------
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_orig_expr_excl1
+(2 rows)
+
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+ indname | indnatts | indnkeyatts | indkey | attnum | attname
+--------------------------+----------+-------------+--------+--------+---------
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 1 | id
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 2 | uniq
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 3 | parity
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 1 | id
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 2 | uniq
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 3 | parity
+(6 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl1
+USING INDEX alteridx_new_expr_excl1;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_expr_excl1" to "alteridx_new_expr_excl1"
+SELECT * FROM show_some_indexes_from_relation(
+ 'alteridx_orig',
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+--------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-------------------------
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+(2 rows)
+
+SELECT * FROM show_index_dependencies_on_table_columns(
+ '{"alteridx_orig_expr_excl1", "alteridx_new_expr_excl1"}'::name[]);
+ indname | indnatts | indnkeyatts | indkey | attnum | attname
+--------------------------+----------+-------------+--------+--------+---------
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 1 | id
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 2 | uniq
+ alteridx_new_expr_excl1 | 1 | 1 | 0 | 3 | parity
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 1 | id
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 2 | uniq
+ alteridx_orig_expr_excl1 | 1 | 1 | 0 | 3 | parity
+(6 rows)
+
+--
+--
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ relname | indname | indpred | indexprs
+---------------+------------------------------------+--------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_orig_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred | id > 3 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred2 | id > (3 - 1) | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_hash | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_wrong | id > 2 | id - uniq
+ alteridx_orig | alteridx_id_key | |
+ alteridx_orig | alteridx_id_key_deferrable | |
+ alteridx_orig | alteridx_new_pkey | |
+ alteridx_orig | alteridx_orig_pkey | |
+ alteridx_orig | alteridx_new_uniq_key_back | |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | |
+ alteridx_orig | alteridx_new_uniq_key_opt | |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | |
+ alteridx_orig | alteridx_new_uniq_key_incl | |
+ alteridx_orig | alteridx_new_msg_key_coll | |
+ alteridx_orig | alteridx_new_msg_key_ops | |
+ alteridx_orig | alteridx_orig_msg_key | |
+ alteridx_orig | alteridx_new_ir_excl | |
+ alteridx_orig | alteridx_orig_ir_excl | |
+ alteridx_orig | alteridx_new_partition_key_key | |
+ alteridx_orig | alteridx_orig_partition_key_key | |
+ alteridx_orig | alteridx_new_partition_key_id_key | |
+ alteridx_orig | alteridx_orig_partition_key_id_key | |
+(27 rows)
+
+DROP INDEX alteridx_new_expr_excl_wrong;
+ALTER TABLE alteridx_does_not_exist ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+ERROR: relation "alteridx_does_not_exist" does not exist
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+ERROR: constraint "alteridx_orig_expr_excl" of relation "alteridx_orig" does not exist
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_expr_excl
+USING INDEX alteridx_new_expr_excl_wrong; -- failure here
+ERROR: index "alteridx_new_expr_excl_wrong" for table "alteridx_orig" does not exist
+--
+DROP INDEX alteridx_new_expr_excl_pred;
+DROP INDEX alteridx_orig_expr_excl;
+DROP INDEX alteridx_new_expr_excl_hash;
+DROP INDEX alteridx_new_expr_excl; -- failure here
+ERROR: cannot drop index alteridx_new_expr_excl because constraint alteridx_new_expr_excl on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_new_expr_excl on table alteridx_orig instead.
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ relname | indname | indpred | indexprs
+---------------+------------------------------------+--------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred2 | id > (3 - 1) | id + uniq
+ alteridx_orig | alteridx_id_key | |
+ alteridx_orig | alteridx_id_key_deferrable | |
+ alteridx_orig | alteridx_new_pkey | |
+ alteridx_orig | alteridx_orig_pkey | |
+ alteridx_orig | alteridx_new_uniq_key_back | |
+ alteridx_orig | alteridx_new_uniq_key_no_unique | |
+ alteridx_orig | alteridx_new_uniq_key_opt | |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | |
+ alteridx_orig | alteridx_new_uniq_key_incl | |
+ alteridx_orig | alteridx_new_msg_key_coll | |
+ alteridx_orig | alteridx_new_msg_key_ops | |
+ alteridx_orig | alteridx_orig_msg_key | |
+ alteridx_orig | alteridx_new_ir_excl | |
+ alteridx_orig | alteridx_orig_ir_excl | |
+ alteridx_orig | alteridx_new_partition_key_key | |
+ alteridx_orig | alteridx_orig_partition_key_key | |
+ alteridx_orig | alteridx_new_partition_key_id_key | |
+ alteridx_orig | alteridx_orig_partition_key_id_key | |
+(23 rows)
+
+ALTER TABLE another_alteridx ALTER CONSTRAINT another_alteridx_expr_excl
+USING INDEX another_alteridx_new_expr_excl;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "another_alteridx_expr_excl" to "another_alteridx_new_expr_excl"
+--
+--
+-- Checking that after simplifying the constants from index expressions some
+-- indexes are considered equal.
+--
+--
+SELECT * FROM show_some_index_exprs_pred(
+ 'another_alteridx',
+ '{"another_alteridx_new_expr_excl",
+ "another_alteridx_new_expr_excl_different",
+ "another_alteridx_new_expr_excl_different2"}'::name[]);
+ relname | indname | indpred | indexprs
+------------------+-------------------------------------------+---------+--------------
+ another_alteridx | another_alteridx_new_expr_excl_different | | id + 2 + 2
+ another_alteridx | another_alteridx_new_expr_excl | | id + 4
+ another_alteridx | another_alteridx_new_expr_excl_different2 | | id + (2 + 2)
+(3 rows)
+
+ALTER TABLE another_alteridx ALTER CONSTRAINT another_alteridx_new_expr_excl
+USING INDEX another_alteridx_new_expr_excl_different; -- failure here
+ERROR: index in constraint "another_alteridx_new_expr_excl" cannot be replaced by "another_alteridx_new_expr_excl_different"
+DETAIL: Indexes must have the same non-column attributes.
+ALTER TABLE another_alteridx ALTER CONSTRAINT another_alteridx_new_expr_excl
+USING INDEX another_alteridx_new_expr_excl_different2;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "another_alteridx_new_expr_excl" to "another_alteridx_new_expr_excl_different2"
+SELECT * FROM show_indexes_from_relation('another_alteridx');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+------------------+-------------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-------------------------------------------
+ another_alteridx | another_alteridx_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ another_alteridx | another_alteridx_new_expr_excl | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ another_alteridx | another_alteridx_new_expr_excl_different | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ another_alteridx | another_alteridx_new_expr_excl_different2 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | another_alteridx_new_expr_excl_different2
+ another_alteridx | another_alteridx_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_pkey
+ another_alteridx | another_alteridx_uniq_key | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_uniq_key
+ another_alteridx | another_alteridx_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | another_alteridx_ir_excl
+(7 rows)
+
+SELECT * FROM show_constraints_named_like('another_%');
+ conname | contype | conkey | condeferrable
+-------------------------------------------+---------+--------+---------------
+ another_alteridx_new_expr_excl_different2 | x | {0} | f
+ another_alteridx_pkey | p | {1} | f
+ another_alteridx_uniq_key | u | {2} | f
+ another_alteridx_ir_excl | x | {5} | f
+(4 rows)
+
+--
+--
+-- Checking that DDL changes can be rolled back
+--
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_pkey
+ alteridx_orig | alteridx_orig_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(23 rows)
+
+BEGIN;
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_pkey
+USING INDEX alteridx_orig_pkey;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_new_pkey" to "alteridx_orig_pkey"
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_pkey
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(23 rows)
+
+ROLLBACK;
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_pkey
+ alteridx_orig | alteridx_orig_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | t | t | f | f | 6 | t | t | t | 0 | 0 | t | f | t | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | t | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | t | f | t | alteridx_orig_partition_key_id_key
+(23 rows)
+
+--
+--
+-- Checking constraints in partitions and partitioned tables
+--
+--
+SELECT * FROM show_alteridx_index_dependencies();
+ indname | referenced_indname
+------------------------------------+------------------------------------------------
+ alteridx_new_partition_key_id_key | partitioned_new_alteridx_partition_key_id_key
+ alteridx_new_partition_key_key | partitioned_new_alteridx_partition_key_key
+ alteridx_orig_partition_key_id_key | partitioned_orig_alteridx_partition_key_id_key
+ alteridx_orig_partition_key_key | partitioned_orig_alteridx_partition_key_key
+(4 rows)
+
+SELECT * FROM show_alteridx_constraint_dependencies();
+ conname | referenced_conname
+------------------------------------+------------------------------------------------
+ alteridx_orig_partition_key_id_key | partitioned_orig_alteridx_partition_key_id_key
+ alteridx_orig_partition_key_key | partitioned_orig_alteridx_partition_key_key
+(2 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_key
+USING INDEX alteridx_new_partition_key_key; -- failure here
+ERROR: index in constraint "alteridx_orig_partition_key_key" cannot be replaced by "alteridx_new_partition_key_key"
+DETAIL: One of the indexes is a partition index.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_id_key
+USING INDEX alteridx_new_partition_key_id_key; -- failure here
+ERROR: index in constraint "alteridx_orig_partition_key_id_key" cannot be replaced by "alteridx_new_partition_key_id_key"
+DETAIL: One of the indexes is a partition index.
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_key
+USING INDEX partitioned_new_alteridx_partition_key_key; -- failure here
+ERROR: index in constraint "partitioned_orig_alteridx_partition_key_key" cannot be replaced by "partitioned_new_alteridx_partition_key_key"
+DETAIL: One of the indexes is a partitioned index.
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_id_key
+USING INDEX partitioned_new_alteridx_partition_key_id_key; -- failure here
+ERROR: index in constraint "partitioned_orig_alteridx_partition_key_id_key" cannot be replaced by "partitioned_new_alteridx_partition_key_id_key"
+DETAIL: One of the indexes is a partitioned index.
+ALTER TABLE partitioned_orig_alteridx DETACH PARTITION alteridx_orig;
+SELECT * FROM show_alteridx_index_dependencies();
+ indname | referenced_indname
+---------+--------------------
+(0 rows)
+
+SELECT * FROM show_alteridx_constraint_dependencies();
+ conname | referenced_conname
+---------+--------------------
+(0 rows)
+
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+------------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_pkey
+ alteridx_orig | alteridx_orig_pkey | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_no_unique | i | f | f | f | f | 2 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_opt | i | f | t | f | f | 2 | t | t | t | 1 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl | i | f | t | f | f | 2 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_coll | i | f | t | f | f | 4 | t | t | t | 0 | 12341 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_msg_key_ops | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_orig_ir_excl | i | f | f | f | f | 5 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_orig_partition_key_key
+ alteridx_orig | alteridx_new_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_orig_partition_key_id_key
+(23 rows)
+
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_key
+USING INDEX alteridx_new_partition_key_key;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_partition_key_key" to "alteridx_new_partition_key_key"
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_orig_partition_key_id_key
+USING INDEX alteridx_new_partition_key_id_key;
+NOTICE: ALTER TABLE / ALTER CONSTRAINT USING INDEX will rename constraint "alteridx_orig_partition_key_id_key" to "alteridx_new_partition_key_id_key"
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_key
+USING INDEX partitioned_new_alteridx_partition_key_key; -- failure here
+ERROR: index in constraint "partitioned_orig_alteridx_partition_key_key" cannot be replaced by "partitioned_new_alteridx_partition_key_key"
+DETAIL: One of the indexes is a partitioned index.
+ALTER TABLE partitioned_orig_alteridx
+ALTER CONSTRAINT partitioned_orig_alteridx_partition_key_id_key
+USING INDEX partitioned_new_alteridx_partition_key_id_key; -- failure here
+ERROR: index in constraint "partitioned_orig_alteridx_partition_key_id_key" cannot be replaced by "partitioned_new_alteridx_partition_key_id_key"
+DETAIL: One of the indexes is a partitioned index.
+--
+--
+-- Dropping replaced indexes
+--
+--
+DROP INDEX alteridx_orig_ir_excl;
+DROP INDEX alteridx_orig_pkey;
+DROP INDEX alteridx_new_uniq_key_no_unique;
+DROP INDEX alteridx_new_uniq_key_incl;
+DROP INDEX alteridx_new_msg_key_coll;
+DROP INDEX alteridx_new_msg_key_ops;
+--
+-- Trying to drop indexes used in constraints after replacement
+--
+DROP INDEX alteridx_new_pkey; -- failure here
+ERROR: cannot drop index alteridx_new_pkey because constraint alteridx_new_pkey on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_new_pkey on table alteridx_orig instead.
+DROP INDEX alteridx_new_uniq_key_back; -- failure here
+ERROR: cannot drop index alteridx_new_uniq_key_back because constraint alteridx_new_uniq_key_back on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_new_uniq_key_back on table alteridx_orig instead.
+DROP INDEX alteridx_orig_msg_key; -- failure here
+ERROR: cannot drop index alteridx_orig_msg_key because constraint alteridx_orig_msg_key on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_orig_msg_key on table alteridx_orig instead.
+DROP INDEX alteridx_new_ir_excl; -- failure here
+ERROR: cannot drop index alteridx_new_ir_excl because constraint alteridx_new_ir_excl on table alteridx_orig requires it
+HINT: You can drop constraint alteridx_new_ir_excl on table alteridx_orig instead.
+DROP INDEX alteridx_new_uniq_key_opt;
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-----------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_pkey
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_partition_key_key
+ alteridx_orig | alteridx_orig_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_partition_key_id_key
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+(16 rows)
+
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+-----------------------------------+---------+--------+---------------
+ alteridx_new_expr_excl | x | {0} | f
+ alteridx_new_expr_excl1 | x | {0} | f
+ alteridx_id_key | u | {1} | t
+ alteridx_new_pkey | p | {1} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_new_ir_excl | x | {5} | f
+ alteridx_new_partition_key_key | u | {6} | f
+ alteridx_new_partition_key_id_key | u | {6,1} | f
+(10 rows)
+
+--
+--
+-- Checking that indexes unavailable for use can't be picked for replacement
+--
+--
+CREATE UNIQUE INDEX alteridx_new_uniq_key_not_live ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_not_valid ON alteridx_orig(uniq);
+CREATE UNIQUE INDEX alteridx_new_uniq_key_not_ready ON alteridx_orig(uniq);
+UPDATE pg_index SET indislive=false
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_not_live';
+UPDATE pg_index SET indisvalid=false
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_not_valid';
+UPDATE pg_index SET indisready=false
+FROM pg_class i WHERE indexrelid = i.oid AND i.relname = 'alteridx_new_uniq_key_not_ready';
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-----------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_pkey
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_not_live | i | f | t | f | f | 2 | f | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_not_ready | i | f | t | f | f | 2 | t | t | f | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_not_valid | i | f | t | f | f | 2 | t | f | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_partition_key_key
+ alteridx_orig | alteridx_orig_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_partition_key_id_key
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+(19 rows)
+
+--
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_not_live; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_not_live"
+DETAIL: One of the indexes is being dropped.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_not_valid; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_not_valid"
+DETAIL: One of the indexes is not valid for queries.
+ALTER TABLE alteridx_orig ALTER CONSTRAINT alteridx_new_uniq_key_back
+USING INDEX alteridx_new_uniq_key_not_ready; -- failure here
+ERROR: index in constraint "alteridx_new_uniq_key_back" cannot be replaced by "alteridx_new_uniq_key_not_ready"
+DETAIL: One of the indexes is not ready for inserts.
+--
+DROP INDEX alteridx_new_uniq_key_not_live;
+DROP INDEX alteridx_new_uniq_key_not_valid;
+DROP INDEX alteridx_new_uniq_key_not_ready;
+--
+SELECT * FROM show_indexes_from_relation('alteridx_orig');
+ relname | indname | index_relkind | index_relispartition | indisunique | indisprimary | indisexclusion | indkey | indislive | indisvalid | indisready | indoption | indcollation | indimmediate | indisreplident | depends_on_table | depends_on_simple_columns | depends_on_constraint | conname
+---------------+------------------------------------+---------------+----------------------+-------------+--------------+----------------+--------+-----------+------------+------------+-----------+--------------+--------------+----------------+------------------+---------------------------+-----------------------+-----------------------------------
+ alteridx_orig | alteridx_new_expr_excl | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl
+ alteridx_orig | alteridx_new_expr_excl1 | i | f | f | f | t | 0 | t | t | t | 0 | 0 | t | f | f | t | t | alteridx_new_expr_excl1
+ alteridx_orig | alteridx_new_expr_excl_pred2 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_orig_expr_excl1 | i | f | f | f | f | 0 | t | t | t | 0 | 0 | t | f | t | t | f |
+ alteridx_orig | alteridx_id_key | i | f | t | f | f | 1 | t | t | t | 0 | 0 | f | f | f | f | t | alteridx_id_key
+ alteridx_orig | alteridx_id_key_deferrable | i | f | t | f | f | 1 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_pkey | i | f | t | t | f | 1 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_pkey
+ alteridx_orig | alteridx_new_uniq_key_back | i | f | t | f | f | 2 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_uniq_key_back
+ alteridx_orig | alteridx_new_uniq_key_incl2 | i | f | t | f | f | 2 3 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | i | f | t | f | f | 2 4 | t | t | t | 0 0 | 0 100 | t | f | f | t | f |
+ alteridx_orig | alteridx_orig_msg_key | i | f | t | f | f | 4 | t | t | t | 0 | 100 | t | f | f | f | t | alteridx_orig_msg_key
+ alteridx_orig | alteridx_new_ir_excl | i | f | f | f | t | 5 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_ir_excl
+ alteridx_orig | alteridx_new_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | f | t | alteridx_new_partition_key_key
+ alteridx_orig | alteridx_orig_partition_key_key | i | f | t | f | f | 6 | t | t | t | 0 | 0 | t | f | f | t | f |
+ alteridx_orig | alteridx_new_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | f | t | alteridx_new_partition_key_id_key
+ alteridx_orig | alteridx_orig_partition_key_id_key | i | f | t | f | f | 6 1 | t | t | t | 0 0 | 0 0 | t | f | f | t | f |
+(16 rows)
+
+SELECT * FROM show_index_exprs_pred('alteridx_orig');
+ relname | indname | indpred | indexprs
+---------------+------------------------------------+--------------+-----------
+ alteridx_orig | alteridx_orig_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl | id > 2 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl1 | parity < 4 | id + uniq
+ alteridx_orig | alteridx_new_expr_excl_pred2 | id > (3 - 1) | id + uniq
+ alteridx_orig | alteridx_id_key | |
+ alteridx_orig | alteridx_id_key_deferrable | |
+ alteridx_orig | alteridx_new_pkey | |
+ alteridx_orig | alteridx_new_uniq_key_back | |
+ alteridx_orig | alteridx_new_uniq_key_incl2 | |
+ alteridx_orig | alteridx_new_uniq_key_with_msg | |
+ alteridx_orig | alteridx_orig_msg_key | |
+ alteridx_orig | alteridx_new_ir_excl | |
+ alteridx_orig | alteridx_new_partition_key_key | |
+ alteridx_orig | alteridx_orig_partition_key_key | |
+ alteridx_orig | alteridx_new_partition_key_id_key | |
+ alteridx_orig | alteridx_orig_partition_key_id_key | |
+(16 rows)
+
+SELECT * FROM show_constraints_named_like('alteridx_%');
+ conname | contype | conkey | condeferrable
+-----------------------------------+---------+--------+---------------
+ alteridx_new_expr_excl | x | {0} | f
+ alteridx_new_expr_excl1 | x | {0} | f
+ alteridx_id_key | u | {1} | t
+ alteridx_new_pkey | p | {1} | f
+ alteridx_new_uniq_key_back | u | {2} | f
+ alteridx_orig_parity_check | c | {3} | f
+ alteridx_orig_msg_key | u | {4} | f
+ alteridx_new_ir_excl | x | {5} | f
+ alteridx_new_partition_key_key | u | {6} | f
+ alteridx_new_partition_key_id_key | u | {6,1} | f
+(10 rows)
+
+--
+--
+-- Checking that constraints still work
+--
+--
+INSERT INTO alteridx_orig VALUES(1, 0, 1, 'AA', int4range(102, 103), 17); -- failure here
+ERROR: duplicate key value violates unique constraint "alteridx_orig_msg_key"
+DETAIL: Key (msg)=(AA) already exists.
+INSERT INTO alteridx_orig VALUES(0, 1, 1, 'AA', int4range(104, 105), 17); -- failure here
+ERROR: duplicate key value violates unique constraint "alteridx_orig_msg_key"
+DETAIL: Key (msg)=(AA) already exists.
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(100, 107), 17); -- failure here
+ERROR: duplicate key value violates unique constraint "alteridx_orig_msg_key"
+DETAIL: Key (msg)=(AA) already exists.
+INSERT INTO alteridx_orig VALUES(0, 0, 1, 'AA', int4range(102, 107), 17); -- failure here
+ERROR: duplicate key value violates unique constraint "alteridx_orig_msg_key"
+DETAIL: Key (msg)=(AA) already exists.
+INSERT INTO alteridx_orig VALUES(NULL, 0, 1, 'AA', int4range(102, 107), 17); -- failure here
+ERROR: null value in column "id" of relation "alteridx_orig" violates not-null constraint
+DETAIL: Failing row contains (null, 0, 1, AA, [102,107), 17).
+INSERT INTO alteridx_orig VALUES(0, NULL, 1, 'AA', int4range(102, 107), 17); -- failure here
+ERROR: null value in column "uniq" of relation "alteridx_orig" violates not-null constraint
+DETAIL: Failing row contains (0, null, 1, AA, [102,107), 17).
+INSERT INTO alteridx_orig VALUES(-1, -1, 1, 'BB', int4range(108, 110), 17);
+--
+SELECT * FROM alteridx_orig;
+ id | uniq | parity | msg | ir | partition_key
+----+------+--------+-----+-----------+---------------
+ 1 | 1 | 1 | ?@ | [2,3) | 1
+ 2 | 2 | 0 | @A | [4,5) | 2
+ 3 | 3 | 1 | AB | [6,7) | 3
+ 4 | 4 | 0 | BC | [8,9) | 4
+ 5 | 5 | 1 | CD | [10,11) | 5
+ 6 | 6 | 0 | DE | [12,13) | 6
+ 7 | 7 | 1 | EF | [14,15) | 7
+ 8 | 8 | 0 | FG | [16,17) | 8
+ 9 | 9 | 1 | GH | [18,19) | 9
+ 10 | 10 | 0 | HI | [20,21) | 10
+ 0 | 0 | 1 | AA | [102,107) | 15
+ -1 | -1 | 1 | BB | [108,110) | 17
+(12 rows)
+
+--
+--
+DROP FUNCTION show_indexes_from_relation(searched_relname name);
+DROP FUNCTION show_constraints_named_like(searched_conname name);
+DROP FUNCTION show_index_exprs_pred(searched_relname name);
+DROP TABLE alteridx_orig;
+DROP TABLE partitioned_orig_alteridx;
+DROP TABLE another_alteridx;
+DROP TABLE third_alteridx;
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On 2020-Jul-05, Anna Akenteva wrote:
-- Swapping primary key's index for an equivalent index,
-- but with INCLUDE-d attributes.
CREATE UNIQUE INDEX new_idx ON target_tbl (id) INCLUDE (info);
ALTER TABLE target_tbl ALTER CONSTRAINT target_tbl_pkey USING INDEX
new_idx;
ALTER TABLE referencing_tbl ALTER CONSTRAINT referencing_tbl_id_ref_fkey
USING INDEX new_idx;
How is this state represented by pg_dump?
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2020-Jul-05, Anna Akenteva wrote:
-- Swapping primary key's index for an equivalent index,
-- but with INCLUDE-d attributes.
CREATE UNIQUE INDEX new_idx ON target_tbl (id) INCLUDE (info);
ALTER TABLE target_tbl ALTER CONSTRAINT target_tbl_pkey USING INDEX
new_idx;
ALTER TABLE referencing_tbl ALTER CONSTRAINT referencing_tbl_id_ref_fkey
USING INDEX new_idx;
How is this state represented by pg_dump?
Even if it's possible to represent, I think we should flat out reject
this "feature". Primary keys that aren't primary keys don't seem like
a good idea. For one thing, it won't be possible to describe the
constraint accurately in the information_schema.
regards, tom lane
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On 2020-07-07 01:08, Tom Lane wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2020-Jul-05, Anna Akenteva wrote:
-- Swapping primary key's index for an equivalent index,
-- but with INCLUDE-d attributes.
CREATE UNIQUE INDEX new_idx ON target_tbl (id) INCLUDE (info);
ALTER TABLE target_tbl ALTER CONSTRAINT target_tbl_pkey USING INDEX
new_idx;
ALTER TABLE referencing_tbl ALTER CONSTRAINT
referencing_tbl_id_ref_fkey
USING INDEX new_idx;How is this state represented by pg_dump?
Even if it's possible to represent, I think we should flat out reject
this "feature". Primary keys that aren't primary keys don't seem like
a good idea. For one thing, it won't be possible to describe the
constraint accurately in the information_schema.
Do you think it could still be a good idea if we only swap the
relfilenodes of indexes, as it was suggested in [1]/messages/by-id/CABwTF4UxTg+kERo1Nd4dt+H2miJoLPcASMFecS1-XHijABOpPg@mail.gmail.com? The original use
case was getting rid of index bloat, which is now solved by REINDEX
CONCURRENTLY, but this feature still has its own use case of adding
INCLUDE-d columns to constraint indexes.
[1]: /messages/by-id/CABwTF4UxTg+kERo1Nd4dt+H2miJoLPcASMFecS1-XHijABOpPg@mail.gmail.com
/messages/by-id/CABwTF4UxTg+kERo1Nd4dt+H2miJoLPcASMFecS1-XHijABOpPg@mail.gmail.com
--
Anna Akenteva
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On Mon, 2020-08-10 at 09:29 +0300, Anna Akenteva wrote:
On 2020-07-07 01:08, Tom Lane wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
On 2020-Jul-05, Anna Akenteva wrote:
-- Swapping primary key's index for an equivalent index,
-- but with INCLUDE-d attributes.
CREATE UNIQUE INDEX new_idx ON target_tbl (id) INCLUDE (info);
ALTER TABLE target_tbl ALTER CONSTRAINT target_tbl_pkey USING INDEX
new_idx;
ALTER TABLE referencing_tbl ALTER CONSTRAINT
referencing_tbl_id_ref_fkey
USING INDEX new_idx;How is this state represented by pg_dump?
Even if it's possible to represent, I think we should flat out reject
this "feature". Primary keys that aren't primary keys don't seem like
a good idea. For one thing, it won't be possible to describe the
constraint accurately in the information_schema.Do you think it could still be a good idea if we only swap the
relfilenodes of indexes, as it was suggested in [1]? The original use
case was getting rid of index bloat, which is now solved by REINDEX
CONCURRENTLY, but this feature still has its own use case of adding
INCLUDE-d columns to constraint indexes.
How can you just swap the filenodes if "indnatts" and "indkey" is
different, since one index has an INCLUDE clause?
I think that the original proposal is better, except that foreign key
dependencies should be changed along with the primary or unique index,
so that everything is consistent once the command is done.
Then the ALTER CONSTRAINT from that replaces the index referenced
by a foreign key becomes unnecessary and should be removed.
The value I see in this is:
- replacing a primary key index
- replacing the index behind a constraint targeted by a foreign key
Some code comments:
+ <varlistentry>
+ <term><literal>ALTER CONSTRAINT</literal> <replaceable class="parameter">constraint_name</replaceable> [USING INDEX <replaceable class="para>
+ <listitem>
+ <para>
+ For uniqueness, primary key, and exclusion constraints, this form
+ replaces the original index and renames the constraint accordingly.You forgot to mention foreign keys.
+ /* This function might need modificatoins if pg_index gets new fields */
+ Assert(Natts_pg_index == 20);Typo.
+ if (!equal(RelationGetIndexExpressions(oldIndex),
+ RelationGetIndexExpressions(newIndex)))
+ return "Indexes must have the same non-column attributes";Correct me if I am wrong, but constraint indexes can never use
expressions. So this should be covered by comparing the key
attributes above (they would be 0 for an expression).
+ if (!equal(oldPredicate, newPredicate))
+ {
+ if (oldPredicate && newPredicate)
+ return "Indexes must have the same partial index predicates";
+ else
+ return "Either none or both indexes must have partial index predicates";
+ }A constraint index can never have predicates. Only the new index would
have to be checked.
+/*
+ * ALTER TABLE ALTER CONSTRAINT USING INDEX
+ *
+ * Replace an index of a constraint.
+ *
+ * Currently only works for UNIQUE, EXCLUSION and PRIMARY constraints.You forgot foreign key constraints (although I think they should not be allowed).
I'll set the commitfest entry to "waiting for author".
Yours,
Laurenz Albe
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On 2020-Sep-04, Laurenz Albe wrote:
The value I see in this is:
- replacing a primary key index
- replacing the index behind a constraint targeted by a foreign key
But why is this better than using REINDEX CONCURRENTLY?
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On Fri, 2020-09-04 at 10:41 -0400, Alvaro Herrera wrote:
The value I see in this is:
- replacing a primary key index
- replacing the index behind a constraint targeted by a foreign keyBut why is this better than using REINDEX CONCURRENTLY?
It is not better, but it can be used to replace a constraint index
with an index with a different INCLUDE clause, which is something
that cannot easily be done otherwise.
For exclusion constraints it is pretty useless, and for unique
constraints it can be worked around with CREATE UNIQUE INDEX CONCURRENTLY.
Admitted, the use case is pretty narrow, and I am not sure if it is
worth adding code and SQL syntax for that.
Yours,
Laurenz Albe
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On 2020-Sep-04, Laurenz Albe wrote:
On Fri, 2020-09-04 at 10:41 -0400, Alvaro Herrera wrote:
The value I see in this is:
- replacing a primary key index
- replacing the index behind a constraint targeted by a foreign keyBut why is this better than using REINDEX CONCURRENTLY?
It is not better, but it can be used to replace a constraint index
with an index with a different INCLUDE clause, which is something
that cannot easily be done otherwise.
I can see that there is value in having an index that serves both a
uniqueness constraint and coverage purposes. But this seems a pretty
roundabout way to get that -- I think you should have to do "CREATE
UNIQUE INDEX ... INCLUDING ..." instead. That way, the fact that this
is a Postgres extension remains clear.
55432 14devel 24138=# create table foo (a int not null, b int not null, c int);
CREATE TABLE
Duraci�n: 1,775 ms
55432 14devel 24138=# create unique index on foo (a, b) include (c);
CREATE INDEX
Duraci�n: 1,481 ms
55432 14devel 24138=# create table bar (a int not null, b int not null, foreign key (a, b) references foo (a, b));
CREATE TABLE
Duraci�n: 2,559 ms
Now you have a normal index that you can reindex in the normal way, if you need
it.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On Fri, 2020-09-04 at 13:31 -0400, Alvaro Herrera wrote:
On 2020-Sep-04, Laurenz Albe wrote:
On Fri, 2020-09-04 at 10:41 -0400, Alvaro Herrera wrote:
The value I see in this is:
- replacing a primary key index
- replacing the index behind a constraint targeted by a foreign keyBut why is this better than using REINDEX CONCURRENTLY?
It is not better, but it can be used to replace a constraint index
with an index with a different INCLUDE clause, which is something
that cannot easily be done otherwise.I can see that there is value in having an index that serves both a
uniqueness constraint and coverage purposes. But this seems a pretty
roundabout way to get that -- I think you should have to do "CREATE
UNIQUE INDEX ... INCLUDING ..." instead. That way, the fact that this
is a Postgres extension remains clear.55432 14devel 24138=# create table foo (a int not null, b int not null, c int);
CREATE TABLE
Duración: 1,775 ms
55432 14devel 24138=# create unique index on foo (a, b) include (c);
CREATE INDEX
Duración: 1,481 ms
55432 14devel 24138=# create table bar (a int not null, b int not null, foreign key (a, b) references foo (a, b));
CREATE TABLE
Duración: 2,559 msNow you have a normal index that you can reindex in the normal way, if you need
it.
Yes, that is true.
But what if you have done
CREATE TABLE a (id bigint CONSTRAINT a_pkey PRIMARY KEY, val integer);
CREATE TABLE b (id bigint CONSTRAINT b_fkey REFERENCES a);
and later you figure out later that it would actually be better to have
an index ON mytab (id) INCLUDE (val), and you don't want to maintain
two indexes.
Yes, you could do
CREATE UNIQUE INDEX CONCURRENTLY ind ON a (id) INCLUDE (val);
ALTER TABLE a ADD UNIQUE USING INDEX ind;
ALTER TABLE a DROP CONSTRAINT a_pkey CASCADE;
ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a(id);
but then you don't have a primary key, and you have to live without
the foreign key for a while.
Adding a primary key to a large table is very painful, because it
locks the table exclusively for a long time.
This patch would provide a more convenient way to do that.
Again, I am not sure if that justifies the effort.
Yours,
Laurenz Albe
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On 2020-Sep-07, Laurenz Albe wrote:
This patch would provide a more convenient way to do that.
Again, I am not sure if that justifies the effort.
I have to admit I've seen cases where it'd be useful to have included
columns in primary keys.
TBH I think if we really wanted the feature of primary keys with
included columns, we'd have to add it to the PRIMARY KEY syntax rather
than having an ad-hoc ALTER TABLE ALTER CONSTRAINT USING INDEX command
to replace the index underneath. Then things like pg_dump would work
normally.
(I have an answer for the information_schema question Tom posed; I'd
like to know what's yours.)
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On Mon, 2020-09-07 at 11:42 -0300, Alvaro Herrera wrote:
This patch would provide a more convenient way to do that.
Again, I am not sure if that justifies the effort.I have to admit I've seen cases where it'd be useful to have included
columns in primary keys.TBH I think if we really wanted the feature of primary keys with
included columns, we'd have to add it to the PRIMARY KEY syntax rather
than having an ad-hoc ALTER TABLE ALTER CONSTRAINT USING INDEX command
to replace the index underneath. Then things like pg_dump would work
normally.(I have an answer for the information_schema question Tom posed; I'd
like to know what's yours.)
Gah, now I see my mistake. I was under the impression that a
primary key can have an INCLUDE clause today, which is not true.
So this would introduce that feature in a weird way.
I agree that that is undesirable.
We should at least have
ALTER TABLE ... ADD PRIMARY KEY (id) INCLUDE (val);
or something before we consider this patch.
As to the information_schema, that could pretend that the INCLUDE
columns just don't exist.
Yours,
Laurenz Albe
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On 2020-Sep-08, Laurenz Albe wrote:
We should at least have
ALTER TABLE ... ADD PRIMARY KEY (id) INCLUDE (val);
or something before we consider this patch.
Agreed.
Now the trick in this new command is to let the user change the included
columns afterwards, which remains useful (since it's clearly reasonable
to change minds after applications using the constraint start to take
shape).
As to the information_schema, that could pretend that the INCLUDE
columns just don't exist.
Yeah, that's what I was thinking too, since for all intents and
purposes, from the standard's POV the constraint works the same
regardless of included columns.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On Fri, Sep 04, 2020 at 01:59:47PM +0200, Laurenz Albe wrote:
I'll set the commitfest entry to "waiting for author".
This review, as well as any of the follow-up emails, have not been
answered by the author, so I have marked the patch as returned with
feedback.
--
Michael
Re: Change a constraint's index - ALTER TABLE ... ALTER CONSTRAINT ... USING INDEX ...
On Wed, 2020-09-30 at 16:27 +0900, Michael Paquier wrote:
On Fri, Sep 04, 2020 at 01:59:47PM +0200, Laurenz Albe wrote:
I'll set the commitfest entry to "waiting for author".
This review, as well as any of the follow-up emails, have not been
answered by the author, so I have marked the patch as returned with
feedback.
I had the impression that "rejected" would be more appropriate.
It doesn't make sense to introduce a featue whose only remaining
use case is modifying a constraint index in a way that is not
supported currently.
Yours,
Laurenz Albe