Fix ALTER COLUMN TYPE failure with a partial exclusion constraint.

ATExecAlterColumnType failed to consider the possibility that an index
that needs to be rebuilt might be a child of a constraint that needs to be
rebuilt.  We missed this so far because usually a constraint index doesn't
have a direct dependency on its table, just on the constraint object.
But if there's a WHERE clause, then dependency analysis of the WHERE
clause results in direct dependencies on the column(s) mentioned in WHERE.
This led to trying to drop and rebuild both the constraint and its
underlying index.

In v11/HEAD, we successfully drop both the index and the constraint,
and then try to rebuild both, and of course the second rebuild hits a
duplicate-index-name problem.  Before v11, it fails with obscure messages
about a missing relation OID, due to trying to drop the index twice.

This is essentially the same kind of problem noted in commit
20bef2c31: the possible dependency linkages are broader than what
ATExecAlterColumnType was designed for.  It was probably OK when
written, but it's certainly been broken since the introduction of
partial exclusion constraints.  Fix by adding an explicit check
for whether any of the indexes-to-be-rebuilt belong to any of the
constraints-to-be-rebuilt, and ignoring any that do.

In passing, fix a latent bug introduced by commit 8b08f7d48: in
get_constraint_index() we must "continue" not "break" when rejecting
a relation of a wrong relkind.  This is harmless today because we don't
expect that code path to be taken anyway; but if there ever were any
relations to be ignored, the existing coding would have an extremely
undesirable dependency on the order of pg_depend entries.

Also adjust a couple of obsolete comments.

Per bug #15835 from Yaroslav Schekin.  Back-patch to all supported
branches.

Discussion: https://postgr.es/m/15835-32d9b7a76c06a7a9@postgresql.org

diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index 7b2d0a7649f78c4ac6b3c5bc6a4b052117be650b..ba977c687c102d8e1d527cf0e2d2bdb40de0e1a2 100644 (file)
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -611,8 +611,8 @@ getOwnedSequences(Oid relid)
 
 /*
  * get_constraint_index
- *     Given the OID of a unique or primary-key constraint, return the
- *     OID of the underlying unique index.
+ *     Given the OID of a unique, primary-key, or exclusion constraint,
+ *     return the OID of the underlying index.
  *
  * Return InvalidOid if the index couldn't be found; this suggests the
  * given OID is bogus, but we leave it to caller to decide what to do.
@@ -672,8 +672,9 @@ get_constraint_index(Oid constraintId)
 
 /*
  * get_index_constraint
- *     Given the OID of an index, return the OID of the owning unique or
- *     primary-key constraint, or InvalidOid if no such constraint.
+ *     Given the OID of an index, return the OID of the owning unique,
+ *     primary-key, or exclusion constraint, or InvalidOid if there
+ *     is no owning constraint.
  */
 Oid
 get_index_constraint(Oid indexId)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cb8cebd4a7350aaffed11d67451f33459468b9fa..17085545100c6076be9d4aff7e49f38c408ad494 100644 (file)
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7787,6 +7787,9 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
    ScanKeyData key[3];
    SysScanDesc scan;
    HeapTuple   depTup;
+   ListCell   *lc;
+   ListCell   *prev;
+   ListCell   *next;
 
    attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
 
