Another version. I realized that attaching a partitioned partition had
further trouble, because the recursion at each step would consider all
FKs instead of only the FKs that had been cloned. So I had to split out
the recursive step of the cloning. Now that works fine.
In order to make this work, I made two little change to struct
ForeignKeyCacheInfo: first, the constraint OID was added. Second, I
made RelationGetFKeyList() return a nonempty list for partitioned
tables, which it didn't before (because of the optimization that
presupposes no triggers means no FKs, which is not true for partitioned
tables). As far as I can see, this has no effect on how the planner
uses this function. (This stuff could be done with repeated scans of
pg_constraint, but it seems much simpler this way.)
Michael sent me his test case offlist, and I verified that it works
correctly with this patch.
Unless there are objections, I intend to get this pushed tomorrow.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 2063abb8ae..40deb29e29 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -19,6 +19,7 @@
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "access/tupconvert.h"
+#include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
@@ -37,6 +38,10 @@
#include "utils/tqual.h"
+static void clone_fk_constraints(Relation pg_constraint, Relation parentRel,
+ Relation partRel, List *clone, List **cloned);
+
+
/*
* CreateConstraintEntry
* Create a constraint table entry.
@@ -400,34 +405,72 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
Relation rel;
ScanKeyData key;
SysScanDesc scan;
- TupleDesc tupdesc;
HeapTuple tuple;
- AttrNumber *attmap;
+ List *clone = NIL;
parentRel = heap_open(parentId, NoLock); /* already got lock */
/* see ATAddForeignKeyConstraint about lock level */
rel = heap_open(relationId, AccessExclusiveLock);
-
pg_constraint = heap_open(ConstraintRelationId, RowShareLock);
+
+ /* Obtain the list of constraints to clone or attach */
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid, BTEqualStrategyNumber,
+ F_OIDEQ, ObjectIdGetDatum(parentId));
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+ NULL, 1, &key);
+ while ((tuple = systable_getnext(scan)) != NULL)
+ clone = lappend_oid(clone, HeapTupleGetOid(tuple));
+ systable_endscan(scan);
+
+ /* Do the actual work, recursing to partitions as needed */
+ clone_fk_constraints(pg_constraint, parentRel, rel, clone, cloned);
+
+ /* We're done. Clean up */
+ heap_close(parentRel, NoLock);
+ heap_close(rel, NoLock); /* keep lock till commit */
+ heap_close(pg_constraint, RowShareLock);
+}
+
+/*
+ * clone_fk_constraints
+ * Recursive subroutine for CloneForeignKeyConstraints
+ *
+ * Clone the given list of FK constraints when a partition is attached.
+ *
+ * When cloning foreign key to a partition, it may happen that some FKs
+ * already exist. In that case we can skip cloning it.
+ *
+ * This function also recurses to partitions, if the new partition is
+ * partitioned; of course, only do this for FKs that were actually cloned.
+ */
+static void
+clone_fk_constraints(Relation pg_constraint, Relation parentRel,
+ Relation partRel, List *clone, List **cloned)
+{
+ TupleDesc tupdesc;
+ AttrNumber *attmap;
+ List *partFKs;
+ List *subclone = NIL;
+ ListCell *cell;
+
tupdesc = RelationGetDescr(pg_constraint);
/*
* The constraint key may differ, if the columns in the partition are
* different. This map is used to convert them.
*/
- attmap = convert_tuples_by_name_map(RelationGetDescr(rel),
+ attmap = convert_tuples_by_name_map(RelationGetDescr(partRel),
RelationGetDescr(parentRel),
gettext_noop("could not convert row type"));
- ScanKeyInit(&key,
- Anum_pg_constraint_conrelid, BTEqualStrategyNumber,
- F_OIDEQ, ObjectIdGetDatum(parentId));
- scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
- NULL, 1, &key);
+ partFKs = copyObject(RelationGetFKeyList(partRel));
- while ((tuple = systable_getnext(scan)) != NULL)
+ foreach(cell, clone)
{
- Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ Oid parentConstrOid = lfirst_oid(cell);
+ Form_pg_constraint constrForm;
+ HeapTuple tuple;
AttrNumber conkey[INDEX_MAX_KEYS];
AttrNumber mapped_conkey[INDEX_MAX_KEYS];
AttrNumber confkey[INDEX_MAX_KEYS];
@@ -435,22 +478,31 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
Oid conppeqop[INDEX_MAX_KEYS];
Oid conffeqop[INDEX_MAX_KEYS];
Constraint *fkconstraint;
- ClonedConstraint *newc;
+ bool attach_it;
Oid constrOid;
ObjectAddress parentAddr,
childAddr;
int nelem;
+ ListCell *cell;
int i;
ArrayType *arr;
Datum datum;
bool isnull;
+ tuple = SearchSysCache1(CONSTROID, parentConstrOid);
+ if (!tuple)
+ elog(ERROR, "cache lookup failed for constraint %u",
+ parentConstrOid);
+ constrForm = (Form_pg_constraint) GETSTRUCT(tuple);
+
/* only foreign keys */
if (constrForm->contype != CONSTRAINT_FOREIGN)
+ {
+ ReleaseSysCache(tuple);
continue;
+ }
- ObjectAddressSet(parentAddr, ConstraintRelationId,
- HeapTupleGetOid(tuple));
+ ObjectAddressSet(parentAddr, ConstraintRelationId, parentConstrOid);
datum = fastgetattr(tuple, Anum_pg_constraint_conkey,
tupdesc, &isnull);
@@ -539,6 +591,84 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
elog(ERROR, "conffeqop is not a 1-D OID array");
memcpy(conffeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+ /*
+ * Before creating a new constraint, see whether any existing FKs are
+ * fit for the purpose. If it is, attach the parent constraint to it,
+ * and don't clone anything. This way we avoid the expensive
+ * verification step and don't end up with a duplicate FK. This also
+ * means we don't consider this constraint when recursing to
+ * partitions.
+ */
+ attach_it = false;
+ foreach (cell, partFKs)
+ {
+ ForeignKeyCacheInfo *fk = lfirst_node(ForeignKeyCacheInfo, cell);
+ Form_pg_constraint partConstr;
+ HeapTuple partcontup;
+
+ attach_it = true;
+
+ if (fk->confrelid != constrForm->confrelid || fk->nkeys != nelem)
+ {
+ attach_it = false;
+ continue;
+ }
+
+ /* Do some quick & easy initial checks */
+ for (i = 0; i < nelem; i++)
+ {
+ if (fk->conkey[i] != mapped_conkey[i] ||
+ fk->confkey[i] != confkey[i] ||
+ fk->conpfeqop[i] != conpfeqop[i])
+ {
+ attach_it = false;
+ break;
+ }
+ }
+ if (!attach_it)
+ continue;
+
+ /* looks good ... do some more extensive checks */
+ partcontup = SearchSysCache1(CONSTROID,
+ ObjectIdGetDatum(fk->conoid));
+ if (!partcontup)
+ elog(ERROR, "cache lookup failed for constraint %u",
+ fk->conoid);
+ partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);
+ if (OidIsValid(partConstr->conparentid) ||
+ !partConstr->convalidated ||
+ partConstr->condeferrable != constrForm->condeferrable ||
+ partConstr->condeferred != constrForm->condeferred ||
+ partConstr->confupdtype != constrForm->confupdtype ||
+ partConstr->confdeltype != constrForm->confdeltype ||
+ partConstr->confmatchtype != constrForm->confmatchtype)
+ {
+ ReleaseSysCache(partcontup);
+ attach_it = false;
+ continue;
+ }
+
+ ReleaseSysCache(partcontup);
+
+ /* looks good! Attach this constraint */
+ ConstraintSetParentConstraint(fk->conoid,
+ HeapTupleGetOid(tuple));
+ CommandCounterIncrement();
+ attach_it = true;
+ break;
+ }
+
+ /*
+ * If we attached to an existing constraint, there is no need to
+ * create a new one. In fact, there's no need to recurse for this
+ * constraint to partitions, either.
+ */
+ if (attach_it)
+ {
+ ReleaseSysCache(tuple);
+ continue;
+ }
+
constrOid =
CreateConstraintEntry(NameStr(constrForm->conname),
constrForm->connamespace,
@@ -547,7 +677,7 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
constrForm->condeferred,
constrForm->convalidated,
HeapTupleGetOid(tuple),
- relationId,
+ RelationGetRelid(partRel),
mapped_conkey,
nelem,
nelem,
@@ -568,6 +698,7 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
NULL,
false,
1, false, true);
+ subclone = lappend_oid(subclone, constrOid);
ObjectAddressSet(childAddr, ConstraintRelationId, constrOid);
recordDependencyOn(&childAddr, &parentAddr, DEPENDENCY_INTERNAL_AUTO);
@@ -580,17 +711,19 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
fkconstraint->deferrable = constrForm->condeferrable;
fkconstraint->initdeferred = constrForm->condeferred;
- createForeignKeyTriggers(rel, constrForm->confrelid, fkconstraint,
+ createForeignKeyTriggers(partRel, constrForm->confrelid, fkconstraint,
constrOid, constrForm->conindid, false);
if (cloned)
{
+ ClonedConstraint *newc;
+
/*
* Feed back caller about the constraints we created, so that they
* can set up constraint verification.
*/
newc = palloc(sizeof(ClonedConstraint));
- newc->relid = relationId;
+ newc->relid = RelationGetRelid(partRel);
newc->refrelid = constrForm->confrelid;
newc->conindid = constrForm->conindid;
newc->conid = constrOid;
@@ -598,25 +731,36 @@ CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
*cloned = lappend(*cloned, newc);
}
+
+ ReleaseSysCache(tuple);
}
- systable_endscan(scan);
pfree(attmap);
+ list_free_deep(partFKs);
- if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ /*
+ * If the partition is partitioned, recurse to handle any constraints
+ * that were cloned.
+ */
+ if (partRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
+ subclone != NIL)
{
- PartitionDesc partdesc = RelationGetPartitionDesc(rel);
+ PartitionDesc partdesc = RelationGetPartitionDesc(partRel);
int i;
for (i = 0; i < partdesc->nparts; i++)
- CloneForeignKeyConstraints(RelationGetRelid(rel),
- partdesc->oids[i],
- cloned);
- }
+ {
+ Relation childRel;
- heap_close(rel, NoLock); /* keep lock till commit */
- heap_close(parentRel, NoLock);
- heap_close(pg_constraint, RowShareLock);
+ childRel = heap_open(partdesc->oids[i], AccessExclusiveLock);
+ clone_fk_constraints(pg_constraint,
+ partRel,
+ childRel,
+ subclone,
+ cloned);
+ heap_close(childRel, NoLock); /* keep lock till commit */
+ }
+ }
}
/*
@@ -1028,17 +1172,33 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
elog(ERROR, "cache lookup failed for constraint %u", childConstrId);
newtup = heap_copytuple(tuple);
constrForm = (Form_pg_constraint) GETSTRUCT(newtup);
- constrForm->conislocal = false;
- constrForm->coninhcount++;
- constrForm->conparentid = parentConstrId;
- CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+ if (OidIsValid(parentConstrId))
+ {
+ constrForm->conislocal = false;
+ constrForm->coninhcount++;
+ constrForm->conparentid = parentConstrId;
+
+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+
+ ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
+ ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
+
+ recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
+ }
+ else
+ {
+ constrForm->coninhcount--;
+ if (constrForm->coninhcount <= 0)
+ constrForm->conislocal = true;
+ constrForm->conparentid = InvalidOid;
+
+ deleteDependencyRecordsForClass(ConstraintRelationId, childConstrId,
+ ConstraintRelationId,
+ DEPENDENCY_INTERNAL_AUTO);
+ CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
+ }
+
ReleaseSysCache(tuple);
-
- ObjectAddressSet(referenced, ConstraintRelationId, parentConstrId);
- ObjectAddressSet(depender, ConstraintRelationId, childConstrId);
-
- recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
-
heap_close(constrRel, RowExclusiveLock);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f988c16659..84136fc37d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14036,6 +14036,11 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
attachrel = heap_openrv(cmd->name, AccessExclusiveLock);
/*
+ * XXX I think it'd be a good idea to grab locks on all tables referenced
+ * by FKs at this point also.
+ */
+
+ /*
* Must be owner of both parent and source table -- parent was checked by
* ATSimplePermissions call in ATPrepCmd
*/
@@ -14607,6 +14612,7 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
ObjectAddress address;
Oid defaultPartOid;
List *indexes;
+ List *fks;
ListCell *cell;
/*
@@ -14682,6 +14688,23 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
}
heap_close(classRel, RowExclusiveLock);
+ /* Detach foreign keys */
+ fks = copyObject(RelationGetFKeyList(partRel));
+ foreach (cell, fks)
+ {
+ ForeignKeyCacheInfo *fk = lfirst(cell);
+ HeapTuple contup;
+
+ contup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
+ if (!contup)
+ elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
+
+ ConstraintSetParentConstraint(fk->conoid, InvalidOid);
+
+ ReleaseSysCache(contup);
+ }
+ list_free_deep(fks);
+
/*
* Invalidate the parent's relcache so that the partition is no longer
* included in its partition descriptor.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 39618323fc..648758de4a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4746,6 +4746,7 @@ _copyForeignKeyCacheInfo(const ForeignKeyCacheInfo *from)
{
ForeignKeyCacheInfo *newnode = makeNode(ForeignKeyCacheInfo);
+ COPY_SCALAR_FIELD(conoid);
COPY_SCALAR_FIELD(conrelid);
COPY_SCALAR_FIELD(confrelid);
COPY_SCALAR_FIELD(nkeys);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9a169f7c6c..6c3dad9ab3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3634,6 +3634,7 @@ _outForeignKeyCacheInfo(StringInfo str, const ForeignKeyCacheInfo *node)
WRITE_NODE_TYPE("FOREIGNKEYCACHEINFO");
+ WRITE_OID_FIELD(conoid);
WRITE_OID_FIELD(conrelid);
WRITE_OID_FIELD(confrelid);
WRITE_INT_FIELD(nkeys);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index a4fc001103..fd3d010b77 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4108,8 +4108,9 @@ RelationGetFKeyList(Relation relation)
if (relation->rd_fkeyvalid)
return relation->rd_fkeylist;
- /* Fast path: if it doesn't have any triggers, it can't have FKs */
- if (!relation->rd_rel->relhastriggers)
+ /* Fast path: non-partitioned tables without triggers can't have FKs */
+ if (!relation->rd_rel->relhastriggers &&
+ relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
return NIL;
/*
@@ -4144,6 +4145,7 @@ RelationGetFKeyList(Relation relation)
continue;
info = makeNode(ForeignKeyCacheInfo);
+ info->conoid = HeapTupleGetOid(htup);
info->conrelid = constraint->conrelid;
info->confrelid = constraint->confrelid;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 6ecbdb6294..84469f5715 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -202,12 +202,13 @@ typedef struct RelationData
* The per-FK-column arrays can be fixed-size because we allow at most
* INDEX_MAX_KEYS columns in a foreign key constraint.
*
- * Currently, we only cache fields of interest to the planner, but the
- * set of fields could be expanded in future.
+ * Currently, we mostly cache fields of interest to the planner, but the set
+ * of fields has already grown the constraint OID for other uses.
*/
typedef struct ForeignKeyCacheInfo
{
NodeTag type;
+ Oid conoid; /* oid of the constraint itself */
Oid conrelid; /* relation constrained by the foreign key */
Oid confrelid; /* relation referenced by the foreign key */
int nkeys; /* number of columns in the foreign key */
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 4e5cb8901e..4d86a8e1a4 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1648,6 +1648,124 @@ SELECT * FROM fk_partitioned_fk WHERE a = 142857;
-- verify that DROP works
DROP TABLE fk_partitioned_fk_2;
+-- Test behavior of the constraint together with attaching and detaching
+-- partitions.
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
+BEGIN;
+DROP TABLE fk_partitioned_fk;
+-- constraint should still be there
+\d fk_partitioned_fk_2;
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | | 2501
+ b | integer | | | 142857
+Foreign-key constraints:
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+ROLLBACK;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+ Table "public.fk_partitioned_fk_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ b | integer | | |
+ a | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (1500, 1502)
+Foreign-key constraints:
+ "fk_partitioned_fk_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
+CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
+CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
+ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2 FOR VALUES FROM (100,100) TO (1000,1000);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_4;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+-- should only have one constraint
+\d fk_partitioned_fk_4
+ Table "public.fk_partitioned_fk_4"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (3500, 3502)
+Partition key: RANGE (b, a)
+Foreign-key constraints:
+ "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d fk_partitioned_fk_4_1
+ Table "public.fk_partitioned_fk_4_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk_4 FOR VALUES FROM (1, 1) TO (100, 100)
+Foreign-key constraints:
+ "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+-- this one has an FK with mismatched properties
+\d fk_partitioned_fk_4_2
+ Table "public.fk_partitioned_fk_4_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk_4 FOR VALUES FROM (100, 100) TO (1000, 1000)
+Foreign-key constraints:
+ "fk_partitioned_fk_4_2_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL
+ "fk_partitioned_fk_4_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
+CREATE TABLE fk_partitioned_fk_5 (a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b),
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b))
+ PARTITION BY RANGE (a);
+CREATE TABLE fk_partitioned_fk_5_1 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_5;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+-- this one has two constraints, similar but not quite the one in the parent,
+-- so it gets a new one
+\d fk_partitioned_fk_5
+ Table "public.fk_partitioned_fk_5"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk FOR VALUES IN (4500)
+Partition key: RANGE (a)
+Foreign-key constraints:
+ "fk_partitioned_fk_5_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+Number of partitions: 1 (Use \d+ to list them.)
+
+-- verify that it works to reattaching a child with multiple candidate
+-- constraints
+ALTER TABLE fk_partitioned_fk_5 DETACH PARTITION fk_partitioned_fk_5_1;
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+\d fk_partitioned_fk_5_1
+ Table "public.fk_partitioned_fk_5_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+Partition of: fk_partitioned_fk_5 FOR VALUES FROM (0) TO (10)
+Foreign-key constraints:
+ "fk_partitioned_fk_5_1_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_5_a_fkey1" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b)
+ "fk_partitioned_fk_a_fkey" FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE
+
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 6fcb5dfb4e..5f931abd7a 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1226,6 +1226,56 @@ SELECT * FROM fk_partitioned_fk WHERE a = 142857;
-- verify that DROP works
DROP TABLE fk_partitioned_fk_2;
+-- Test behavior of the constraint together with attaching and detaching
+-- partitions.
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES IN (1500,1502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_2;
+BEGIN;
+DROP TABLE fk_partitioned_fk;
+-- constraint should still be there
+\d fk_partitioned_fk_2;
+ROLLBACK;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+DROP TABLE fk_partitioned_fk_2;
+CREATE TABLE fk_partitioned_fk_2 (b int, c text, a int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk ON UPDATE CASCADE ON DELETE CASCADE);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN c;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1500,1502);
+-- should have only one constraint
+\d fk_partitioned_fk_2
+DROP TABLE fk_partitioned_fk_2;
+
+CREATE TABLE fk_partitioned_fk_4 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE CASCADE ON DELETE CASCADE) PARTITION BY RANGE (b, a);
+CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4 FOR VALUES FROM (1,1) TO (100,100);
+CREATE TABLE fk_partitioned_fk_4_2 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b) ON UPDATE SET NULL);
+ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2 FOR VALUES FROM (100,100) TO (1000,1000);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_4;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_4 FOR VALUES IN (3500,3502);
+-- should only have one constraint
+\d fk_partitioned_fk_4
+\d fk_partitioned_fk_4_1
+-- this one has an FK with mismatched properties
+\d fk_partitioned_fk_4_2
+
+CREATE TABLE fk_partitioned_fk_5 (a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b),
+ FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk(a, b))
+ PARTITION BY RANGE (a);
+CREATE TABLE fk_partitioned_fk_5_1 (a int, b int, FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+ALTER TABLE fk_partitioned_fk DETACH PARTITION fk_partitioned_fk_5;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_5 FOR VALUES IN (4500);
+-- this one has two constraints, similar but not quite the one in the parent,
+-- so it gets a new one
+\d fk_partitioned_fk_5
+-- verify that it works to reattaching a child with multiple candidate
+-- constraints
+ALTER TABLE fk_partitioned_fk_5 DETACH PARTITION fk_partitioned_fk_5_1;
+ALTER TABLE fk_partitioned_fk_5 ATTACH PARTITION fk_partitioned_fk_5_1 FOR VALUES FROM (0) TO (10);
+\d fk_partitioned_fk_5_1
+
-- verify that attaching a table checks that the existing data satisfies the
-- constraint
CREATE TABLE fk_partitioned_fk_2 (a int, b int) PARTITION BY RANGE (b);
