While adding some more tests for the "action" part (i.e. updates and
deletes on the referenced table) I came across a bug that was causing
the server to crash ... but it's actually a preexisting bug in an
assert. The fix is in 0001.
0002 I already posted: don't clone row triggers that are declared
internal. As you noted, there is no test that changes because of this.
I haven't tried yet; the only case that comes to mind is doing something
with a deferred unique constraint. Anyway, it was clear to me from the
beginning that cloning internal triggers was not going to work for the
FK constraints.
0003 is the main patch, which is a bit changed from v4, notably to cover
your review comments:
Peter Eisentraut wrote:
> > - tables and permanent tables.
> > + tables and permanent tables. Also note that while it is permitted to
> > + define a foreign key on a partitioned table, declaring a foreign key
> > + that references a partitioned table is not allowed.
> > <para>
>
> Maybe use "possible" or "supported" instead of "allowed" and "permitted"
> in this and similar cases.
Fixed.
> @@ -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))));
> + }
>
> Maybe this error message should be more along the lines of "is not
> supported yet".
I added errdetail("This feature is not yet supported on partitioned tables.")))
> Also, this restriction should perhaps be mentioned in
> the documentation additions that we have been discussing.
Added a note in ALTER TABLE.
>
> The first few hunks in ri_triggers.c (the ones that refer to the
> pktable) are apparently for the postponed part of the feature, foreign
> keys referencing partitioned tables. So I think those hunks should be
> dropped from this patch.
Yeah, reverted.
> The removal of the ONLY for the foreign key queries also affects
> inherited tables, in undesirable ways. For example, consider this
> script: [...]
> With the patch, this will have deleted the (111, 1) record in test2a,
> which it did not do before.
>
> I think the trigger functions need to look up whether the table is a
> partitioned table and decide whether the use ONLY based on that.
> (This will probably also apply to the PK side, when that is
> implemented.)
Updated this. I added you test script to inherit.sql.
>
> In pg_dump, maybe this can be refined:
>
> + /*
> + * For partitioned tables, it doesn't work to emit constraints
> as not
> + * inherited.
> + */
> + if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
> + only = "";
> + else
> + only = "ONLY ";
>
> We use ONLY for foreign keys on inherited tables, but foreign keys are
> not inherited anyway, so this is at best future-proofing. We could
> just drop the ONLY unconditionally. Or at least explain this more.
Yeah, I admit this is a bit weird. I expanded the comment but didn't
change the code:
/*
* Foreign keys on partitioned tables are always declared as
inheriting
* to partitions; for all other cases, emit them as applying
ONLY
* directly to the named table, because that's how they work for
* regular inherited tables.
*/
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From d5313eea2e0196631d3269f453eb3bad86e5eca5 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Tue, 3 Apr 2018 13:58:49 -0300
Subject: [PATCH v5 1/3] Ancient bug fix
---
src/backend/utils/adt/ri_triggers.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/ri_triggers.c
b/src/backend/utils/adt/ri_triggers.c
index 3bb708f863..d0fe65cea9 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -514,7 +514,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
bool result;
/* Only called for non-null rows */
- Assert(ri_NullCheck(RelationGetDescr(fk_rel), old_row, riinfo, true) ==
RI_KEYS_NONE_NULL);
+ Assert(ri_NullCheck(RelationGetDescr(pk_rel), old_row, riinfo, true) ==
RI_KEYS_NONE_NULL);
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, "SPI_connect failed");
--
2.11.0
>From ed65fe82a8395e086ee0ceeea6243da10c26ac2b 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 v5 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 c8da82217d..c0f987cc81 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14336,6 +14336,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 d7cdeda6707a9435540385accbc047931b73cff1 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 v5 3/3] Allow foreign key triggers on partitioned tables
---
doc/src/sgml/ref/alter_table.sgml | 3 +-
doc/src/sgml/ref/create_table.sgml | 13 +-
src/backend/catalog/pg_constraint.c | 237 +++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 196 +++++++++++++++++++-----
src/backend/parser/parse_utilcmd.c | 12 --
src/backend/utils/adt/ri_triggers.c | 59 ++++---
src/bin/pg_dump/pg_dump.c | 41 +++--
src/include/catalog/pg_constraint_fn.h | 16 ++
src/include/commands/tablecmds.h | 4 +
src/test/regress/expected/alter_table.out | 69 ++++++++-
src/test/regress/expected/create_table.out | 10 --
src/test/regress/expected/foreign_key.out | 189 +++++++++++++++++++++++
src/test/regress/expected/inherit.out | 25 +++
src/test/regress/sql/alter_table.sql | 45 +++++-
src/test/regress/sql/create_table.sql | 8 -
src/test/regress/sql/foreign_key.sql | 149 ++++++++++++++++++
src/test/regress/sql/inherit.sql | 12 ++
17 files changed, 973 insertions(+), 115 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml
b/doc/src/sgml/ref/alter_table.sgml
index 69f3355ede..bd2262761e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -368,7 +368,8 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
specified check constraints). But the
database will not assume that the constraint holds for all rows in
the table, until it is validated by using the <literal>VALIDATE
- CONSTRAINT</literal> option.
+ CONSTRAINT</literal> option. Foreign key constraints on partitioned
+ tables may not be declared <literal>NOT VALID</literal> at present.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 14a43b45e9..d49899c497 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -546,9 +546,12 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- Partitioned tables do not support <literal>EXCLUDE</literal> or
- <literal>FOREIGN KEY</literal> constraints; however, you can define
- these constraints on individual partitions.
+ Partitioned tables do not support <literal>EXCLUDE</literal> constraints;
+ however, you can define these constraints on individual partitions.
+ Also, while it's possible to define <literal>PRIMARY KEY</literal>
+ constraints on partitioned tables, it is not supported to create foreign
+ keys cannot that reference them. This restriction will be lifted in a
+ future release.
</para>
</listitem>
@@ -907,7 +910,9 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
must have <literal>REFERENCES</literal> permission on the referenced
table
(either the whole table, or the specific referenced columns).
Note that foreign key constraints cannot be defined between temporary
- tables and permanent tables.
+ tables and permanent tables. Also note that while it is possible to
+ define a foreign key on a partitioned table, it is not possible to
+ declare a foreign key that references a partitioned table.
</para>
<para>
diff --git a/src/backend/catalog/pg_constraint.c
b/src/backend/catalog/pg_constraint.c
index 4f1a27a7d3..153522782d 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,242 @@ 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. The columns may be in different
+ * order, though.
+ *
+ * The *cloned list is appended ClonedConstraint elements describing what was
+ * created.
+ */
+void
+CloneForeignKeyConstraints(Oid parentId, Oid relationId, List **cloned)
+{
+ Relation pg_constraint;
+ Relation parentRel;
+ Relation rel;
+ ScanKeyData key;
+ SysScanDesc scan;
+ TupleDesc tupdesc;
+ HeapTuple tuple;
+ AttrNumber *attmap;
+
+ 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 *newc;
+ 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, false);
+
+ if (cloned)
+ {
+ /*
+ * Feed back caller about the constraints we created,
so that they can
+ * set up constraint verification.
+ */
+ newc = palloc(sizeof(ClonedConstraint));
+ newc->relid = relationId;
+ newc->refrelid = constrForm->confrelid;
+ newc->conindid = constrForm->conindid;
+ newc->conid = constrOid;
+ newc->constraint = fkconstraint;
+
+ *cloned = lappend(*cloned, newc);
+ }
+ }
+ systable_endscan(scan);
+
+ pfree(attmap);
+
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);
+ int i;
+
+ for (i = 0; i < partdesc->nparts; i++)
+ CloneForeignKeyConstraints(RelationGetRelid(rel),
+
partdesc->oids[i],
+
cloned);
+ }
+
+ heap_close(rel, NoLock); /* keep lock till commit */
+ heap_close(parentRel, NoLock);
+ heap_close(pg_constraint, RowShareLock);
+}
/*
* 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 c0f987cc81..c1304729e4 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, NULL);
+
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];
@@ -7220,6 +7229,21 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
errmsg("cannot reference partitioned table
\"%s\"",
RelationGetRelationName(pkrel))));
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ if (!recurse)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("foreign key referencing
partitioned table \"%s\" must not be ONLY",
+
RelationGetRelationName(pkrel))));
+ 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)),
+ errdetail("This feature is not yet
supported on partitioned tables.")));
+ }
+
if (pkrel->rd_rel->relkind != RELKIND_RELATION)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -7527,7 +7551,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation
rel,
fkconstraint->deferrable,
fkconstraint->initdeferred,
fkconstraint->initially_valid,
-
InvalidOid, /* no parent constraint */
+
parentConstr,
RelationGetRelid(rel),
fkattnum,
numfks,
@@ -7553,10 +7577,12 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
ObjectAddressSet(address, ConstraintRelationId, constrOid);
/*
- * Create the triggers that will enforce the constraint.
+ * Create the triggers that will enforce the constraint. We only want
+ * the action triggers to appear for the parent partitioned relation,
+ * even though the constraints also exist below.
*/
createForeignKeyTriggers(rel, RelationGetRelid(pkrel), fkconstraint,
- constrOid, indexOid);
+ constrOid, indexOid,
!recursing);
/*
* Tell Phase 3 to check that the constraint is satisfied by existing
@@ -7581,6 +7607,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);
@@ -7842,8 +7902,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)
@@ -8488,24 +8548,12 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid,
Constraint *fkconstraint,
CommandCounterIncrement();
}
-/*
- * Create the triggers that implement an FK constraint.
- *
- * NB: if you change any trigger properties here, see also
- * ATExecAlterConstraint.
- */
static void
-createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
- Oid constraintOid, Oid
indexOid)
+createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint
*fkconstraint,
+ Oid constraintOid,
Oid indexOid)
{
- Oid myRelOid;
CreateTrigStmt *fk_trigger;
- myRelOid = RelationGetRelid(rel);
-
- /* Make changes-so-far visible */
- CommandCounterIncrement();
-
/*
* Build and execute a CREATE CONSTRAINT TRIGGER statement for the ON
* DELETE action on the referenced table.
@@ -8555,7 +8603,8 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid,
Constraint *fkconstraint,
}
fk_trigger->args = NIL;
- (void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid,
constraintOid,
+ (void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),
+ constraintOid,
indexOid, InvalidOid,
InvalidOid, NULL, true, false);
/* Make changes-so-far visible */
@@ -8610,16 +8659,16 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid,
Constraint *fkconstraint,
}
fk_trigger->args = NIL;
- (void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid,
constraintOid,
+ (void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel),
+ constraintOid,
indexOid, InvalidOid,
InvalidOid, NULL, true, false);
+}
- /* Make changes-so-far visible */
- CommandCounterIncrement();
-
- /*
- * Build and execute CREATE CONSTRAINT TRIGGER statements for the CHECK
- * action for both INSERTs and UPDATEs on the referencing table.
- */
+static void
+createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid,
+ Constraint
*fkconstraint, Oid constraintOid,
+ Oid indexOid)
+{
CreateFKCheckTrigger(myRelOid, refRelOid, fkconstraint, constraintOid,
indexOid, true);
CreateFKCheckTrigger(myRelOid, refRelOid, fkconstraint, constraintOid,
@@ -8627,6 +8676,37 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid,
Constraint *fkconstraint,
}
/*
+ * Create the triggers that implement an FK constraint.
+ *
+ * NB: if you change any trigger properties here, see also
+ * ATExecAlterConstraint.
+ */
+void
+createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
+ Oid constraintOid, Oid
indexOid, bool create_action)
+{
+ /*
+ * For the referenced side, create action triggers, if requested. (If
the
+ * referencing side is partitioned, there is still only one trigger,
which
+ * runs on the referenced side and points to the top of the referencing
+ * hierarchy.)
+ */
+ if (create_action)
+ createForeignKeyActionTriggers(rel, refRelOid, fkconstraint,
constraintOid,
+
indexOid);
+
+ /*
+ * For the referencing side, create the check triggers. We only need
these
+ * on the partitions.
+ */
+ if (rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ createForeignKeyCheckTriggers(RelationGetRelid(rel), refRelOid,
+
fkconstraint, constraintOid, indexOid);
+
+ CommandCounterIncrement();
+}
+
+/*
* ALTER TABLE DROP CONSTRAINT
*
* Like DROP COLUMN, we can't use the normal ALTER TABLE recursion mechanism.
@@ -13873,7 +13953,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;
@@ -13889,6 +13970,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
@@ -14072,6 +14155,35 @@ ATExecAttachPartition(List **wqueue, Relation rel,
PartitionCmd *cmd)
CloneRowTriggersToPartition(rel, attachrel);
/*
+ * Clone foreign key constraints, and setup for Phase 3 to verify them.
+ */
+ cloned = NIL;
+ CloneForeignKeyConstraints(RelationGetRelid(rel),
+
RelationGetRelid(attachrel), &cloned);
+ foreach(l, cloned)
+ {
+ ClonedConstraint *cloned = lfirst(l);
+ NewConstraint *newcon;
+ Relation clonedrel;
+ AlteredTableInfo *parttab;
+
+ clonedrel = relation_open(cloned->relid, NoLock);
+ parttab = ATGetQueueEntry(wqueue, clonedrel);
+
+ 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);
+
+ relation_close(clonedrel, NoLock);
+ }
+
+ /*
* 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 d0fe65cea9..90ddbe5516 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -788,20 +788,23 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
char paramname[16];
const char *querysep;
Oid
queryoids[RI_MAX_NUMKEYS];
+ const char *fk_only;
int i;
/* ----------
* The query string built is
- * SELECT 1 FROM ONLY <fktable> x WHERE $1
= fkatt1 [AND ...]
+ * SELECT 1 FROM [ONLY] <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.
* ----------
*/
initStringInfo(&querybuf);
+ fk_only = fk_rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "SELECT 1 FROM ONLY
%s x",
- fkrelname);
+ appendStringInfo(&querybuf, "SELECT 1 FROM %s%s
x",
+ fk_only,
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -947,17 +950,21 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
char paramname[16];
const char *querysep;
Oid
queryoids[RI_MAX_NUMKEYS];
+ const char *fk_only;
/* ----------
* The query string built is
- * DELETE FROM ONLY <fktable> WHERE $1 =
fkatt1 [AND ...]
+ * DELETE FROM [ONLY] <fktable> WHERE $1 =
fkatt1 [AND ...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
* ----------
*/
initStringInfo(&querybuf);
+ fk_only = fk_rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "DELETE FROM ONLY
%s", fkrelname);
+ appendStringInfo(&querybuf, "DELETE FROM %s%s",
+ fk_only,
fkrelname);
querysep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
{
@@ -1118,10 +1125,11 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
const char *querysep;
const char *qualsep;
Oid
queryoids[RI_MAX_NUMKEYS * 2];
+ const char *fk_only;
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = $1
[, ...]
+ * UPDATE [ONLY] <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
@@ -1131,8 +1139,11 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
*/
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
+ fk_only = fk_rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s
SET", fkrelname);
+ appendStringInfo(&querybuf, "UPDATE %s%s SET",
+ fk_only,
fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0, j = riinfo->nkeys; i <
riinfo->nkeys; i++, j++)
@@ -1337,11 +1348,12 @@ ri_setnull(TriggerData *trigdata)
char paramname[16];
const char *querysep;
const char *qualsep;
+ const char *fk_only;
Oid
queryoids[RI_MAX_NUMKEYS];
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 = NULL
[, ...]
+ * UPDATE [ONLY] <fktable> SET fkatt1 =
NULL [, ...]
* WHERE $1 = fkatt1 [AND
...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
@@ -1349,8 +1361,11 @@ ri_setnull(TriggerData *trigdata)
*/
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
+ fk_only = fk_rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s
SET", fkrelname);
+ appendStringInfo(&querybuf, "UPDATE %s%s SET",
+ fk_only,
fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1554,11 +1569,12 @@ ri_setdefault(TriggerData *trigdata)
const char *querysep;
const char *qualsep;
Oid
queryoids[RI_MAX_NUMKEYS];
+ const char *fk_only;
int i;
/* ----------
* The query string built is
- * UPDATE ONLY <fktable> SET fkatt1 =
DEFAULT [, ...]
+ * UPDATE [ONLY] <fktable> SET fkatt1 =
DEFAULT [, ...]
* WHERE $1 = fkatt1 [AND
...]
* The type id's for the $ parameters are those
of the
* corresponding PK attributes.
@@ -1567,7 +1583,10 @@ ri_setdefault(TriggerData *trigdata)
initStringInfo(&querybuf);
initStringInfo(&qualbuf);
quoteRelationName(fkrelname, fk_rel);
- appendStringInfo(&querybuf, "UPDATE ONLY %s
SET", fkrelname);
+ fk_only = fk_rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
+ appendStringInfo(&querybuf, "UPDATE %s%s SET",
+ fk_only,
fkrelname);
querysep = "";
qualsep = "WHERE";
for (i = 0; i < riinfo->nkeys; i++)
@@ -1838,6 +1857,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel,
Relation pk_rel)
RangeTblEntry *pkrte;
RangeTblEntry *fkrte;
const char *sep;
+ const char *fk_only;
int i;
int save_nestlevel;
char workmembuf[32];
@@ -1894,8 +1914,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 [ONLY] relname fk
+ * LEFT OUTER JOIN pkrelname pk
* ON (pk.pkkeycol1=fk.keycol1 [AND ...])
* WHERE pk.pkkeycol1 IS NULL AND
* For MATCH SIMPLE:
@@ -1920,9 +1940,11 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel,
Relation pk_rel)
quoteRelationName(pkrelname, pk_rel);
quoteRelationName(fkrelname, fk_rel);
+ fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
appendStringInfo(&querybuf,
- " FROM ONLY %s fk LEFT OUTER JOIN ONLY
%s pk ON",
- fkrelname, pkrelname);
+ " FROM %s%s fk LEFT OUTER JOIN %s pk
ON",
+ fk_only, fkrelname, pkrelname);
strcpy(pkattname, "pk.");
strcpy(fkattname, "fk.");
@@ -2298,13 +2320,6 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation
trig_rel, bool rel_is_pk)
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 64cde3266b..5826ddfcbe 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,27 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
}
else if (coninfo->contype == 'f')
{
+ char *only;
+
+ /*
+ * Foreign keys on partitioned tables are always declared as
+ * inheriting to partitions; for all other cases, emit them as
+ * applying ONLY directly to the named table.
+ */
+ only = tbinfo->relkind == RELKIND_PARTITIONED_TABLE ? "" :
"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..0170e08c45 100644
--- a/src/include/catalog/pg_constraint_fn.h
+++ b/src/include/catalog/pg_constraint_fn.h
@@ -27,6 +27,19 @@ 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 relid;
+ Oid refrelid;
+ Oid conindid;
+ Oid conid;
+ Constraint *constraint;
+} ClonedConstraint;
+
extern Oid CreateConstraintEntry(const char *constraintName,
Oid constraintNamespace,
char constraintType,
@@ -57,6 +70,9 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conNoInherit,
bool is_internal);
+extern void CloneForeignKeyConstraints(Oid parentId, Oid relationId,
+ List **cloned);
+
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..04a961d383 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, bool
create_action);
+
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..5db187dd77 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -485,6 +485,71 @@ 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)
+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 +3370,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..460fad994a 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1415,3 +1415,192 @@ 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: cannot reference partitioned table "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;
+ERROR: cannot reference partitioned table "fk_partitioned_pk"
+DROP TABLE fk_partitioned_pk, fk_notpartitioned_fk_2;
+-- 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));
+ALTER TABLE fk_notpartitioned_pk DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+CREATE TABLE fk_partitioned_fk (b int, fdrop1 int, a int) PARTITION BY RANGE
(a, b);
+ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1;
+CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b
int);
+ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP
COLUMN fdrop3;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 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 (b int, fdrop1 int, fdrop2 int, a int);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES
FROM (1000,1000) TO (2000,2000);
+CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4
int, b int, a int)
+ PARTITION BY HASH (a);
+ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
+ DROP COLUMN fdrop3, DROP COLUMN fdrop4;
+CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES
WITH (MODULUS 5, REMAINDER 0);
+CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES
WITH (MODULUS 5, REMAINDER 1);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
+ FOR VALUES FROM (2000,2000) TO (3000,3000);
+-- these inserts, targetting both the partition directly as well as the
+-- partitioned table, should all 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_1 (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_2 (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, 2502);
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(2500, 2502) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2500, 2502);
+ERROR: insert or update on table "fk_partitioned_fk_3_1" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(2500, 2502) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(2501, 2503) is not present in table
"fk_notpartitioned_pk".
+INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2501, 2503);
+ERROR: insert or update on table "fk_partitioned_fk_3_0" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(2501, 2503) is not present in table
"fk_notpartitioned_pk".
+-- but if we insert the values that make them valid, then they work
+INSERT INTO fk_notpartitioned_pk VALUES (500, 501), (1500, 1501),
+ (2500, 2502), (2501, 2503);
+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, 2502);
+INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
+-- these updates would leave lingering rows in the referencing table; disallow
+UPDATE fk_notpartitioned_pk SET b = 502 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 b = 1502 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 b = 2504 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, 2502) is still referenced from table
"fk_partitioned_fk".
+ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_fkey;
+ALTER TABLE fk_partitioned_fk
+ ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk
+ ON UPDATE SET DEFAULT ON DELETE SET NULL,
+ ALTER a SET DEFAULT 42,
+ ALTER b SET DEFAULT 142857;
+-- fails: the default value does not exist:
+UPDATE fk_notpartitioned_pk SET b = b + 1 WHERE b = 2503;
+ERROR: insert or update on table "fk_partitioned_fk_1" violates foreign key
constraint "fk_partitioned_fk_a_fkey"
+DETAIL: Key (a, b)=(42, 142857) is not present in table
"fk_notpartitioned_pk".
+-- works for the FK, fails partitioning constraint:
+DELETE FROM fk_notpartitioned_pk WHERE b in (2502, 2503);
+ERROR: no partition of relation "fk_partitioned_fk" found for row
+DETAIL: Partition key of the failing row contains (a, b) = (null, null).
+CONTEXT: SQL statement "UPDATE "public"."fk_partitioned_fk" SET "a" = NULL,
"b" = NULL WHERE $1 OPERATOR(pg_catalog.=) "a" AND $2 OPERATOR(pg_catalog.=)
"b""
+DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
+-- 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".
+INSERT INTO fk_notpartitioned_pk 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
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index f56151fc1e..5e57b9a465 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1209,6 +1209,31 @@ Inherits: test_foreign_constraints
DROP TABLE test_foreign_constraints_inh;
DROP TABLE test_foreign_constraints;
DROP TABLE test_primary_constraints;
+-- Test foreign key behavior
+create table inh_fk_1 (a int primary key);
+insert into inh_fk_1 values (1), (2), (3);
+create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete
cascade);
+insert into inh_fk_2 values (11, 1), (22, 2), (33, 3);
+create table inh_fk_2_child () inherits (inh_fk_2);
+insert into inh_fk_2_child values (111, 1), (222, 2);
+delete from inh_fk_1 where a = 1;
+select * from inh_fk_1 order by 1;
+ a
+---
+ 2
+ 3
+(2 rows)
+
+select * from inh_fk_2 order by 1, 2;
+ x | y
+-----+---
+ 22 | 2
+ 33 | 3
+ 111 | 1
+ 222 | 2
+(4 rows)
+
+drop table inh_fk_1, inh_fk_2, inh_fk_2_child;
-- Test that parent and child CHECK constraints can be created in either order
create table p1(f1 int);
create table p1_c1() inherits(p1);
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..e534a4f094 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1055,3 +1055,152 @@ 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;
+DROP TABLE fk_partitioned_pk, fk_notpartitioned_fk_2;
+
+-- 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));
+ALTER TABLE fk_notpartitioned_pk DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+CREATE TABLE fk_partitioned_fk (b int, fdrop1 int, a int) PARTITION BY RANGE
(a, b);
+ALTER TABLE fk_partitioned_fk DROP COLUMN fdrop1;
+CREATE TABLE fk_partitioned_fk_1 (fdrop1 int, fdrop2 int, a int, fdrop3 int, b
int);
+ALTER TABLE fk_partitioned_fk_1 DROP COLUMN fdrop1, DROP COLUMN fdrop2, DROP
COLUMN fdrop3;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_1 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 (b int, fdrop1 int, fdrop2 int, a int);
+ALTER TABLE fk_partitioned_fk_2 DROP COLUMN fdrop1, DROP COLUMN fdrop2;
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES
FROM (1000,1000) TO (2000,2000);
+
+CREATE TABLE fk_partitioned_fk_3 (fdrop1 int, fdrop2 int, fdrop3 int, fdrop4
int, b int, a int)
+ PARTITION BY HASH (a);
+ALTER TABLE fk_partitioned_fk_3 DROP COLUMN fdrop1, DROP COLUMN fdrop2,
+ DROP COLUMN fdrop3, DROP COLUMN fdrop4;
+CREATE TABLE fk_partitioned_fk_3_0 PARTITION OF fk_partitioned_fk_3 FOR VALUES
WITH (MODULUS 5, REMAINDER 0);
+CREATE TABLE fk_partitioned_fk_3_1 PARTITION OF fk_partitioned_fk_3 FOR VALUES
WITH (MODULUS 5, REMAINDER 1);
+ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_3
+ FOR VALUES FROM (2000,2000) TO (3000,3000);
+
+-- these inserts, targetting both the partition directly as well as the
+-- partitioned table, should all fail
+INSERT INTO fk_partitioned_fk (a,b) VALUES (500, 501);
+INSERT INTO fk_partitioned_fk_1 (a,b) VALUES (500, 501);
+INSERT INTO fk_partitioned_fk (a,b) VALUES (1500, 1501);
+INSERT INTO fk_partitioned_fk_2 (a,b) VALUES (1500, 1501);
+INSERT INTO fk_partitioned_fk (a,b) VALUES (2500, 2502);
+INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2500, 2502);
+INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
+INSERT INTO fk_partitioned_fk_3 (a,b) VALUES (2501, 2503);
+
+-- but if we insert the values that make them valid, then they work
+INSERT INTO fk_notpartitioned_pk VALUES (500, 501), (1500, 1501),
+ (2500, 2502), (2501, 2503);
+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, 2502);
+INSERT INTO fk_partitioned_fk (a,b) VALUES (2501, 2503);
+
+-- these updates would leave lingering rows in the referencing table; disallow
+UPDATE fk_notpartitioned_pk SET b = 502 WHERE a = 500;
+UPDATE fk_notpartitioned_pk SET b = 1502 WHERE a = 1500;
+UPDATE fk_notpartitioned_pk SET b = 2504 WHERE a = 2500;
+ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_fkey;
+ALTER TABLE fk_partitioned_fk
+ ADD FOREIGN KEY (a, b) REFERENCES fk_notpartitioned_pk
+ ON UPDATE SET DEFAULT ON DELETE SET NULL,
+ ALTER a SET DEFAULT 42,
+ ALTER b SET DEFAULT 142857;
+-- fails: the default value does not exist:
+UPDATE fk_notpartitioned_pk SET b = b + 1 WHERE b = 2503;
+-- works for the FK, fails partitioning constraint:
+DELETE FROM fk_notpartitioned_pk WHERE b in (2502, 2503);
+
+DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
+
+-- 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);
+INSERT INTO fk_notpartitioned_pk 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
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 9397f72c13..5a48376fc0 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -409,6 +409,18 @@ DROP TABLE test_foreign_constraints_inh;
DROP TABLE test_foreign_constraints;
DROP TABLE test_primary_constraints;
+-- Test foreign key behavior
+create table inh_fk_1 (a int primary key);
+insert into inh_fk_1 values (1), (2), (3);
+create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete
cascade);
+insert into inh_fk_2 values (11, 1), (22, 2), (33, 3);
+create table inh_fk_2_child () inherits (inh_fk_2);
+insert into inh_fk_2_child values (111, 1), (222, 2);
+delete from inh_fk_1 where a = 1;
+select * from inh_fk_1 order by 1;
+select * from inh_fk_2 order by 1, 2;
+drop table inh_fk_1, inh_fk_2, inh_fk_2_child;
+
-- Test that parent and child CHECK constraints can be created in either order
create table p1(f1 int);
create table p1_c1() inherits(p1);
--
2.11.0
