Detach constraints when partitions are detached

I (Álvaro) forgot to do this in eb7ed3f30634, leading to undroppable
constraints after partitions are detached.  Repair.

Reported-by: Amit Langote
Author: Amit Langote
Discussion: https://postgr.es/m/c1c9b688-b886-84f7-4048-1e4ebe9b1d06@lab.ntt.co.jp

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 5a62adbafcd885913e7411f3f9e9310d4adae07a..32eb5f88bf70717deeb8ca768ead4b99a1ddeece 100644 (file)
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15233,6 +15233,7 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
    {
        Oid         idxid = lfirst_oid(cell);
        Relation    idx;
+       Oid         constrOid;
 
        if (!has_superclass(idxid))
            continue;
@@ -15244,6 +15245,23 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
        IndexSetParentIndex(idx, InvalidOid);
        update_relispartition(classRel, idxid, false);
        index_close(idx, NoLock);
+
+       /*
+        * Detach any constraints associated with the index too.  Only UNIQUE
+        * and PRIMARY KEY index constraints can be inherited, so no need
+        * to check for others.
+        */
+       if (!idx->rd_index->indisprimary && !idx->rd_index->indisunique)
+           continue;
+
+       constrOid = get_relation_idx_constraint_oid(RelationGetRelid(partRel),
+                                                   idxid);
+       if (!OidIsValid(constrOid))
+           elog(ERROR, "missing pg_constraint entry of index \"%s\" of partition \"%s\"",
+                RelationGetRelationName(idx),
+                RelationGetRelationName(partRel));
+
+       ConstraintSetParentConstraint(constrOid, InvalidOid);
    }
    heap_close(classRel, RowExclusiveLock);
 

diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 11fdcdc3b21d975fbf199e60b1693e7f7c355410..769b7b83d434131a5bd3d90e3509e7d86be72baa 100644 (file)
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1387,3 +1387,18 @@ DETAIL:  Key (a)=(4) already exists.
 create unique index on covidxpart (b) include (a); -- should fail
 ERROR:  insufficient columns in UNIQUE constraint definition
 DETAIL:  UNIQUE constraint on table "covidxpart" lacks column "a" which is part of the partition key.
+-- check that detaching a partition also detaches the primary key constraint
+create table parted_pk_detach_test (a int primary key) partition by list (a);
+create table parted_pk_detach_test1 partition of parted_pk_detach_test for values in (1);
+alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;    -- should fail
+ERROR:  cannot drop inherited constraint "parted_pk_detach_test1_pkey" of relation "parted_pk_detach_test1"
+alter table parted_pk_detach_test detach partition parted_pk_detach_test1;
+alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;
+drop table parted_pk_detach_test, parted_pk_detach_test1;
+create table parted_uniq_detach_test (a int unique) partition by list (a);
+create table parted_uniq_detach_test1 partition of parted_uniq_detach_test for values in (1);
+alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;   -- should fail
+ERROR:  cannot drop inherited constraint "parted_uniq_detach_test1_a_key" of relation "parted_uniq_detach_test1"
+alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
+alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
+drop table parted_uniq_detach_test, parted_uniq_detach_test1;

diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 8c5a0024eef47e63058dbcffc600d9f0bd052ec4..9adc5be10c27ccb57f6eaa6305f21827945aae0d 100644 (file)
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -740,3 +740,17 @@ alter table covidxpart attach partition covidxpart4 for values in (4);
 insert into covidxpart values (4, 1);
 insert into covidxpart values (4, 1);
 create unique index on covidxpart (b) include (a); -- should fail
+
+-- check that detaching a partition also detaches the primary key constraint
+create table parted_pk_detach_test (a int primary key) partition by list (a);
+create table parted_pk_detach_test1 partition of parted_pk_detach_test for values in (1);
+alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;    -- should fail
+alter table parted_pk_detach_test detach partition parted_pk_detach_test1;
+alter table parted_pk_detach_test1 drop constraint parted_pk_detach_test1_pkey;
+drop table parted_pk_detach_test, parted_pk_detach_test1;
+create table parted_uniq_detach_test (a int unique) partition by list (a);
+create table parted_uniq_detach_test1 partition of parted_uniq_detach_test for values in (1);
+alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;   -- should fail
+alter table parted_uniq_detach_test detach partition parted_uniq_detach_test1;
+alter table parted_uniq_detach_test1 drop constraint parted_uniq_detach_test1_a_key;
+drop table parted_uniq_detach_test, parted_uniq_detach_test1;