Hi,
While working on NOT ENFORCED constraints[1], which are by default marked as NOT
VALID, I encountered an error when adding a NOT ENFORCED foreign key (FK)
constraint to a partitioned table [2]. Alvaro also confirmed off-list that NOT
VALID FK constraints have not yet been implemented. This patch addresses that
gap.
When adding a new FK constraint or attaching a partitioned table, where
matching FK constraints are merged, we allow the parent constraint to be NOT
VALID while the child constraint remains VALID, which is harmless. However, the
reverse scenario -- where the parent constraint is VALID and the child is NOT
VALID -- is incorrect. To address this, when merging a NOT VALID FK constraint
from the child with a VALID parent constraint, it implicitly validates the
child constraint against its existing data and marks it as VALID. This behavior
aligns with adding a new FK constraint directly to the child table, which would
also validate the existing data.
The 0001 patch focuses on code refactoring and does not modify or introduce new
behaviors. It splits ATExecValidateConstraint() into two separate functions for
handling FK and CHECK constraints. For this feature, I wanted to reuse the FK
validation logic and make it recursive for partitioned tables, necessitating
its separation. Although CHECK constraint validation isn't required for this
work, separating it simplifies ATExecValidateConstraint() and prepares the
codebase for adding support for other constraint types (e.g., NOT NULL) in the
future. Additional changes in the refactoring include renaming the variable
tuple to contuple within these functions, duplicating a few lines of code that
update pg_constraint.convalidated, and running pgindent, which rearranged the
code and comments. I hope the duplication is not a significant concern.
Please review the attached patches. Any comments or suggestions would
be greatly appreciated. Thank you!
1]
https://postgr.es/m/caaj_b962c5acyw9kut_r_er5qs3fugbe4az-sp-vuwps-w-...@mail.gmail.com
2]
https://postgr.es/m/caaj_b94+0-yfj4lopvqz_+c7ckkuya77g_5rgtjvnuyepuh...@mail.gmail.com
--
Regards,
Amul Sul
EDB: http://www.enterprisedb.com
From 7b03ff68ae24ded5547a9e268be8692b196cf509 Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Thu, 2 Jan 2025 17:15:30 +0530
Subject: [PATCH v1 1/2] Refactor: Split ATExecValidateConstraint()
Splitting ATExecValidateConstraint() into two separate functions to
handle FOREIGN KEY and CHECK constraints respectively. The next patch
requires the FOREIGN KEY validation code in a separate function so it
can be called directly. Additionally, the function needs to be
recursive to handle NOT VALID constraints on declarative partitions.
Although moving the CHECK constraint-related code is not strictly
necessary for this task, maintaining separate code for FOREIGN KEY
constraints makes it logical to do the same for CHECK constraints.
This separation will also simplify adding support for other
constraints (e.g., NOT NULL) in ATExecValidateConstraint() in the
future.
---
src/backend/commands/tablecmds.c | 282 +++++++++++++++++++------------
1 file changed, 171 insertions(+), 111 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 33ea619224b..afe71fb7e8e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -394,6 +394,11 @@ static ObjectAddress ATExecAlterConstraint(Relation rel, AlterTableCmd *cmd,
static bool ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
Relation rel, HeapTuple contuple, List **otherrelids,
LOCKMODE lockmode);
+static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ HeapTuple contuple, LOCKMODE lockmode);
+static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ char *constrName, HeapTuple contuple,
+ bool recurse, bool recursing, LOCKMODE lockmode);
static ObjectAddress ATExecValidateConstraint(List **wqueue,
Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
@@ -11951,6 +11956,169 @@ ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
return changed;
}
+/*
+ * QueueFKConstraintValidation
+ *
+ * Add an entry to the wqueue to validate the given foreign key constraint in
+ * Phase 3 and update the convalidated field in the pg_constraint catalog
+ * for the specified relation.
+ */
+static void
+QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ HeapTuple contuple, LOCKMODE lockmode)
+{
+ Form_pg_constraint con;
+ AlteredTableInfo *tab;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+
+ con = (Form_pg_constraint) GETSTRUCT(contuple);
+ Assert(con->contype == CONSTRAINT_FOREIGN);
+
+ if (rel->rd_rel->relkind == RELKIND_RELATION)
+ {
+ NewConstraint *newcon;
+ Constraint *fkconstraint;
+
+ /* Queue validation for phase 3 */
+ fkconstraint = makeNode(Constraint);
+ /* for now this is all we need */
+ fkconstraint->conname = pstrdup(NameStr(con->conname));
+
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = fkconstraint->conname;
+ newcon->contype = CONSTR_FOREIGN;
+ newcon->refrelid = con->confrelid;
+ newcon->refindid = con->conindid;
+ newcon->conid = con->oid;
+ newcon->qual = (Node *) fkconstraint;
+
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->constraints = lappend(tab->constraints, newcon);
+ }
+
+ /*
+ * We disallow creating invalid foreign keys to or from partitioned
+ * tables, so ignoring the recursion bit is okay.
+ */
+
+ /*
+ * Now update the catalog, while we have the door open.
+ */
+ copyTuple = heap_copytuple(contuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->convalidated = true;
+ CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+ heap_freetuple(copyTuple);
+}
+
+/*
+ * QueueCheckConstraintValidation
+ *
+ * Add an entry to the wqueue to validate the given check constraint in Phase 3
+ * and update the convalidated field in the pg_constraint catalog for the
+ * specified relation and all its inheriting children.
+ */
+static void
+QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ char *constrName, HeapTuple contuple,
+ bool recurse, bool recursing, LOCKMODE lockmode)
+{
+ Form_pg_constraint con;
+ AlteredTableInfo *tab;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+
+ List *children = NIL;
+ ListCell *child;
+ NewConstraint *newcon;
+ Datum val;
+ char *conbin;
+
+ con = (Form_pg_constraint) GETSTRUCT(contuple);
+ Assert(con->contype == CONSTRAINT_CHECK);
+
+ /*
+ * If we're recursing, the parent has already done this, so skip it.
+ * Also, if the constraint is a NO INHERIT constraint, we shouldn't try to
+ * look for it in the children.
+ */
+ if (!recursing && !con->connoinherit)
+ children = find_all_inheritors(RelationGetRelid(rel),
+ lockmode, NULL);
+
+ /*
+ * For CHECK constraints, we must ensure that we only mark the constraint
+ * as validated on the parent if it's already validated on the children.
+ *
+ * We recurse before validating on the parent, to reduce risk of
+ * deadlocks.
+ */
+ foreach(child, children)
+ {
+ Oid childoid = lfirst_oid(child);
+ Relation childrel;
+
+ if (childoid == RelationGetRelid(rel))
+ continue;
+
+ /*
+ * If we are told not to recurse, there had better not be any child
+ * tables, because we can't mark the constraint on the parent valid
+ * unless it is valid for all child tables.
+ */
+ if (!recurse)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("constraint must be validated on child tables too")));
+
+ /* find_all_inheritors already got lock */
+ childrel = table_open(childoid, NoLock);
+
+ ATExecValidateConstraint(wqueue, childrel, constrName, false,
+ true, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ /* Queue validation for phase 3 */
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = constrName;
+ newcon->contype = CONSTR_CHECK;
+ newcon->refrelid = InvalidOid;
+ newcon->refindid = InvalidOid;
+ newcon->conid = con->oid;
+
+ val = SysCacheGetAttrNotNull(CONSTROID, contuple,
+ Anum_pg_constraint_conbin);
+ conbin = TextDatumGetCString(val);
+ newcon->qual = (Node *) stringToNode(conbin);
+
+ /* Find or create work queue entry for this table */
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->constraints = lappend(tab->constraints, newcon);
+
+ /*
+ * Invalidate relcache so that others see the new validated constraint.
+ */
+ CacheInvalidateRelcache(rel);
+
+ /*
+ * Now update the catalog, while we have the door open.
+ */
+ copyTuple = heap_copytuple(contuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->convalidated = true;
+ CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+ heap_freetuple(copyTuple);
+}
+
/*
* ALTER TABLE VALIDATE CONSTRAINT
*
@@ -12010,124 +12178,16 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
if (!con->convalidated)
{
- AlteredTableInfo *tab;
- HeapTuple copyTuple;
- Form_pg_constraint copy_con;
-
if (con->contype == CONSTRAINT_FOREIGN)
{
- NewConstraint *newcon;
- Constraint *fkconstraint;
-
- /* Queue validation for phase 3 */
- fkconstraint = makeNode(Constraint);
- /* for now this is all we need */
- fkconstraint->conname = constrName;
-
- newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
- newcon->name = constrName;
- newcon->contype = CONSTR_FOREIGN;
- newcon->refrelid = con->confrelid;
- newcon->refindid = con->conindid;
- newcon->conid = con->oid;
- newcon->qual = (Node *) fkconstraint;
-
- /* Find or create work queue entry for this table */
- tab = ATGetQueueEntry(wqueue, rel);
- tab->constraints = lappend(tab->constraints, newcon);
-
- /*
- * We disallow creating invalid foreign keys to or from
- * partitioned tables, so ignoring the recursion bit is okay.
- */
+ QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode);
}
else if (con->contype == CONSTRAINT_CHECK)
{
- List *children = NIL;
- ListCell *child;
- NewConstraint *newcon;
- Datum val;
- char *conbin;
-
- /*
- * If we're recursing, the parent has already done this, so skip
- * it. Also, if the constraint is a NO INHERIT constraint, we
- * shouldn't try to look for it in the children.
- */
- if (!recursing && !con->connoinherit)
- children = find_all_inheritors(RelationGetRelid(rel),
- lockmode, NULL);
-
- /*
- * For CHECK constraints, we must ensure that we only mark the
- * constraint as validated on the parent if it's already validated
- * on the children.
- *
- * We recurse before validating on the parent, to reduce risk of
- * deadlocks.
- */
- foreach(child, children)
- {
- Oid childoid = lfirst_oid(child);
- Relation childrel;
-
- if (childoid == RelationGetRelid(rel))
- continue;
-
- /*
- * If we are told not to recurse, there had better not be any
- * child tables, because we can't mark the constraint on the
- * parent valid unless it is valid for all child tables.
- */
- if (!recurse)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("constraint must be validated on child tables too")));
-
- /* find_all_inheritors already got lock */
- childrel = table_open(childoid, NoLock);
-
- ATExecValidateConstraint(wqueue, childrel, constrName, false,
- true, lockmode);
- table_close(childrel, NoLock);
- }
-
- /* Queue validation for phase 3 */
- newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
- newcon->name = constrName;
- newcon->contype = CONSTR_CHECK;
- newcon->refrelid = InvalidOid;
- newcon->refindid = InvalidOid;
- newcon->conid = con->oid;
-
- val = SysCacheGetAttrNotNull(CONSTROID, tuple,
- Anum_pg_constraint_conbin);
- conbin = TextDatumGetCString(val);
- newcon->qual = (Node *) stringToNode(conbin);
-
- /* Find or create work queue entry for this table */
- tab = ATGetQueueEntry(wqueue, rel);
- tab->constraints = lappend(tab->constraints, newcon);
-
- /*
- * Invalidate relcache so that others see the new validated
- * constraint.
- */
- CacheInvalidateRelcache(rel);
+ QueueCheckConstraintValidation(wqueue, conrel, rel, constrName,
+ tuple, recurse, recursing, lockmode);
}
- /*
- * Now update the catalog, while we have the door open.
- */
- copyTuple = heap_copytuple(tuple);
- copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
- copy_con->convalidated = true;
- CatalogTupleUpdate(conrel, ©Tuple->t_self, copyTuple);
-
- InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
-
- heap_freetuple(copyTuple);
-
ObjectAddressSet(address, ConstraintRelationId, con->oid);
}
else
--
2.43.5
From 78731d71a3351af19af32c2145b1175e3674a433 Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Thu, 2 Jan 2025 19:04:49 +0530
Subject: [PATCH v1 2/2] Allow NOT VALID foreign key constraints on partitioned
tables.
When merging an existing foreign key during the addition or attachment
of a partition, consider the following:
1. If the parent foreign key constraint being added is NOT VALID, the
validity flag of the matching child constraint is ignored. It is
harmless for the child constraint to remain valid.
2. If the parent foreign key constraint being added is VALID and the
matching child constraint is NOT VALID, the we implicitly validates
the child constraint, marking it as valid. This behavior is
consistent with creating a new constraint on the child table rather
than attaching it to an existing one, as it ensures the child data
is validated.
---
doc/src/sgml/ref/alter_table.sgml | 2 -
src/backend/commands/tablecmds.c | 126 +++++++++++++++-------
src/test/regress/expected/foreign_key.out | 76 +++++++++++--
src/test/regress/sql/foreign_key.sql | 58 +++++++++-
4 files changed, 214 insertions(+), 48 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c8f7ab7d956..c9dc7c44877 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -486,8 +486,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
Additional restrictions apply when unique or primary key constraints
are added to partitioned tables; see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
- Also, foreign key constraints on partitioned
- tables may not be declared <literal>NOT VALID</literal> at present.
</para>
</listitem>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index afe71fb7e8e..6a5cfd94792 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -568,8 +568,9 @@ static void createForeignKeyActionTriggers(Relation rel, Oid refRelOid,
Oid indexOid,
Oid parentDelTrigger, Oid parentUpdTrigger,
Oid *deleteTrigOid, Oid *updateTrigOid);
-static bool tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
- Oid partRelid,
+static bool tryAttachPartitionForeignKey(List **wqueue,
+ ForeignKeyCacheInfo *fk,
+ Relation partition,
Oid parentConstrOid, int numfks,
AttrNumber *mapped_conkey, AttrNumber *confkey,
Oid *conpfeqop,
@@ -9745,22 +9746,12 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* Validity checks (permission checks wait till we have the column
* numbers)
*/
- if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
- {
- if (!recurse)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("cannot use ONLY for foreign key on partitioned table \"%s\" referencing relation \"%s\"",
- RelationGetRelationName(rel),
- RelationGetRelationName(pkrel))));
- if (fkconstraint->skip_validation && !fkconstraint->initially_valid)
- ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("cannot add NOT VALID foreign key on partitioned table \"%s\" referencing relation \"%s\"",
- RelationGetRelationName(rel),
- RelationGetRelationName(pkrel)),
- errdetail("This feature is not yet supported on partitioned tables.")));
- }
+ if (!recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot use ONLY for foreign key on partitioned table \"%s\" referencing relation \"%s\"",
+ RelationGetRelationName(rel),
+ RelationGetRelationName(pkrel))));
if (pkrel->rd_rel->relkind != RELKIND_RELATION &&
pkrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
@@ -10752,8 +10743,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
*/
for (int i = 0; i < pd->nparts; i++)
{
- Oid partitionId = pd->oids[i];
- Relation partition = table_open(partitionId, lockmode);
+ Relation partition = table_open(pd->oids[i], lockmode);
List *partFKs;
AttrMap *attmap;
AttrNumber mapped_fkattnum[INDEX_MAX_KEYS];
@@ -10777,8 +10767,9 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
ForeignKeyCacheInfo *fk;
fk = lfirst_node(ForeignKeyCacheInfo, cell);
- if (tryAttachPartitionForeignKey(fk,
- partitionId,
+ if (tryAttachPartitionForeignKey(wqueue,
+ fk,
+ partition,
parentConstr,
numfks,
mapped_fkattnum,
@@ -11230,8 +11221,9 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
{
ForeignKeyCacheInfo *fk = lfirst_node(ForeignKeyCacheInfo, lc);
- if (tryAttachPartitionForeignKey(fk,
- RelationGetRelid(partRel),
+ if (tryAttachPartitionForeignKey(wqueue,
+ fk,
+ partRel,
parentConstrOid,
numfks,
mapped_conkey,
@@ -11334,8 +11326,9 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
* return false.
*/
static bool
-tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
- Oid partRelid,
+tryAttachPartitionForeignKey(List **wqueue,
+ ForeignKeyCacheInfo *fk,
+ Relation partition,
Oid parentConstrOid,
int numfks,
AttrNumber *mapped_conkey,
@@ -11354,6 +11347,7 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
HeapTuple trigtup;
Oid insertTriggerOid,
updateTriggerOid;
+ bool validateConstr;
parentConstrTup = SearchSysCache1(CONSTROID,
ObjectIdGetDatum(parentConstrOid));
@@ -11382,9 +11376,10 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
}
/*
- * Looks good so far; do some more extensive checks. Presumably the check
- * for 'convalidated' could be dropped, since we don't really care about
- * that, but let's be careful for now.
+ * Looks good so far; perform more extensive checks except for
+ * convalidated. There’s no need to worry about it because attaching a
+ * valid parent constraint to an invalid child constraint will eventually
+ * trigger implicit data validation for the child.
*/
partcontup = SearchSysCache1(CONSTROID,
ObjectIdGetDatum(fk->conoid));
@@ -11392,7 +11387,6 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
partConstr = (Form_pg_constraint) GETSTRUCT(partcontup);
if (OidIsValid(partConstr->conparentid) ||
- !partConstr->convalidated ||
partConstr->condeferrable != parentConstr->condeferrable ||
partConstr->condeferred != parentConstr->condeferred ||
partConstr->confupdtype != parentConstr->confupdtype ||
@@ -11404,6 +11398,16 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
return false;
}
+ /*
+ * If a valid parent constraint is attached to a NOT VALID child
+ * constraint, implicitly trigger validation for the child constraint.
+ * This behavior aligns with creating a new constraint on the child table
+ * rather than attaching it to the existing one, as it would ultimately
+ * validate the child data. Conversely, having an invalid parent
+ * constraint while the child constraint is valid doesn't cause any harm.
+ */
+ validateConstr = (parentConstr->convalidated && !partConstr->convalidated);
+
ReleaseSysCache(partcontup);
ReleaseSysCache(parentConstrTup);
@@ -11451,7 +11455,8 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
systable_endscan(scan);
- ConstraintSetParentConstraint(fk->conoid, parentConstrOid, partRelid);
+ ConstraintSetParentConstraint(fk->conoid, parentConstrOid,
+ RelationGetRelid(partition));
/*
* Like the constraint, attach partition's "check" triggers to the
@@ -11462,10 +11467,10 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
&insertTriggerOid, &updateTriggerOid);
Assert(OidIsValid(insertTriggerOid) && OidIsValid(parentInsTrigger));
TriggerSetParentTrigger(trigrel, insertTriggerOid, parentInsTrigger,
- partRelid);
+ RelationGetRelid(partition));
Assert(OidIsValid(updateTriggerOid) && OidIsValid(parentUpdTrigger));
TriggerSetParentTrigger(trigrel, updateTriggerOid, parentUpdTrigger,
- partRelid);
+ RelationGetRelid(partition));
/*
* If the referenced table is partitioned, then the partition we're
@@ -11543,6 +11548,25 @@ tryAttachPartitionForeignKey(ForeignKeyCacheInfo *fk,
}
CommandCounterIncrement();
+
+ /* Perform the constraint validation for the reason explained earlier. */
+ if (validateConstr)
+ {
+ Relation conrel;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ partcontup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(fk->conoid));
+ if (!HeapTupleIsValid(partcontup))
+ elog(ERROR, "cache lookup failed for constraint %u", fk->conoid);
+
+ /* Using the same lock as used in AT_ValidateConstraint */
+ QueueFKConstraintValidation(wqueue, conrel, partition, partcontup,
+ ShareUpdateExclusiveLock);
+ ReleaseSysCache(partcontup);
+ table_close(conrel, RowExclusiveLock);
+ }
+
return true;
}
@@ -11961,7 +11985,7 @@ ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel,
*
* Add an entry to the wqueue to validate the given foreign key constraint in
* Phase 3 and update the convalidated field in the pg_constraint catalog
- * for the specified relation.
+ * for the specified relation and all its children.
*/
static void
QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
@@ -11999,9 +12023,39 @@ QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation rel,
}
/*
- * We disallow creating invalid foreign keys to or from partitioned
- * tables, so ignoring the recursion bit is okay.
+ * If the table at either end of the constraint is partitioned, we need to
+ * recurse and handle every constraint that is a child of this constraint.
*/
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ get_rel_relkind(con->confrelid) == RELKIND_PARTITIONED_TABLE)
+ {
+ ScanKeyData pkey;
+ SysScanDesc pscan;
+ HeapTuple childtup;
+
+ ScanKeyInit(&pkey,
+ Anum_pg_constraint_conparentid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(con->oid));
+
+ pscan = systable_beginscan(conrel, ConstraintParentIndexId,
+ true, NULL, 1, &pkey);
+
+ while (HeapTupleIsValid(childtup = systable_getnext(pscan)))
+ {
+ Form_pg_constraint childcon;
+ Relation childrel;
+
+ childcon = (Form_pg_constraint) GETSTRUCT(childtup);
+ childrel = table_open(childcon->conrelid, lockmode);
+
+ QueueFKConstraintValidation(wqueue, conrel, childrel, childtup,
+ lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ systable_endscan(pscan);
+ }
/*
* Now update the catalog, while we have the door open.
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 3f459f70ac1..f617d519aef 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1571,6 +1571,76 @@ drop table pktable2, fktable2;
--
-- Foreign keys and partitioned tables
--
+-- NOT VALID foreign key on partitioned table
+CREATE TABLE fk_notpartitioned_pk (a int, b int, PRIMARY KEY (a, b));
+CREATE TABLE fk_partitioned_fk (b int, a int) PARTITION BY RANGE (a, b);
+ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;
+-- Attaching a child table with the same valid foreign key constraint.
+CREATE TABLE fk_partitioned_fk_1 (a int, b int);
+ALTER TABLE fk_partitioned_fk_1 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
+-- Child constraint will remain valid.
+SELECT conname, convalidated, conrelid::regclass FROM pg_constraint
+WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;
+ conname | convalidated | conrelid
+------------------------------+--------------+---------------------
+ fk_partitioned_fk_a_b_fkey | f | fk_partitioned_fk
+ fk_partitioned_fk_1_a_b_fkey | t | fk_partitioned_fk_1
+(2 rows)
+
+-- Validate the constraint
+ALTER TABLE fk_partitioned_fk VALIDATE CONSTRAINT fk_partitioned_fk_a_b_fkey;
+-- All constraints are now valid.
+SELECT conname, convalidated, conrelid::regclass FROM pg_constraint
+WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;
+ conname | convalidated | conrelid
+------------------------------+--------------+---------------------
+ fk_partitioned_fk_a_b_fkey | t | fk_partitioned_fk
+ fk_partitioned_fk_1_a_b_fkey | t | fk_partitioned_fk_1
+(2 rows)
+
+-- Attaching a child with a NOT VALID constraint.
+CREATE TABLE fk_partitioned_fk_2 (a int, b int);
+INSERT INTO fk_partitioned_fk_2 VALUES(1000, 1000); -- doesn't exist in referenced table
+ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;
+-- It will fail because the attach operation implicitly validates the data.
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key constraint "fk_partitioned_fk_2_a_b_fkey"
+DETAIL: Key (a, b)=(1000, 1000) is not present in table "fk_notpartitioned_pk".
+-- Remove the invalid data and try again.
+TRUNCATE fk_partitioned_fk_2;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
+-- The child constraint will also be valid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_partitioned_fk_2'::regclass;
+ conname | convalidated
+------------------------------+--------------
+ fk_partitioned_fk_2_a_b_fkey | t
+(1 row)
+
+DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;
+-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table
+CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
+CREATE TABLE fk_notpartitioned_fk (b int, a int);
+ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;
+-- Constraint will be invalid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;
+ conname | convalidated
+--------------------------------+--------------
+ fk_notpartitioned_fk_a_b_fkey | f
+ fk_notpartitioned_fk_a_b_fkey1 | f
+(2 rows)
+
+ALTER TABLE fk_notpartitioned_fk VALIDATE CONSTRAINT fk_notpartitioned_fk_a_b_fkey;
+-- All constraints are now valid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;
+ conname | convalidated
+--------------------------------+--------------
+ fk_notpartitioned_fk_a_b_fkey | t
+ fk_notpartitioned_fk_a_b_fkey1 | t
+(2 rows)
+
+DROP TABLE fk_notpartitioned_fk, fk_partitioned_pk;
-- Creation of a partitioned hierarchy with irregular definitions
CREATE TABLE fk_notpartitioned_pk (fdrop1 int, a int, fdrop2 int, b int,
PRIMARY KEY (a, b));
@@ -1597,12 +1667,6 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
REFERENCES fk_notpartitioned_pk;
ERROR: cannot use ONLY for foreign key on partitioned table "fk_partitioned_fk" referencing relation "fk_notpartitioned_pk"
--- Adding a NOT VALID foreign key on a partitioned table referencing
--- a non-partitioned table fails.
-ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
- REFERENCES fk_notpartitioned_pk NOT VALID;
-ERROR: cannot add NOT VALID foreign key on partitioned table "fk_partitioned_fk" referencing relation "fk_notpartitioned_pk"
-DETAIL: This feature is not yet supported on partitioned tables.
-- these inserts, targeting both the partition directly as well as the
-- partitioned table, should all fail
INSERT INTO fk_partitioned_fk (a,b) VALUES (500, 501);
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 2e710e419c2..d5f68812ba6 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1173,6 +1173,60 @@ drop table pktable2, fktable2;
-- Foreign keys and partitioned tables
--
+-- NOT VALID foreign key on partitioned table
+CREATE TABLE fk_notpartitioned_pk (a int, b int, PRIMARY KEY (a, b));
+CREATE TABLE fk_partitioned_fk (b int, a int) PARTITION BY RANGE (a, b);
+ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;
+
+-- Attaching a child table with the same valid foreign key constraint.
+CREATE TABLE fk_partitioned_fk_1 (a int, b int);
+ALTER TABLE fk_partitioned_fk_1 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 FOR VALUES FROM (0,0) TO (1000,1000);
+
+-- Child constraint will remain valid.
+SELECT conname, convalidated, conrelid::regclass FROM pg_constraint
+WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;
+
+-- Validate the constraint
+ALTER TABLE fk_partitioned_fk VALIDATE CONSTRAINT fk_partitioned_fk_a_b_fkey;
+
+-- All constraints are now valid.
+SELECT conname, convalidated, conrelid::regclass FROM pg_constraint
+WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;
+
+-- Attaching a child with a NOT VALID constraint.
+CREATE TABLE fk_partitioned_fk_2 (a int, b int);
+INSERT INTO fk_partitioned_fk_2 VALUES(1000, 1000); -- doesn't exist in referenced table
+ALTER TABLE fk_partitioned_fk_2 ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk NOT VALID;
+
+-- It will fail because the attach operation implicitly validates the data.
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
+
+-- Remove the invalid data and try again.
+TRUNCATE fk_partitioned_fk_2;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES FROM (1000,1000) TO (2000,2000);
+
+-- The child constraint will also be valid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_partitioned_fk_2'::regclass;
+
+DROP TABLE fk_partitioned_fk, fk_notpartitioned_pk;
+
+-- NOT VALID foreign key on a non-partitioned table referencing a partitioned table
+CREATE TABLE fk_partitioned_pk (a int, b int, PRIMARY KEY (a, b)) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_pk_1 PARTITION OF fk_partitioned_pk FOR VALUES FROM (0,0) TO (1000,1000);
+CREATE TABLE fk_notpartitioned_fk (b int, a int);
+ALTER TABLE fk_notpartitioned_fk ADD FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk NOT VALID;
+
+-- Constraint will be invalid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;
+
+ALTER TABLE fk_notpartitioned_fk VALIDATE CONSTRAINT fk_notpartitioned_fk_a_b_fkey;
+
+-- All constraints are now valid.
+SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'fk_notpartitioned_fk'::regclass;
+
+DROP TABLE fk_notpartitioned_fk, fk_partitioned_pk;
+
-- Creation of a partitioned hierarchy with irregular definitions
CREATE TABLE fk_notpartitioned_pk (fdrop1 int, a int, fdrop2 int, b int,
PRIMARY KEY (a, b));
@@ -1200,10 +1254,6 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
-- a non-partitioned table fails.
ALTER TABLE ONLY fk_partitioned_fk ADD FOREIGN KEY (a, b)
REFERENCES fk_notpartitioned_pk;
--- Adding a NOT VALID foreign key on a partitioned table referencing
--- a non-partitioned table fails.
-ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b)
- REFERENCES fk_notpartitioned_pk NOT VALID;
-- these inserts, targeting both the partition directly as well as the
-- partitioned table, should all fail
--
2.43.5
