Here's an updated version. After wasting some time trying to resolve
"minor last minute issues", I decided to reduce the scope for now: in
the current patch, it's allowed to have foreign keys in partitioned
tables, but it is not possible to have foreign keys that point to
partitioned tables. I have split out some preliminary changes that
intended to support FKs referencing partitioned tables; I intend to
propose that for early v12, to avoid spending any more time this
commitfest on that. Yes, I'm pretty disappointed about that.
0001 prohibits having foreign keys pointing to partitioned tables, as
discussed above.
0002 is a fixup for a bug in the row triggers patch: I had a restriction
earlier that triggers declared internal were not cloned, and I seem to
have lost it in rebase. Reinstate it.
0003 is the matter of interest. This is essentially the same code I
posted earlier, rebased to the committed row triggers patch, with a few
minor bug fixes and some changes in the regression tests to try and make
them more comprehensive, including leaving a partitioned table with an
FK to test that the whole pg_dump thing works via the pg_upgrade test.
An important change is that when a table containing data is attached as
a partition, the data is verified to satisfy the constraint via the
regular alter table phase 3 code.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 0a9cff0ceb4f0e72c09551bcd2d40f31e245267c Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Thu, 29 Mar 2018 15:49:17 -0300
Subject: [PATCH v4 1/3] Refuse a FK pointing to a PK in a partitioned table
---
src/backend/commands/tablecmds.c | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 83a881eff3..d0545495ae 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7211,6 +7211,17 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
pkrel = heap_openrv(fkconstraint->pktable,
ShareRowExclusiveLock);
/*
+ * Disallow a foreign key referencing a partitioned table; supporting
this
+ * case requires more work.
+ */
+ if (pkrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("\"%s\" is a partitioned table",
+ RelationGetRelationName(pkrel)),
+ errdetail("Foreign keys cannot reference
partitioned tables.")));
+
+ /*
* Validity checks (permission checks wait till we have the column
* numbers)
*/
--
2.11.0
>From 7c59061ef6752cae5e96d0c0e6515a43a1f1d5e1 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Mon, 26 Mar 2018 16:01:34 -0300
Subject: [PATCH v4 2/3] don't clone internal triggers
---
src/backend/commands/tablecmds.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d0545495ae..f67eefc74c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14347,6 +14347,10 @@ CloneRowTriggersToPartition(Relation parent, Relation
partition)
if (!TRIGGER_FOR_ROW(trigForm->tgtype))
continue;
+ /* We don't clone internal triggers, either */
+ if (trigForm->tgisinternal)
+ continue;
+
/*
* Complain if we find an unexpected trigger type.
*/
--
2.11.0
>From 0a3a3034222167c71cea8d2d5d14f6915b20e8cf Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Thu, 22 Mar 2018 14:47:12 -0300
Subject: [PATCH v4 3/3] Allow foreign key triggers on partitioned tables
---
src/backend/catalog/pg_constraint.c | 224 +++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 138 +++++++++++++++---
src/backend/parser/parse_utilcmd.c | 12 --
src/backend/utils/adt/ri_triggers.c | 50 +++----
src/bin/pg_dump/pg_dump.c | 43 ++++--
src/include/catalog/pg_constraint_fn.h | 14 ++
src/include/commands/tablecmds.h | 4 +
src/test/regress/expected/alter_table.out | 71 ++++++++-
src/test/regress/expected/create_table.out | 10 --
src/test/regress/expected/foreign_key.out | 107 ++++++++++++++
src/test/regress/sql/alter_table.sql | 45 +++++-
src/test/regress/sql/create_table.sql | 8 --
src/test/regress/sql/foreign_key.sql | 86 +++++++++++
13 files changed, 713 insertions(+), 99 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c
b/src/backend/catalog/pg_constraint.c
index 4f1a27a7d3..749529bf39 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -26,6 +26,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
+#include "commands/tablecmds.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
@@ -377,6 +378,229 @@ CreateConstraintEntry(const char *constraintName,
return conOid;
}
+/*
+ * CloneForeignKeyConstraints
+ * Clone foreign keys from a partitioned table to a newly acquired
+ * partition.
+ *
+ * relationId is a partition of parentId, so we can be certain that it has the
+ * same columns with the same datatypes. They may be in different order,
+ * though.
+ *
+ * Returns a list of ClonedConstraint indicating what was done.
+ */
+List *
+CloneForeignKeyConstraints(Oid parentId, Oid relationId)
+{
+ Relation pg_constraint;
+ Relation parentRel;
+ Relation rel;
+ ScanKeyData key;
+ SysScanDesc scan;
+ TupleDesc tupdesc;
+ HeapTuple tuple;
+ AttrNumber *attmap;
+ List *constraints = 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);
+ 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),
+
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, ConstraintRelidIndexId, true,
+ NULL, 1, &key);
+
+ while ((tuple = systable_getnext(scan)) != NULL)
+ {
+ Form_pg_constraint constrForm = (Form_pg_constraint)
GETSTRUCT(tuple);
+ AttrNumber conkey[INDEX_MAX_KEYS];
+ AttrNumber mapped_conkey[INDEX_MAX_KEYS];
+ AttrNumber confkey[INDEX_MAX_KEYS];
+ Oid conpfeqop[INDEX_MAX_KEYS];
+ Oid conppeqop[INDEX_MAX_KEYS];
+ Oid conffeqop[INDEX_MAX_KEYS];
+ Constraint *fkconstraint;
+ ClonedConstraint *cloned;
+ Oid constrOid;
+ ObjectAddress parentAddr,
+ childAddr;
+ int nelem;
+ int i;
+ ArrayType *arr;
+ Datum datum;
+ bool isnull;
+
+ /* only foreign keys */
+ if (constrForm->contype != CONSTRAINT_FOREIGN)
+ continue;
+
+ ObjectAddressSet(parentAddr, ConstraintRelationId,
+ HeapTupleGetOid(tuple));
+
+ datum = fastgetattr(tuple, Anum_pg_constraint_conkey,
+ tupdesc, &isnull);
+ if (isnull)
+ elog(ERROR, "null conkey");
+ arr = DatumGetArrayTypeP(datum);
+ nelem = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ nelem < 1 ||
+ nelem > INDEX_MAX_KEYS ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != INT2OID)
+ elog(ERROR, "conkey is not a 1-D smallint array");
+ memcpy(conkey, ARR_DATA_PTR(arr), nelem * sizeof(AttrNumber));
+
+ for (i = 0; i < nelem; i++)
+ mapped_conkey[i] = attmap[conkey[i] - 1];
+
+ datum = fastgetattr(tuple, Anum_pg_constraint_confkey,
+ tupdesc, &isnull);
+ if (isnull)
+ elog(ERROR, "null confkey");
+ arr = DatumGetArrayTypeP(datum);
+ nelem = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ nelem < 1 ||
+ nelem > INDEX_MAX_KEYS ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != INT2OID)
+ elog(ERROR, "confkey is not a 1-D smallint array");
+ memcpy(confkey, ARR_DATA_PTR(arr), nelem * sizeof(AttrNumber));
+
+ datum = fastgetattr(tuple, Anum_pg_constraint_conpfeqop,
+ tupdesc, &isnull);
+ if (isnull)
+ elog(ERROR, "null conpfeqop");
+ arr = DatumGetArrayTypeP(datum);
+ nelem = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ nelem < 1 ||
+ nelem > INDEX_MAX_KEYS ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != OIDOID)
+ elog(ERROR, "conpfeqop is not a 1-D OID array");
+ memcpy(conpfeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+
+ datum = fastgetattr(tuple, Anum_pg_constraint_conpfeqop,
+ tupdesc, &isnull);
+ if (isnull)
+ elog(ERROR, "null conpfeqop");
+ arr = DatumGetArrayTypeP(datum);
+ nelem = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ nelem < 1 ||
+ nelem > INDEX_MAX_KEYS ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != OIDOID)
+ elog(ERROR, "conpfeqop is not a 1-D OID array");
+ memcpy(conpfeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+
+ datum = fastgetattr(tuple, Anum_pg_constraint_conppeqop,
+ tupdesc, &isnull);
+ if (isnull)
+ elog(ERROR, "null conppeqop");
+ arr = DatumGetArrayTypeP(datum);
+ nelem = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ nelem < 1 ||
+ nelem > INDEX_MAX_KEYS ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != OIDOID)
+ elog(ERROR, "conppeqop is not a 1-D OID array");
+ memcpy(conppeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+
+ datum = fastgetattr(tuple, Anum_pg_constraint_conffeqop,
+ tupdesc, &isnull);
+ if (isnull)
+ elog(ERROR, "null conffeqop");
+ arr = DatumGetArrayTypeP(datum);
+ nelem = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ nelem < 1 ||
+ nelem > INDEX_MAX_KEYS ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != OIDOID)
+ elog(ERROR, "conffeqop is not a 1-D OID array");
+ memcpy(conffeqop, ARR_DATA_PTR(arr), nelem * sizeof(Oid));
+
+ constrOid =
+ CreateConstraintEntry(NameStr(constrForm->conname),
+
constrForm->connamespace,
+
CONSTRAINT_FOREIGN,
+
constrForm->condeferrable,
+
constrForm->condeferred,
+
constrForm->convalidated,
+
HeapTupleGetOid(tuple),
+ relationId,
+ mapped_conkey,
+ nelem,
+ InvalidOid,
/* not a domain constraint */
+
constrForm->conindid, /* same index */
+
constrForm->confrelid, /* same foreign rel */
+ confkey,
+ conpfeqop,
+ conppeqop,
+ conffeqop,
+ nelem,
+
constrForm->confupdtype,
+
constrForm->confdeltype,
+
constrForm->confmatchtype,
+ NULL,
+ NULL,
+ NULL,
+ NULL,
+ false,
+ 1, false,
true);
+
+ ObjectAddressSet(childAddr, ConstraintRelationId, constrOid);
+ recordDependencyOn(&childAddr, &parentAddr,
DEPENDENCY_INTERNAL_AUTO);
+
+ fkconstraint = makeNode(Constraint);
+ /* for now this is all we need */
+ fkconstraint->fk_upd_action = constrForm->confupdtype;
+ fkconstraint->fk_del_action = constrForm->confdeltype;
+ fkconstraint->deferrable = constrForm->condeferrable;
+ fkconstraint->initdeferred = constrForm->condeferred;
+
+ createForeignKeyTriggers(rel, constrForm->confrelid,
fkconstraint,
+ constrOid,
constrForm->conindid);
+
+ /*
+ * Feed back caller about the constraints we created, so that
they can
+ * set up constraint verification, if needed.
+ */
+ cloned = palloc(sizeof(ClonedConstraint));
+ cloned->refrelid = constrForm->confrelid;
+ cloned->conindid = constrForm->conindid;
+ cloned->conid = constrOid;
+ cloned->constraint = fkconstraint;
+
+ constraints = lappend(constraints, cloned);
+ }
+ systable_endscan(scan);
+
+ pfree(attmap);
+
+ heap_close(rel, NoLock); /* keep lock till commit */
+ heap_close(parentRel, NoLock);
+ heap_close(pg_constraint, RowShareLock);
+
+ return constraints;
+}
/*
* Test whether given name is currently used as a constraint name
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f67eefc74c..c83bd7cb69 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -338,9 +338,6 @@ static void validateCheckConstraint(Relation rel, HeapTuple
constrtup);
static void validateForeignKeyConstraint(char *conname,
Relation rel, Relation
pkrel,
Oid pkindOid, Oid
constraintOid);
-static void createForeignKeyTriggers(Relation rel, Oid refRelOid,
- Constraint *fkconstraint,
- Oid constraintOid, Oid
indexOid);
static void ATController(AlterTableStmt *parsetree,
Relation rel, List *cmds, bool recurse, LOCKMODE
lockmode);
static void ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
@@ -411,8 +408,10 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue,
Constraint *constr,
bool recurse, bool recursing, bool
is_readd,
LOCKMODE lockmode);
-static ObjectAddress ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation
rel,
- Constraint *fkconstraint,
LOCKMODE lockmode);
+static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab,
+ Relation rel, Constraint
*fkconstraint, Oid parentConstr,
+ bool recurse, bool recursing,
+ LOCKMODE lockmode);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
bool recurse, bool recursing,
@@ -480,8 +479,8 @@ static void ComputePartitionAttrs(Relation rel, List
*partParams, AttrNumber *pa
List **partexprs, Oid *partopclass,
Oid *partcollation, char strategy);
static void CreateInheritance(Relation child_rel, Relation parent_rel);
static void RemoveInheritance(Relation child_rel, Relation parent_rel);
-static ObjectAddress ATExecAttachPartition(List **wqueue, Relation rel,
- PartitionCmd *cmd);
+static ObjectAddress ATExecAttachPartition(List **wqueue, AlteredTableInfo
*tab,
+ Relation rel, PartitionCmd *cmd);
static void AttachPartitionEnsureIndexes(Relation rel, Relation attachrel);
static void ValidatePartitionConstraints(List **wqueue, Relation scanrel,
List *scanrel_children,
@@ -505,6 +504,7 @@ static void refuseDupeIndexAttach(Relation parentIdx,
Relation partIdx,
* relkind: relkind to assign to the new relation
* ownerId: if not InvalidOid, use this as the new relation's owner.
* typaddress: if not null, it's set to the pg_type entry's address.
+ * queryString: for error reporting
*
* Note that permissions checks are done against current user regardless of
* ownerId. A nonzero ownerId is used when someone is creating a relation
@@ -908,8 +908,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes and
triggers
- * defined in the parent.
+ * If we're creating a partition, create now all the indexes, triggers,
+ * FKs defined in the parent.
*
* We can't do it earlier, because DefineIndex wants to know the
partition
* key which we just stored.
@@ -961,6 +961,12 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (parent->trigdesc != NULL)
CloneRowTriggersToPartition(parent, rel);
+ /*
+ * And foreign keys too. Note that because we're freshly
creating the
+ * table, there is no need to verify these new constraints.
+ */
+ CloneForeignKeyConstraints(parentId, relationId);
+
heap_close(parent, NoLock);
}
@@ -4173,7 +4179,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation
rel,
break;
case AT_AttachPartition:
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
- ATExecAttachPartition(wqueue, rel,
(PartitionCmd *) cmd->def);
+ ATExecAttachPartition(wqueue, tab, rel,
(PartitionCmd *) cmd->def);
else
ATExecAttachPartitionIdx(wqueue, rel,
((PartitionCmd *) cmd->def)->name);
@@ -7025,7 +7031,9 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab,
Relation rel,
RelationGetNamespace(rel),
NIL);
- address = ATAddForeignKeyConstraint(tab, rel,
newConstraint,
+ address = ATAddForeignKeyConstraint(wqueue, tab, rel,
+
newConstraint, InvalidOid,
+
recurse, false,
lockmode);
break;
@@ -7180,8 +7188,9 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
* We do permissions checks here, however.
*/
static ObjectAddress
-ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
- Constraint *fkconstraint,
LOCKMODE lockmode)
+ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
+ Constraint *fkconstraint, Oid
parentConstr,
+ bool recurse, bool recursing,
LOCKMODE lockmode)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -7226,12 +7235,23 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
* numbers)
*/
if (pkrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ /* fix recursion in ATExecValidateConstraint to enable this
case */
+ if (fkconstraint->skip_validation &&
!fkconstraint->initially_valid)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot add NOT VALID foreign
key to relation \"%s\"",
+
RelationGetRelationName(pkrel))));
+ }
+
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && !recurse)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("cannot reference partitioned table
\"%s\"",
+ errmsg("foreign key referencing partitioned
table \"%s\" must not be ONLY",
RelationGetRelationName(pkrel))));
- if (pkrel->rd_rel->relkind != RELKIND_RELATION)
+ if (pkrel->rd_rel->relkind != RELKIND_RELATION &&
+ pkrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("referenced relation \"%s\" is not a
table",
@@ -7538,7 +7558,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation
rel,
fkconstraint->deferrable,
fkconstraint->initdeferred,
fkconstraint->initially_valid,
-
InvalidOid, /* no parent constraint */
+
parentConstr,
RelationGetRelid(rel),
fkattnum,
numfks,
@@ -7592,6 +7612,40 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
}
/*
+ * When called on a partitioned table, recurse to create the constraint
on
+ * the partitions also.
+ */
+ if (recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ PartitionDesc partdesc;
+
+ partdesc = RelationGetPartitionDesc(rel);
+
+ for (i = 0; i < partdesc->nparts; i++)
+ {
+ Oid partitionId = partdesc->oids[i];
+ Relation partition = heap_open(partitionId,
lockmode);
+ AlteredTableInfo *childtab;
+ ObjectAddress childAddr;
+
+ CheckTableNotInUse(partition, "ALTER TABLE");
+
+ /* Find or create work queue entry for this table */
+ childtab = ATGetQueueEntry(wqueue, partition);
+
+ childAddr =
+ ATAddForeignKeyConstraint(wqueue, childtab,
partition,
+
fkconstraint, constrOid,
+
recurse, true, lockmode);
+
+ /* Record this constraint as dependent on the parent
one */
+ recordDependencyOn(&childAddr, &address,
DEPENDENCY_INTERNAL_AUTO);
+
+ heap_close(partition, NoLock);
+ }
+ }
+
+ /*
* Close pk table, but keep lock until we've committed.
*/
heap_close(pkrel, NoLock);
@@ -7853,8 +7907,8 @@ ATExecValidateConstraint(Relation rel, char *constrName,
bool recurse,
heap_close(refrel, NoLock);
/*
- * Foreign keys do not inherit, so we purposely ignore
the
- * recursion bit here
+ * We disallow creating invalid foreign keys to or from
+ * partitioned tables, so ignoring the recursion bit is
okay.
*/
}
else if (con->contype == CONSTRAINT_CHECK)
@@ -8505,7 +8559,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid,
Constraint *fkconstraint,
* NB: if you change any trigger properties here, see also
* ATExecAlterConstraint.
*/
-static void
+void
createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
Oid constraintOid, Oid
indexOid)
{
@@ -8635,6 +8689,25 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid,
Constraint *fkconstraint,
indexOid, true);
CreateFKCheckTrigger(myRelOid, refRelOid, fkconstraint, constraintOid,
indexOid, false);
+
+ /*
+ * If this is a partitioned table, recurse to create triggers for each
+ * child. We consider that one pg_constraint entry is enough; we only
+ * need the triggers to appear per-partition.
+ */
+ if (get_rel_relkind(refRelOid) == RELKIND_PARTITIONED_TABLE)
+ {
+ ListCell *cell;
+ List *dchildren;
+
+ /* XXX maybe we need a stronger lock? */
+ dchildren = find_inheritance_children(refRelOid, RowShareLock);
+ foreach(cell, dchildren)
+ {
+ createForeignKeyTriggers(rel, lfirst_oid(cell),
fkconstraint,
+
constraintOid, indexOid);
+ }
+ }
}
/*
@@ -13884,7 +13957,8 @@ ValidatePartitionConstraints(List **wqueue, Relation
scanrel,
* Return the address of the newly attached partition.
*/
static ObjectAddress
-ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
+ATExecAttachPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
+ PartitionCmd *cmd)
{
Relation attachrel,
catalog;
@@ -13900,6 +13974,8 @@ ATExecAttachPartition(List **wqueue, Relation rel,
PartitionCmd *cmd)
bool found_whole_row;
Oid defaultPartOid;
List *partBoundConstraint;
+ List *cloned;
+ ListCell *l;
/*
* We must lock the default partition, because attaching a new partition
@@ -14083,6 +14159,28 @@ ATExecAttachPartition(List **wqueue, Relation rel,
PartitionCmd *cmd)
CloneRowTriggersToPartition(rel, attachrel);
/*
+ * Clone foreign key constraints, and setup for Phase 3 to verify them.
+ */
+ cloned = CloneForeignKeyConstraints(RelationGetRelid(rel),
+
RelationGetRelid(attachrel));
+ foreach(l, cloned)
+ {
+ ClonedConstraint *cloned = lfirst(l);
+ NewConstraint *newcon;
+ AlteredTableInfo *parttab = ATGetQueueEntry(wqueue, attachrel);
+
+ newcon = (NewConstraint *) palloc0(sizeof(NewConstraint));
+ newcon->name = cloned->constraint->conname;
+ newcon->contype = CONSTR_FOREIGN;
+ newcon->refrelid = cloned->refrelid;
+ newcon->refindid = cloned->conindid;
+ newcon->conid = cloned->conid;
+ newcon->qual = (Node *) cloned->constraint;
+
+ parttab->constraints = lappend(parttab->constraints, newcon);
+ }
+
+ /*
* Generate partition constraint from the partition bound specification.
* If the parent itself is a partition, make sure to include its
* constraint as well.
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index 0fd14f43c6..513a5dda26 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -749,12 +749,6 @@ transformColumnDefinition(CreateStmtContext *cxt,
ColumnDef *column)
errmsg("foreign key
constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)
- ereport(ERROR,
-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("foreign key
constraints are not supported on partitioned tables"),
-
parser_errposition(cxt->pstate,
-
constraint->location)));
/*
* Fill in the current attribute's name and
throw it into the
@@ -868,12 +862,6 @@ transformTableConstraint(CreateStmtContext *cxt,
Constraint *constraint)
errmsg("foreign key
constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)
- ereport(ERROR,
-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("foreign key
constraints are not supported on partitioned tables"),
- parser_errposition(cxt->pstate,
-
constraint->location)));
cxt->fkconstraints = lappend(cxt->fkconstraints,
constraint);
break;
diff --git a/src/backend/utils/adt/ri_triggers.c
b/src/backend/utils/adt/ri_triggers.c
index 3bb708f863..55390b497b 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -400,7 +400,7 @@ RI_FKey_check(TriggerData *trigdata)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND
...]
+ * SELECT 1 FROM <pktable> x WHERE pkatt1 = $1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* corresponding FK attributes.
@@ -408,7 +408,7 @@ RI_FKey_check(TriggerData *trigdata)
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
pkrelname);
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -536,7 +536,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <pktable> x WHERE pkatt1 = $1 [AND
...]
+ * SELECT 1 FROM <pktable> x WHERE pkatt1 = $1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those of the
* PK attributes themselves.
@@ -544,7 +544,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
*/
initStringInfo(&querybuf);
quoteRelationName(pkrelname, pk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY %s x",
pkrelname);
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s x", pkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -792,7 +792,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> x WHERE $1
= fkatt1 [AND ...]
+ * SELECT 1 FROM <fktable> x WHERE $1 =
fkatt1 [AND ...]
* FOR KEY SHARE OF x
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
@@ -800,7 +800,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY
%s x",
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s
x",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -950,14 +950,14 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * DELETE FROM ONLY <fktable> WHERE $1 =
fkatt1 [AND ...]
+ * DELETE FROM <fktable> WHERE $1 = fkatt1
[AND ...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "DELETE FROM ONLY
%s", fkrelname);
+ appendStringInfo(&querybuf, "DELETE FROM %s",
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -1121,7 +1121,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = $1
[, ...]
+ * UPDATE <fktable> SET fkatt1 = $1 [, ...]
* WHERE $n = fkatt1 [AND
...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes. Note that we
are assuming
@@ -1132,7 +1132,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s
SET", fkrelname);
+ appendStringInfo(&querybuf, "UPDATE %s SET",
fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0, j = riinfo->nkeys; i <
riinfo->nkeys; i++, j++)
@@ -1341,7 +1341,7 @@ ri_setnull(TriggerData *trigdata)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = NULL
[, ...]
+ * UPDATE <fktable> SET fkatt1 = NULL [,
...]
* WHERE $1 = fkatt1 [AND
...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
@@ -1350,7 +1350,7 @@ ri_setnull(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s
SET", fkrelname);
+ appendStringInfo(&querybuf, "UPDATE %s SET",
fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1558,7 +1558,7 @@ ri_setdefault(TriggerData *trigdata)
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 =
DEFAULT [, ...]
+ * UPDATE <fktable> SET fkatt1 = DEFAULT
[, ...]
* WHERE $1 = fkatt1 [AND
...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
@@ -1567,7 +1567,7 @@ ri_setdefault(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s
SET", fkrelname);
+ appendStringInfo(&querybuf, "UPDATE %s SET",
fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1894,8 +1894,8 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel,
Relation pk_rel)
/*----------
* The query string built is:
- * SELECT fk.keycols FROM ONLY relname fk
- * LEFT OUTER JOIN ONLY pkrelname pk
+ * SELECT fk.keycols FROM relname fk
+ * LEFT OUTER JOIN pkrelname pk
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
* WHERE pk.pkkeycol1 IS NULL AND
* For MATCH SIMPLE:
@@ -1921,7 +1921,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel,
Relation pk_rel)
quoteRelationName(pkrelname, pk_rel);
quoteRelationName(fkrelname, fk_rel);
appendStringInfo(&querybuf,
- " FROM ONLY %s fk LEFT OUTER JOIN ONLY
%s pk ON",
+ " FROM %s fk LEFT OUTER JOIN %s pk ON",
fkrelname, pkrelname);
strcpy(pkattname, "pk.");
@@ -2290,22 +2290,6 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation
trig_rel, bool rel_is_pk)
/* Find or create a hashtable entry for the constraint */
riinfo = ri_LoadConstraintInfo(constraintOid);
- /* Do some easy cross-checks against the trigger call data */
- if (rel_is_pk)
- {
- if (riinfo->fk_relid != trigger->tgconstrrelid ||
- riinfo->pk_relid != RelationGetRelid(trig_rel))
- elog(ERROR, "wrong pg_constraint entry for trigger
\"%s\" on table \"%s\"",
- trigger->tgname,
RelationGetRelationName(trig_rel));
- }
- else
- {
- if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||
- riinfo->pk_relid != trigger->tgconstrrelid)
- elog(ERROR, "wrong pg_constraint entry for trigger
\"%s\" on table \"%s\"",
- trigger->tgname,
RelationGetRelationName(trig_rel));
- }
-
return riinfo;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8d65a9ee3..1a45107b41 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7116,13 +7116,23 @@ getConstraints(Archive *fout, TableInfo tblinfo[], int
numTables)
tbinfo->dobj.name);
resetPQExpBuffer(query);
- appendPQExpBuffer(query,
- "SELECT tableoid, oid,
conname, confrelid, "
-
"pg_catalog.pg_get_constraintdef(oid) AS condef "
- "FROM
pg_catalog.pg_constraint "
- "WHERE conrelid =
'%u'::pg_catalog.oid "
- "AND contype = 'f'",
- tbinfo->dobj.catId.oid);
+ if (fout->remoteVersion >= 110000)
+ appendPQExpBuffer(query,
+ "SELECT tableoid,
oid, conname, confrelid, "
+
"pg_catalog.pg_get_constraintdef(oid) AS condef "
+ "FROM
pg_catalog.pg_constraint "
+ "WHERE conrelid =
'%u'::pg_catalog.oid "
+ "AND conparentid = 0 "
+ "AND contype = 'f'",
+
tbinfo->dobj.catId.oid);
+ else
+ appendPQExpBuffer(query,
+ "SELECT tableoid,
oid, conname, confrelid, "
+
"pg_catalog.pg_get_constraintdef(oid) AS condef "
+ "FROM
pg_catalog.pg_constraint "
+ "WHERE conrelid =
'%u'::pg_catalog.oid "
+ "AND contype = 'f'",
+
tbinfo->dobj.catId.oid);
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
ntups = PQntuples(res);
@@ -16374,18 +16384,29 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
}
else if (coninfo->contype == 'f')
{
+ char *only;
+
+ /*
+ * For partitioned tables, it doesn't work to emit constraints
as not
+ * inherited.
+ */
+ if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
+ only = "";
+ else
+ only = "ONLY ";
+
/*
* XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so
that the
* current table data is not processed
*/
- appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
- fmtQualifiedDumpable(tbinfo));
+ appendPQExpBuffer(q, "ALTER TABLE %s%s\n",
+ only,
fmtQualifiedDumpable(tbinfo));
appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
fmtId(coninfo->dobj.name),
coninfo->condef);
- appendPQExpBuffer(delq, "ALTER TABLE ONLY %s ",
- fmtQualifiedDumpable(tbinfo));
+ appendPQExpBuffer(delq, "ALTER TABLE %s%s ",
+ only,
fmtQualifiedDumpable(tbinfo));
appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
fmtId(coninfo->dobj.name));
diff --git a/src/include/catalog/pg_constraint_fn.h
b/src/include/catalog/pg_constraint_fn.h
index 06a2362003..e457afbf29 100644
--- a/src/include/catalog/pg_constraint_fn.h
+++ b/src/include/catalog/pg_constraint_fn.h
@@ -27,6 +27,18 @@ typedef enum ConstraintCategory
CONSTRAINT_ASSERTION /* for future expansion */
} ConstraintCategory;
+/*
+ * Used when cloning a foreign key constraint to a partition, so that the
+ * caller can optionally set up a verification pass for it.
+ */
+typedef struct ClonedConstraint
+{
+ Oid refrelid;
+ Oid conindid;
+ Oid conid;
+ Constraint *constraint;
+} ClonedConstraint;
+
extern Oid CreateConstraintEntry(const char *constraintName,
Oid constraintNamespace,
char constraintType,
@@ -57,6 +69,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conNoInherit,
bool is_internal);
+extern List *CloneForeignKeyConstraints(Oid parentId, Oid relationId);
+
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 06e5180a30..3896da3243 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -74,6 +74,10 @@ extern void find_composite_type_dependencies(Oid typeOid,
extern void check_of_type(HeapTuple typetuple);
+extern void createForeignKeyTriggers(Relation rel, Oid refRelOid,
+ Constraint *fkconstraint, Oid
constraintOid,
+ Oid indexOid);
+
extern void register_on_commit_action(Oid relid, OnCommitAction action);
extern void remove_on_commit_action(Oid relid);
diff --git a/src/test/regress/expected/alter_table.out
b/src/test/regress/expected/alter_table.out
index a80d16a394..2ce5998aa4 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -485,6 +485,73 @@ DROP TABLE attmp5;
DROP TABLE attmp4;
DROP TABLE attmp3;
DROP TABLE attmp2;
+-- Ensure we can add foreign keys from partitioned tables
+SET search_path TO at_tst;
+CREATE SCHEMA at_tst;
+CREATE TABLE at_regular1 (col1 INT PRIMARY KEY);
+CREATE TABLE at_partitioned (col2 INT PRIMARY KEY,
+ reg1_col1 INT NOT NULL) PARTITION BY RANGE (col2);
+ALTER TABLE at_partitioned ADD FOREIGN KEY (reg1_col1) REFERENCES at_regular1;
+CREATE TABLE at_partitioned_0 PARTITION OF at_partitioned
+ FOR VALUES FROM (0) TO (10000);
+-- these fail:
+INSERT INTO at_partitioned VALUES (1000, 42);
+ERROR: insert or update on table "at_partitioned_0" violates foreign key
constraint "at_partitioned_reg1_col1_fkey"
+DETAIL: Key (reg1_col1)=(42) is not present in table "at_regular1".
+-- these work:
+INSERT INTO at_regular1 VALUES (1000);
+INSERT INTO at_partitioned VALUES (42, 1000);
+CREATE TABLE at_partitioned_1 PARTITION OF at_partitioned
+ FOR VALUES FROM (10000) TO (20000);
+CREATE TABLE at_partitioned_2 (reg1_col1 INT, col2 INT);
+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2
+ FOR VALUES FROM (20000) TO (30000);
+ERROR: column "col2" in child table must be marked NOT NULL
+ALTER TABLE at_partitioned_2
+ ALTER col2 SET NOT NULL,
+ ALTER reg1_col1 SET NOT NULL;
+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2
+ FOR VALUES FROM (20000) TO (30000);
+\d at_partitioned
+ Table "at_tst.at_partitioned"
+ Column | Type | Collation | Nullable | Default
+-----------+---------+-----------+----------+---------
+ col2 | integer | | not null |
+ reg1_col1 | integer | | not null |
+Partition key: RANGE (col2)
+Indexes:
+ "at_partitioned_pkey" PRIMARY KEY, btree (col2)
+Foreign-key constraints:
+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES
at_regular1(col1)
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d at_partitioned_0
+ Table "at_tst.at_partitioned_0"
+ Column | Type | Collation | Nullable | Default
+-----------+---------+-----------+----------+---------
+ col2 | integer | | not null |
+ reg1_col1 | integer | | not null |
+Partition of: at_partitioned FOR VALUES FROM (0) TO (10000)
+Indexes:
+ "at_partitioned_0_pkey" PRIMARY KEY, btree (col2)
+Foreign-key constraints:
+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES
at_regular1(col1)
+
+INSERT INTO at_partitioned VALUES (5000, 42);
+ERROR: insert or update on table "at_partitioned_0" violates foreign key
constraint "at_partitioned_reg1_col1_fkey"
+DETAIL: Key (reg1_col1)=(42) is not present in table "at_regular1".
+INSERT INTO at_regular1 VALUES (42), (1042), (2042);
+INSERT INTO at_partitioned VALUES (5000, 42), (15000, 1042), (25000, 2042);
+-- the constraint doesn't exist in the partition, so it cannot be dropped
+ALTER TABLE at_partitioned_0 DROP CONSTRAINT at_partitioned_reg1_col1_fkey;
+ERROR: cannot drop inherited constraint "at_partitioned_reg1_col1_fkey" of
relation "at_partitioned_0"
+-- ok
+ALTER TABLE at_partitioned DROP CONSTRAINT at_partitioned_reg1_col1_fkey;
+\set VERBOSITY terse
+DROP SCHEMA at_tst CASCADE;
+NOTICE: drop cascades to 2 other objects
+\set VERBOSITY default
+RESET search_path;
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
@@ -3305,10 +3372,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
-ERROR: foreign key constraints are not supported on partitioned tables
-LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
- ^
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
ERROR: exclusion constraints are not supported on partitioned tables
LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
diff --git a/src/test/regress/expected/create_table.out
b/src/test/regress/expected/create_table.out
index 39a963888d..e724439037 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -281,16 +281,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
-- unsupported constraint type for partitioned tables
-CREATE TABLE pkrel (
- a int PRIMARY KEY
-);
-CREATE TABLE partitioned (
- a int REFERENCES pkrel(a)
-) PARTITION BY RANGE (a);
-ERROR: foreign key constraints are not supported on partitioned tables
-LINE 2: a int REFERENCES pkrel(a)
- ^
-DROP TABLE pkrel;
CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
diff --git a/src/test/regress/expected/foreign_key.out
b/src/test/regress/expected/foreign_key.out
index fef072eddf..8fa5c7060f 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1415,3 +1415,110 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events
commit;
drop table pktable2, fktable2;
+--
+-- Foreign keys and partitioned tables
+--
+-- partitioned table in the referenced side are not allowed
+CREATE TABLE fk_partitioned_pk (a int, b int, primary key (a, b))
+ PARTITION BY RANGE (a, b);
+-- verify with create table first ...
+CREATE TABLE fk_notpartitioned_fk (a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk);
+ERROR: "fk_partitioned_pk" is a partitioned table
+DETAIL: Foreign keys cannot reference partitioned tables.
+-- and then with alter table.
+CREATE TABLE fk_notpartitioned_fk_2 (a int, b int);
+ALTER TABLE fk_notpartitioned_fk_2 ADD FOREIGN KEY (a, b)
+ REFERENCES fk_partitioned_pk;
+ERROR: "fk_partitioned_pk" is a partitioned table
+DETAIL: Foreign keys cannot reference partitioned tables.
+-- Creation of a FK with a partitioned table in the referencing side
+CREATE TABLE fk_notpartitioned_pk (a int, b int, primary key (a, b));
+CREATE TABLE fk_partitioned_fk (a int, b int) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES
FROM (0,0) TO (1000,1000);
+ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES
fk_notpartitioned_pk;
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES
FROM (1000,1000) TO (2000,2000);
+CREATE TABLE fk_partitioned_fk_3 (a int, b int);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3 FOR VALUES
FROM (2000,2000) TO (3000,3000);
+-- these inserts should fail
+INSERT INTO fk_partitioned_fk (a, b) VALUES (500, 501);
+ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(500, 501) is not present in table "fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk (a, b) VALUES (1500, 1501);
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(1500, 1501) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk (a, b) VALUES (2500, 2501);
+ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(2500, 2501) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk_1 (a, b) VALUES (502, 503);
+ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(502, 503) is not present in table "fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk_2 (a, b) VALUES (1502, 1503);
+ERROR: insert or update on table "fk_partitioned_fk_2" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(1502, 1503) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
+ERROR: insert or update on table "fk_partitioned_fk_3" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(2502, 2503) is not present in table
"fk_notpartitioned_pk".
+-- now create the referenced rows ...
+INSERT INTO fk_notpartitioned_pk VALUES
+ (500, 501), (1500, 1501), (2500, 2501),
+ (502, 503), (1502, 1503), (2502, 2503);
+--- and now the same inserts all work
+INSERT INTO fk_partitioned_fk (a, b) VALUES (500, 501);
+INSERT INTO fk_partitioned_fk (a, b) VALUES (1500, 1501);
+INSERT INTO fk_partitioned_fk (a, b) VALUES (2500, 2501);
+INSERT INTO fk_partitioned_fk_1 (a, b) VALUES (502, 503);
+INSERT INTO fk_partitioned_fk_2 (a, b) VALUES (1502, 1503);
+INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
+-- these updates should fail
+UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 500;
+ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key
constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
+DETAIL: Key (a, b)=(500, 501) is still referenced from table
"fk_partitioned_fk".
+UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 1500;
+ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key
constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
+DETAIL: Key (a, b)=(1500, 1501) is still referenced from table
"fk_partitioned_fk".
+UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 2500;
+ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key
constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
+DETAIL: Key (a, b)=(2500, 2501) is still referenced from table
"fk_partitioned_fk".
+-- these deletes should fail
+DELETE FROM fk_notpartitioned_pk WHERE a = 500;
+ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key
constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
+DETAIL: Key (a, b)=(500, 501) is still referenced from table
"fk_partitioned_fk".
+DELETE FROM fk_notpartitioned_pk WHERE a = 1500;
+ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key
constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
+DETAIL: Key (a, b)=(1500, 1501) is still referenced from table
"fk_partitioned_fk".
+DELETE FROM fk_notpartitioned_pk WHERE a = 2500;
+ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key
constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
+DETAIL: Key (a, b)=(2500, 2501) is still referenced from table
"fk_partitioned_fk".
+-- sub-partitioning should exhibit identical behavior
+CREATE TABLE fk_partitioned_fk_4 PARTITION OF fk_partitioned_fk
+ FOR VALUES FROM (3000,3000) TO (4000,4000) PARTITION BY RANGE (a);
+CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4
+ FOR VALUES FROM (3000) TO (3500);
+CREATE TABLE fk_partitioned_fk_4_2 (a int, b int);
+ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2
+ FOR VALUES FROM (3500) TO (4000);
+-- so these inserts fail:
+INSERT INTO fk_partitioned_fk VALUES (3200, 3201);
+ERROR: insert or update on table "fk_partitioned_fk_4_1" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(3200, 3201) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk VALUES (3600, 3601);
+ERROR: insert or update on table "fk_partitioned_fk_4_2" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(3600, 3601) is not present in table
"fk_notpartitioned_pk".
+-- now create the referenced rows
+INSERT INTO fk_notpartitioned_pk VALUES
+ (3200, 3201), (3600, 3601);
+-- and now the same inserts work
+INSERT INTO fk_partitioned_fk VALUES (3200, 3201);
+INSERT INTO fk_partitioned_fk VALUES (3600, 3601);
+-- verify that DROP works
+DROP TABLE fk_partitioned_fk_2;
+-- verify that attaching a table checks that the existing data satisfies the
+-- constraint
+CREATE TABLE fk_partitioned_fk_2 (a int, b int);
+INSERT INTO fk_partitioned_fk_2 VALUES (1600, 1601);
+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_a_fkey"
+DETAIL: Key (a, b)=(1600, 1601) is not present in table
"fk_notpartitioned_pk".
+-- leave these tables around intentionally
diff --git a/src/test/regress/sql/alter_table.sql
b/src/test/regress/sql/alter_table.sql
index 8198d1e930..aa45048993 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -394,6 +394,50 @@ DROP TABLE attmp3;
DROP TABLE attmp2;
+-- Ensure we can add foreign keys from partitioned tables
+SET search_path TO at_tst;
+CREATE SCHEMA at_tst;
+CREATE TABLE at_regular1 (col1 INT PRIMARY KEY);
+CREATE TABLE at_partitioned (col2 INT PRIMARY KEY,
+ reg1_col1 INT NOT NULL) PARTITION BY RANGE (col2);
+ALTER TABLE at_partitioned ADD FOREIGN KEY (reg1_col1) REFERENCES at_regular1;
+CREATE TABLE at_partitioned_0 PARTITION OF at_partitioned
+ FOR VALUES FROM (0) TO (10000);
+-- these fail:
+INSERT INTO at_partitioned VALUES (1000, 42);
+
+-- these work:
+INSERT INTO at_regular1 VALUES (1000);
+INSERT INTO at_partitioned VALUES (42, 1000);
+
+CREATE TABLE at_partitioned_1 PARTITION OF at_partitioned
+ FOR VALUES FROM (10000) TO (20000);
+CREATE TABLE at_partitioned_2 (reg1_col1 INT, col2 INT);
+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2
+ FOR VALUES FROM (20000) TO (30000);
+ALTER TABLE at_partitioned_2
+ ALTER col2 SET NOT NULL,
+ ALTER reg1_col1 SET NOT NULL;
+ALTER TABLE at_partitioned ATTACH PARTITION at_partitioned_2
+ FOR VALUES FROM (20000) TO (30000);
+
+\d at_partitioned
+\d at_partitioned_0
+
+INSERT INTO at_partitioned VALUES (5000, 42);
+INSERT INTO at_regular1 VALUES (42), (1042), (2042);
+INSERT INTO at_partitioned VALUES (5000, 42), (15000, 1042), (25000, 2042);
+
+-- the constraint doesn't exist in the partition, so it cannot be dropped
+ALTER TABLE at_partitioned_0 DROP CONSTRAINT at_partitioned_reg1_col1_fkey;
+-- ok
+ALTER TABLE at_partitioned DROP CONSTRAINT at_partitioned_reg1_col1_fkey;
+
+\set VERBOSITY terse
+DROP SCHEMA at_tst CASCADE;
+\set VERBOSITY default
+RESET search_path;
+
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
@@ -2035,7 +2079,6 @@ CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah;
ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-- cannot drop column that is part of the partition key
diff --git a/src/test/regress/sql/create_table.sql
b/src/test/regress/sql/create_table.sql
index 7d67ce05d9..235bef13dc 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -298,14 +298,6 @@ CREATE TABLE partitioned (
) PARTITION BY LIST (a1, a2); -- fail
-- unsupported constraint type for partitioned tables
-CREATE TABLE pkrel (
- a int PRIMARY KEY
-);
-CREATE TABLE partitioned (
- a int REFERENCES pkrel(a)
-) PARTITION BY RANGE (a);
-DROP TABLE pkrel;
-
CREATE TABLE partitioned (
a int,
EXCLUDE USING gist (a WITH &&)
diff --git a/src/test/regress/sql/foreign_key.sql
b/src/test/regress/sql/foreign_key.sql
index 5f19dad03c..8d10a01966 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1055,3 +1055,89 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
commit;
drop table pktable2, fktable2;
+
+
+--
+-- Foreign keys and partitioned tables
+--
+
+-- partitioned table in the referenced side are not allowed
+CREATE TABLE fk_partitioned_pk (a int, b int, primary key (a, b))
+ PARTITION BY RANGE (a, b);
+-- verify with create table first ...
+CREATE TABLE fk_notpartitioned_fk (a int, b int,
+ FOREIGN KEY (a, b) REFERENCES fk_partitioned_pk);
+-- and then with alter table.
+CREATE TABLE fk_notpartitioned_fk_2 (a int, b int);
+ALTER TABLE fk_notpartitioned_fk_2 ADD FOREIGN KEY (a, b)
+ REFERENCES fk_partitioned_pk;
+
+-- Creation of a FK with a partitioned table in the referencing side
+CREATE TABLE fk_notpartitioned_pk (a int, b int, primary key (a, b));
+CREATE TABLE fk_partitioned_fk (a int, b int) PARTITION BY RANGE (a, b);
+CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES
FROM (0,0) TO (1000,1000);
+ALTER TABLE fk_partitioned_fk ADD FOREIGN KEY (a, b) REFERENCES
fk_notpartitioned_pk;
+CREATE TABLE fk_partitioned_fk_2 PARTITION OF fk_partitioned_fk FOR VALUES
FROM (1000,1000) TO (2000,2000);
+CREATE TABLE fk_partitioned_fk_3 (a int, b int);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3 FOR VALUES
FROM (2000,2000) TO (3000,3000);
+
+-- these inserts should fail
+INSERT INTO fk_partitioned_fk (a, b) VALUES (500, 501);
+INSERT INTO fk_partitioned_fk (a, b) VALUES (1500, 1501);
+INSERT INTO fk_partitioned_fk (a, b) VALUES (2500, 2501);
+INSERT INTO fk_partitioned_fk_1 (a, b) VALUES (502, 503);
+INSERT INTO fk_partitioned_fk_2 (a, b) VALUES (1502, 1503);
+INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
+
+-- now create the referenced rows ...
+INSERT INTO fk_notpartitioned_pk VALUES
+ (500, 501), (1500, 1501), (2500, 2501),
+ (502, 503), (1502, 1503), (2502, 2503);
+
+--- and now the same inserts all work
+INSERT INTO fk_partitioned_fk (a, b) VALUES (500, 501);
+INSERT INTO fk_partitioned_fk (a, b) VALUES (1500, 1501);
+INSERT INTO fk_partitioned_fk (a, b) VALUES (2500, 2501);
+INSERT INTO fk_partitioned_fk_1 (a, b) VALUES (502, 503);
+INSERT INTO fk_partitioned_fk_2 (a, b) VALUES (1502, 1503);
+INSERT INTO fk_partitioned_fk_3 (a, b) VALUES (2502, 2503);
+
+-- these updates should fail
+UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 500;
+UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 1500;
+UPDATE fk_notpartitioned_pk SET a = a + 1 WHERE a = 2500;
+-- these deletes should fail
+DELETE FROM fk_notpartitioned_pk WHERE a = 500;
+DELETE FROM fk_notpartitioned_pk WHERE a = 1500;
+DELETE FROM fk_notpartitioned_pk WHERE a = 2500;
+
+-- sub-partitioning should exhibit identical behavior
+CREATE TABLE fk_partitioned_fk_4 PARTITION OF fk_partitioned_fk
+ FOR VALUES FROM (3000,3000) TO (4000,4000) PARTITION BY RANGE (a);
+CREATE TABLE fk_partitioned_fk_4_1 PARTITION OF fk_partitioned_fk_4
+ FOR VALUES FROM (3000) TO (3500);
+CREATE TABLE fk_partitioned_fk_4_2 (a int, b int);
+ALTER TABLE fk_partitioned_fk_4 ATTACH PARTITION fk_partitioned_fk_4_2
+ FOR VALUES FROM (3500) TO (4000);
+
+-- so these inserts fail:
+INSERT INTO fk_partitioned_fk VALUES (3200, 3201);
+INSERT INTO fk_partitioned_fk VALUES (3600, 3601);
+-- now create the referenced rows
+INSERT INTO fk_notpartitioned_pk VALUES
+ (3200, 3201), (3600, 3601);
+-- and now the same inserts work
+INSERT INTO fk_partitioned_fk VALUES (3200, 3201);
+INSERT INTO fk_partitioned_fk VALUES (3600, 3601);
+
+-- verify that DROP works
+DROP TABLE fk_partitioned_fk_2;
+
+-- verify that attaching a table checks that the existing data satisfies the
+-- constraint
+CREATE TABLE fk_partitioned_fk_2 (a int, b int);
+INSERT INTO fk_partitioned_fk_2 VALUES (1600, 1601);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
+ FOR VALUES FROM (1000,1000) TO (2000,2000);
+
+-- leave these tables around intentionally
--
2.11.0