@@ -7900,14 +7903,20 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 
                    if (relKind == RELKIND_INDEX)
                    {
+                       /*
+                        * Indexes that are directly dependent on the table
+                        * might be regular indexes or constraint indexes.
+                        * Constraint indexes typically have only indirect
+                        * dependencies; but there are exceptions, notably
+                        * partial exclusion constraints.  Hence we must check
+                        * whether the index depends on any constraint that's
+                        * due to be rebuilt, which we'll do below after we've
+                        * found all such constraints.
+                        */
                        Assert(foundObject.objectSubId == 0);
-                       if (!list_member_oid(tab->changedIndexOids, foundObject.objectId))
-                       {
-                           tab->changedIndexOids = lappend_oid(tab->changedIndexOids,
-                                                      foundObject.objectId);
-                           tab->changedIndexDefs = lappend(tab->changedIndexDefs,
-                              pg_get_indexdef_string(foundObject.objectId));
-                       }
+                       tab->changedIndexOids =
+                           list_append_unique_oid(tab->changedIndexOids,
+                                                  foundObject.objectId);
                    }
                    else if (relKind == RELKIND_SEQUENCE)
                    {
@@ -8043,6 +8052,41 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 
    systable_endscan(scan);
 
+   /*
+    * Check the collected index OIDs to see which ones belong to the
+    * constraint(s) of the table, and drop those from the list of indexes
+    * that we need to process; rebuilding the constraints will handle them.
+    */
+   prev = NULL;
+   for (lc = list_head(tab->changedIndexOids); lc; lc = next)
+   {
+       Oid         indexoid = lfirst_oid(lc);
+       Oid         conoid;
+
+       next = lnext(lc);
+
+       conoid = get_index_constraint(indexoid);
+       if (OidIsValid(conoid) &&
+           list_member_oid(tab->changedConstraintOids, conoid))
+           tab->changedIndexOids = list_delete_cell(tab->changedIndexOids,
+                                                    lc, prev);
+       else
+           prev = lc;
+   }
+
+   /*
+    * Now collect the definitions of the indexes that must be rebuilt.  (We
+    * could merge this into the previous loop, but it'd be more complicated
+    * for little gain.)
+    */
+   foreach(lc, tab->changedIndexOids)
+   {
+       Oid         indexoid = lfirst_oid(lc);
+
+       tab->changedIndexDefs = lappend(tab->changedIndexDefs,
+                                       pg_get_indexdef_string(indexoid));
+   }
+
    /*
     * Now scan for dependencies of this column on other things.  The only
     * thing we should find is the dependency on the column datatype, which we

diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 1a55bf71847391c2be3e0faf8d9a5a2a99cf2544..6c4bd067ce5733eaff800340a53ce557f62d6bcc 100644 (file)
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1898,6 +1898,46 @@ select * from anothertab;
  f      | IT WAS NULL!
 (3 rows)
 
+drop table anothertab;
+-- Test alter table column type with constraint indexes (cf. bug #15835)
+create table anothertab(f1 int primary key, f2 int unique, f3 int, f4 int);
+alter table anothertab
+  add exclude using btree (f3 with =);
+alter table anothertab
+  add exclude using btree (f4 with =) where (f4 is not null);
+\d anothertab
+  Table "public.anothertab"
+ Column |  Type   | Modifiers 
+--------+---------+-----------
+ f1     | integer | not null
+ f2     | integer | 
+ f3     | integer | 
+ f4     | integer | 
+Indexes:
+    "anothertab_pkey" PRIMARY KEY, btree (f1)
+    "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
+    "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
+    "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
+
+alter table anothertab alter column f1 type bigint;
+alter table anothertab
+  alter column f2 type bigint,
+  alter column f3 type bigint,
+  alter column f4 type bigint;
+\d anothertab
+  Table "public.anothertab"
+ Column |  Type  | Modifiers 
+--------+--------+-----------
+ f1     | bigint | not null
+ f2     | bigint | 
+ f3     | bigint | 
+ f4     | bigint | 
+Indexes:
+    "anothertab_pkey" PRIMARY KEY, btree (f1)
+    "anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
+    "anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
+    "anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
+
 drop table anothertab;
 create table another (f1 int, f2 text);
 insert into another values(1, 'one');

diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 03a3789f3ff08d18eaf981bfb75673a01b7e71d7..67d7a95e25f4ebd4e4072a5c81a09bcd332e0714 100644 (file)
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1292,6 +1292,23 @@ select * from anothertab;
 
 drop table anothertab;
 
+-- Test alter table column type with constraint indexes (cf. bug #15835)
+create table anothertab(f1 int primary key, f2 int unique, f3 int, f4 int);
+alter table anothertab
+  add exclude using btree (f3 with =);
+alter table anothertab
+  add exclude using btree (f4 with =) where (f4 is not null);
+
+\d anothertab
+alter table anothertab alter column f1 type bigint;
+alter table anothertab
+  alter column f2 type bigint,
+  alter column f3 type bigint,
+  alter column f4 type bigint;
+\d anothertab
+
+drop table anothertab;
+
 create table another (f1 int, f2 text);
 
 insert into another values(1, 'one');