From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Foreign keys and partitioned tables
Date: Sun, 11 Mar 2018 23:40:23 -0300
Message-ID: <20180312024023.w2t37viip5wejz4y@alvherre.pgsql> (raw)
In-Reply-To: <20180124175245.hsakxldr4cd74svv@alvherre.pgsql>
[ Resending an email from yesterday. Something is going very wrong with
my outgoing mail provider :-( ]
Rebase of the prior code, on top of the improved row triggers posted
elsewhere. I added some more tests too, and fixed a couple of small
bugs.
(This includes the patches I just posted in the row triggers patch)
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From a34e786924b54d94dbf28c182aed27d0e92dba06 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:01:39 -0300
Subject: [PATCH v3 1/4] add missing CommandCounterIncrement in
StorePartitionBound
---
src/backend/catalog/heap.c | 3 +++
1 file changed, 3 insertions(+)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex cf36ce4add..2b5377bdf2 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -3299,6 +3299,9 @@ StorePartitionBound(Relation rel, Relation parent, PartitionBoundSpec *bound)
heap_freetuple(newtuple);
heap_close(classRel, RowExclusiveLock);
+ /* Make update visible */+ CommandCounterIncrement();+
/*
* The partition constraint for the default partition depends on the
* partition bounds of every other partition, so we must invalidate the
--
2.11.0
From 1165c0438c627ea214de9ee4cffa83d89b0aa485 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:04:13 -0300
Subject: [PATCH v3 2/4] Add missing CommandCounterIncrement() in partitioned
index code
---
src/backend/catalog/pg_constraint.c | 4 ++++
src/backend/commands/indexcmds.c | 6 ++++++
src/backend/commands/tablecmds.c | 2 --
3 files changed, 10 insertions(+), 2 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 731c5e4317..38fdf72877 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -18,6 +18,7 @@
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/sysattr.h"
+#include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
@@ -781,6 +782,9 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
heap_close(constrRel, RowExclusiveLock);
++ /* make update visible */+ CommandCounterIncrement();
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 504806b25b..9ca632865b 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -1003,6 +1003,9 @@ DefineIndex(Oid relationId,
ReleaseSysCache(tup);
heap_close(pg_index, RowExclusiveLock);
heap_freetuple(newtup);
++ /* make update visible */+ CommandCounterIncrement();
}
}
else
@@ -2512,5 +2515,8 @@ IndexSetParentIndex(Relation partitionIdx, Oid parentOid)
recordDependencyOn(&partIdx, &partitionTbl, DEPENDENCY_AUTO);
}
++ /* make our updates visible */+ CommandCounterIncrement();
}
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 74e020bffc..7ecfbc17a0 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -14571,8 +14571,6 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
pfree(attmap);
- CommandCounterIncrement();-
validatePartitionedIndex(parentIdx, parentTbl);
}
--
2.11.0
From ee640078fa9bd7662a28058b8b0affe49cdd336f Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH v3 3/4] Allow FOR EACH ROW triggers on partitioned tables
---
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 4 +-
src/backend/catalog/pg_constraint.c | 3 +
src/backend/commands/tablecmds.c | 92 +++++++++-
src/backend/commands/trigger.c | 191 ++++++++++++++++++--
src/backend/commands/typecmds.c | 1 +
src/backend/tcop/utility.c | 3 +-
src/include/catalog/indexing.h | 2 +
src/include/catalog/pg_constraint.h | 39 ++--
src/include/catalog/pg_constraint_fn.h | 1 +
src/include/commands/trigger.h | 4 +-
src/test/regress/expected/triggers.out | 277 ++++++++++++++++++++++++++---
src/test/regress/input/constraints.source | 16 ++
src/test/regress/output/constraints.source | 26 +++
src/test/regress/sql/triggers.sql | 178 ++++++++++++++++--
15 files changed, 763 insertions(+), 75 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex 2b5377bdf2..0c97ff979b 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -2117,6 +2117,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
false, /* Is Deferrable */
false, /* Is Deferred */
is_validated,
+ InvalidOid, /* no parent constraint */
RelationGetRelid(rel), /* relation */
attNos, /* attrs in the constraint */
keycount, /* # attrs in the constraint */
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 431bc31969..ca0a66753e 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -1283,6 +1283,7 @@ index_constraint_create(Relation heapRelation,
deferrable,
initdeferred,
true,
+ parentConstraintId,
RelationGetRelid(heapRelation),
indexInfo->ii_KeyAttrNumbers,
indexInfo->ii_NumIndexAttrs,
@@ -1361,7 +1362,8 @@ index_constraint_create(Relation heapRelation,
trigger->constrrel = NULL;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
- InvalidOid, conOid, indexRelationId, true);+ InvalidOid, conOid, indexRelationId, InvalidOid,+ InvalidOid, true, false);
}
/*
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 38fdf72877..8bf8c2f4a6 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -53,6 +53,7 @@ CreateConstraintEntry(const char *constraintName,
bool isDeferrable,
bool isDeferred,
bool isValidated,
+ Oid parentConstrId,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
@@ -171,6 +172,7 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
values[Anum_pg_constraint_conindid - 1] = ObjectIdGetDatum(indexRelId);
+ values[Anum_pg_constraint_conparentid - 1] = ObjectIdGetDatum(parentConstrId);
values[Anum_pg_constraint_confrelid - 1] = ObjectIdGetDatum(foreignRelId);
values[Anum_pg_constraint_confupdtype - 1] = CharGetDatum(foreignUpdateType);
values[Anum_pg_constraint_confdeltype - 1] = CharGetDatum(foreignDeleteType);
@@ -773,6 +775,7 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
constrForm = (Form_pg_constraint) GETSTRUCT(newtup);
constrForm->conislocal = false;
constrForm->coninhcount++;
+ constrForm->conparentid = parentConstrId;
CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
ReleaseSysCache(tuple);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 7ecfbc17a0..f5fc0938a6 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -487,6 +487,7 @@ static void ValidatePartitionConstraints(List **wqueue, Relation scanrel,
List *scanrel_children,
List *partConstraint,
bool validate_default);
+static void CloneRowTriggersToPartition(Oid parentId, Oid partitionId);
static ObjectAddress ATExecDetachPartition(Relation rel, RangeVar *name);
static ObjectAddress ATExecAttachPartitionIdx(List **wqueue, Relation rel,
RangeVar *name);
@@ -916,9 +917,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes defined in- * the parent. We can't do it earlier, because DefineIndex wants to know- * the partition key which we just stored.+ * If we're creating a partition, create now all the indexes and triggers+ * defined in the parent.+ *+ * We can't do it earlier, because DefineIndex wants to know the partition+ * key which we just stored.
*/
if (stmt->partbound)
{
@@ -959,6 +962,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
list_free(idxlist);
++ /*+ * If there are any row-level triggers, clone them to the new+ * partition.+ */+ if (parent->trigdesc != NULL)+ CloneRowTriggersToPartition(RelationGetRelid(parent), relationId);+
heap_close(parent, NoLock);
}
@@ -7501,6 +7512,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
fkconstraint->deferrable,
fkconstraint->initdeferred,
fkconstraint->initially_valid,
+ InvalidOid, /* no parent constraint */
RelationGetRelid(rel),
fkattnum,
numfks,
@@ -8455,7 +8467,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8529,7 +8541,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8584,7 +8596,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -11124,7 +11136,7 @@ static void
ATExecEnableDisableTrigger(Relation rel, const char *trigname,
char fires_when, bool skip_system, LOCKMODE lockmode)
{
- EnableDisableTrigger(rel, trigname, fires_when, skip_system);+ EnableDisableTrigger(rel, trigname, fires_when, skip_system, lockmode);
}
/*
@@ -14040,6 +14052,9 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
/* Ensure there exists a correct set of indexes in the partition. */
AttachPartitionEnsureIndexes(rel, attachrel);
+ /* and triggers */+ CloneRowTriggersToPartition(RelationGetRelid(rel), RelationGetRelid(attachrel));+
/*
* Generate partition constraint from the partition bound specification.
* If the parent itself is a partition, make sure to include its
@@ -14256,6 +14271,69 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
}
/*
+ * CloneRowTriggersToPartition+ * subroutine for ATExecAttachPartition/DefineRelation to create row+ * triggers on partitions+ */+static void+CloneRowTriggersToPartition(Oid parentId, Oid partitionId)+{+ Relation pg_trigger;+ ScanKeyData key;+ SysScanDesc scan;+ HeapTuple tuple;++ ScanKeyInit(&key, Anum_pg_trigger_tgrelid, BTEqualStrategyNumber,+ F_OIDEQ, ObjectIdGetDatum(parentId));+ pg_trigger = heap_open(TriggerRelationId, RowExclusiveLock);+ scan = systable_beginscan(pg_trigger, TriggerRelidNameIndexId,+ true, NULL, 1, &key);++ while (HeapTupleIsValid(tuple = systable_getnext(scan)))+ {+ Form_pg_trigger trigForm;+ CreateTrigStmt *trigStmt;++ trigForm = (Form_pg_trigger) GETSTRUCT(tuple);++ /*+ * We only clone a) FOR EACH ROW triggers b) timed AFTER events, c)+ * that are not constraint triggers.+ */+ if (!TRIGGER_FOR_ROW(trigForm->tgtype) ||+ !TRIGGER_FOR_AFTER(trigForm->tgtype) ||+ OidIsValid(trigForm->tgconstraint))+ continue;++ trigStmt = makeNode(CreateTrigStmt);++ trigStmt->trigname = NameStr(trigForm->tgname);+ trigStmt->relation = NULL;+ trigStmt->funcname = NULL;+ trigStmt->args = NULL;+ trigStmt->row = true;+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;+ trigStmt->columns = NIL;+ trigStmt->whenClause = NULL;+ trigStmt->isconstraint = false;+ trigStmt->transitionRels = NIL;+ trigStmt->deferrable = trigForm->tgdeferrable;+ trigStmt->initdeferred = trigForm->tginitdeferred;+ trigStmt->constrrel = NULL;++ CreateTrigger(trigStmt, NULL, partitionId,+ InvalidOid, InvalidOid, InvalidOid,+ trigForm->tgfoid, HeapTupleGetOid(tuple), false, true);+ pfree(trigStmt);+ }++ systable_endscan(scan);++ heap_close(pg_trigger, RowExclusiveLock);+}++/*
* ALTER TABLE DETACH PARTITION
*
* Return the address of the relation that is no longer a partition of rel.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex fbd176b5d0..6a857df566 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -20,6 +20,7 @@
#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/dependency.h"
+#include "catalog/index.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_constraint.h"
@@ -125,6 +126,12 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* indexOid, if nonzero, is the OID of an index associated with the constraint.
* We do nothing with this except store it into pg_trigger.tgconstrindid.
*
+ * funcoid, if nonzero, is the OID of the function to invoke. When this is+ * given, stmt->funcname is ignored.+ *+ * parentTriggerOid, if nonzero, is a trigger that begets this one; so that+ * if that trigger is dropped, this one should be too.+ *
* If isInternal is true then this is an internally-generated trigger.
* This argument sets the tgisinternal field of the pg_trigger entry, and
* if true causes us to modify the given trigger name to ensure uniqueness.
@@ -133,6 +140,10 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* relation, as well as ACL_EXECUTE on the trigger function. For internal
* triggers the caller must apply any required permission checks.
*
+ * When called on partitioned tables, this function recurses to create the+ * trigger on all the partitions, except if isInternal is true, in which+ * case caller is expected to execute recursion on its own.+ *
* Note: can return InvalidObjectAddress if we decided to not create a trigger
* at all, but a foreign-key constraint. This is a kluge for backwards
* compatibility.
@@ -140,7 +151,8 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal)+ Oid funcoid, Oid parentTriggerOid, bool isInternal,+ bool in_partition)
{
int16 tgtype;
int ncolumns;
@@ -159,7 +171,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Relation pgrel;
HeapTuple tuple;
Oid fargtypes[1]; /* dummy */
- Oid funcoid;
Oid funcrettype;
Oid trigoid;
char internaltrigname[NAMEDATALEN];
@@ -179,8 +190,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION ||- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -190,13 +200,49 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
- /* Disallow ROW triggers on partitioned tables */- if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a partitioned table",+ errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
- errdetail("Partitioned tables cannot have ROW triggers.")));+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ /*+ * FOR EACH ROW triggers have further restrictions+ */+ if (stmt->row)+ {+ /*+ * BEFORE triggers FOR EACH ROW are forbidden, because they would+ * allow the user to direct the row to another partition, which+ * isn't implemented in the executor.+ */+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));+++ /*+ * Disallow use of transition tables. If this partitioned table+ * has any partitions, the error would occur below; but if it+ * doesn't then we would only hit that code when the first CREATE+ * TABLE ... PARTITION OF is executed, which is too late. Check+ * early to avoid the problem.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
{
@@ -587,7 +633,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Find and validate the trigger function.
*/
- funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);+ if (!OidIsValid(funcoid))+ funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);
if (!isInternal)
{
aclresult = pg_proc_aclcheck(funcoid, GetUserId(), ACL_EXECUTE);
@@ -651,6 +698,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
stmt->deferrable,
stmt->initdeferred,
true,
+ InvalidOid, /* no parent */
RelationGetRelid(rel),
NULL, /* no conkey */
0,
@@ -733,6 +781,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Build the new pg_trigger tuple.
+ *+ * When we're creating a trigger in a partition, we mark it as internal,+ * even though we don't do the isInternal magic in this function. This+ * makes the triggers in partitions identical to the ones in the+ * partitioned tables, except that they are marked internal.
*/
memset(nulls, false, sizeof(nulls));
@@ -742,7 +795,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
- values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);+ values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
@@ -928,11 +981,18 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* User CREATE TRIGGER, so place dependencies. We make trigger be
* auto-dropped if its relation is dropped or if the FK relation is
* dropped. (Auto drop is compatible with our pre-7.3 behavior.)
+ *+ * Exception: if this trigger comes from a parent partitioned table,+ * then it's not separately drop-able, but goes away if the partition+ * does.
*/
referenced.classId = RelationRelationId;
referenced.objectId = RelationGetRelid(rel);
referenced.objectSubId = 0;
- recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);+ recordDependencyOn(&myself, &referenced, OidIsValid(parentTriggerOid) ?+ DEPENDENCY_INTERNAL_AUTO :+ DEPENDENCY_AUTO);+
if (OidIsValid(constrrelid))
{
referenced.classId = RelationRelationId;
@@ -954,6 +1014,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectSubId = 0;
recordDependencyOn(&referenced, &myself, DEPENDENCY_INTERNAL);
}
++ /* Depends on the parent trigger, if there is one. */+ if (OidIsValid(parentTriggerOid))+ {+ ObjectAddressSet(referenced, TriggerRelationId, parentTriggerOid);+ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL_AUTO);+ }
}
/* If column-specific trigger, add normal dependencies on columns */
@@ -982,6 +1049,56 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
isInternal);
+ /*+ * If this is a non-internal FOR EACH ROW trigger on a partitioned table,+ * recurse for each partition.+ */+ if (!isInternal && stmt->row &&+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);+ List *idxs = NIL;+ List *childrenTbls = NIL;+ ListCell *l;+ int i;++ if (OidIsValid(indexOid))+ {+ idxs = find_inheritance_children(indexOid, AccessShareLock);+ foreach(l, idxs)+ childrenTbls = lappend_oid(childrenTbls,+ IndexGetRelation(lfirst_oid(l),+ false));+ }++ for (i = 0; i < partdesc->nparts; i++)+ {+ Oid indexOnChild = InvalidOid;+ ListCell *l2;++ /* Find which of the child indexes is the one on this partition */+ if (OidIsValid(indexOid))+ {+ forboth(l, idxs, l2, childrenTbls)+ {+ if (lfirst_oid(l2) == partdesc->oids[i])+ {+ indexOnChild = lfirst_oid(l);+ break;+ }+ }+ if (!OidIsValid(indexOnChild))+ elog(ERROR, "failed to find index matching index \"%s\" in partition \"%s\"",+ get_rel_name(indexOid), get_rel_name(partdesc->oids[i]));+ }++ CreateTrigger(copyObject(stmt), queryString,+ partdesc->oids[i], refRelOid,+ constraintOid, indexOnChild,+ InvalidOid, trigoid, isInternal, true);+ }+ }+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
@@ -1579,7 +1696,7 @@ renametrig(RenameStmt *stmt)
*/
void
EnableDisableTrigger(Relation rel, const char *tgname,
- char fires_when, bool skip_system)+ char fires_when, bool skip_system, LOCKMODE lockmode)
{
Relation tgrel;
int nkeys;
@@ -1642,6 +1759,27 @@ EnableDisableTrigger(Relation rel, const char *tgname,
heap_freetuple(newtup);
+ /*+ * When altering FOR EACH ROW triggers on a partitioned table,+ * do the same on the partitions as well.+ */+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ (TRIGGER_FOR_ROW(oldtrig->tgtype)))+ {+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);+ int i;++ for (i = 0; i < partdesc->nparts; i++)+ {+ Relation part;++ part = relation_open(partdesc->oids[i], lockmode);+ EnableDisableTrigger(part, NameStr(oldtrig->tgname),+ fires_when, skip_system, lockmode);+ heap_close(part, NoLock); /* keep lock till commit */+ }+ }+
changed = true;
}
@@ -5123,6 +5261,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
* constraints within the first search-path schema that has any
* matches, but disregard matches in schemas beyond the first match.
* (This is a bit odd but it's the historical behavior.)
+ *+ * A constraint in a partitioned table may have corresponding+ * constraints in the partitions. Grab those too.
*/
conrel = heap_open(ConstraintRelationId, AccessShareLock);
@@ -5217,6 +5358,32 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
constraint->relname)));
}
+ /*+ * Scan for any possible descendants of the constraints. We append+ * whatever we find to the same list that we're scanning; this has the+ * effect that we create new scans for those, too, so if there are+ * further descendents, we'll also catch them.+ */+ foreach(lc, conoidlist)+ {+ Oid parent = lfirst_oid(lc);+ ScanKeyData key;+ SysScanDesc scan;+ HeapTuple tuple;++ ScanKeyInit(&key,+ Anum_pg_constraint_conparentid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(parent));++ scan = systable_beginscan(conrel, ConstraintParentIndexId, true, NULL, 1, &key);++ while (HeapTupleIsValid(tuple = systable_getnext(scan)))+ conoidlist = lappend_oid(conoidlist, HeapTupleGetOid(tuple));++ systable_endscan(scan);+ }+
heap_close(conrel, AccessShareLock);
/*
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.cindex bf3cd3a454..fa3c7099d2 100644--- a/src/backend/commands/typecmds.c+++ b/src/backend/commands/typecmds.c@@ -3153,6 +3153,7 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
false, /* Is Deferrable */
false, /* Is Deferred */
!constr->skip_validation, /* Is Validated */
+ InvalidOid, /* no parent constraint */
InvalidOid, /* not a relation constraint */
NULL,
0,
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex f78efdf359..6df4211280 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -1507,7 +1507,8 @@ ProcessUtilitySlow(ParseState *pstate,
case T_CreateTrigStmt:
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, false);+ InvalidOid, InvalidOid, InvalidOid,+ InvalidOid, false, false);
break;
case T_CreatePLangStmt:
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.hindex 0bb875441e..7dd9d108d6 100644--- a/src/include/catalog/indexing.h+++ b/src/include/catalog/indexing.h@@ -128,6 +128,8 @@ DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(c
#define ConstraintTypidIndexId 2666
DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
#define ConstraintOidIndexId 2667
+DECLARE_INDEX(pg_constraint_conparentid_index, 2579, on pg_constraint using btree(conparentid oid_ops));+#define ConstraintParentIndexId 2579
DECLARE_UNIQUE_INDEX(pg_conversion_default_index, 2668, on pg_conversion using btree(connamespace oid_ops, conforencoding int4_ops, contoencoding int4_ops, oid oid_ops));
#define ConversionDefaultIndexId 2668
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.hindex 8fca86d71e..45b26cdfa8 100644--- a/src/include/catalog/pg_constraint.h+++ b/src/include/catalog/pg_constraint.h@@ -73,6 +73,12 @@ CATALOG(pg_constraint,2606)
Oid conindid; /* index supporting this constraint */
/*
+ * if this constraint is on a partition inherited from a partitioned table,+ * this is the OID of the corresponding constraint in the parent.+ */+ Oid conparentid;++ /*
* These fields, plus confkey, are only meaningful for a foreign-key
* constraint. Otherwise confrelid is 0 and the char fields are spaces.
*/
@@ -150,7 +156,7 @@ typedef FormData_pg_constraint *Form_pg_constraint;
* compiler constants for pg_constraint
* ----------------
*/
-#define Natts_pg_constraint 24+#define Natts_pg_constraint 25
#define Anum_pg_constraint_conname 1
#define Anum_pg_constraint_connamespace 2
#define Anum_pg_constraint_contype 3
@@ -160,21 +166,22 @@ typedef FormData_pg_constraint *Form_pg_constraint;
#define Anum_pg_constraint_conrelid 7
#define Anum_pg_constraint_contypid 8
#define Anum_pg_constraint_conindid 9
-#define Anum_pg_constraint_confrelid 10-#define Anum_pg_constraint_confupdtype 11-#define Anum_pg_constraint_confdeltype 12-#define Anum_pg_constraint_confmatchtype 13-#define Anum_pg_constraint_conislocal 14-#define Anum_pg_constraint_coninhcount 15-#define Anum_pg_constraint_connoinherit 16-#define Anum_pg_constraint_conkey 17-#define Anum_pg_constraint_confkey 18-#define Anum_pg_constraint_conpfeqop 19-#define Anum_pg_constraint_conppeqop 20-#define Anum_pg_constraint_conffeqop 21-#define Anum_pg_constraint_conexclop 22-#define Anum_pg_constraint_conbin 23-#define Anum_pg_constraint_consrc 24+#define Anum_pg_constraint_conparentid 10+#define Anum_pg_constraint_confrelid 11+#define Anum_pg_constraint_confupdtype 12+#define Anum_pg_constraint_confdeltype 13+#define Anum_pg_constraint_confmatchtype 14+#define Anum_pg_constraint_conislocal 15+#define Anum_pg_constraint_coninhcount 16+#define Anum_pg_constraint_connoinherit 17+#define Anum_pg_constraint_conkey 18+#define Anum_pg_constraint_confkey 19+#define Anum_pg_constraint_conpfeqop 20+#define Anum_pg_constraint_conppeqop 21+#define Anum_pg_constraint_conffeqop 22+#define Anum_pg_constraint_conexclop 23+#define Anum_pg_constraint_conbin 24+#define Anum_pg_constraint_consrc 25
/* ----------------
* initial contents of pg_constraint
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.hindex d3351f4a83..06a2362003 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -33,6 +33,7 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool isDeferrable,
bool isDeferred,
bool isValidated,
+ Oid parentConstrId,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.hindex ff5546cf28..fac450e967 100644--- a/src/include/commands/trigger.h+++ b/src/include/commands/trigger.h@@ -159,7 +159,7 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal);+ Oid funcid, Oid parentTriggerOid, bool isInternal, bool recursing);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
@@ -167,7 +167,7 @@ extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
extern ObjectAddress renametrig(RenameStmt *stmt);
extern void EnableDisableTrigger(Relation rel, const char *tgname,
- char fires_when, bool skip_system);+ char fires_when, bool skip_system, LOCKMODE lockmode);
extern void RelationBuildTriggers(Relation relation);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex 99be9ac6e9..521bca7f01 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -1847,7 +1847,74 @@ drop function my_trigger_function();
drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a table+DETAIL: Tables cannot have INSTEAD OF triggers.+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Triggers on partitioned tables cannot have transition tables.+drop table parted_trig;+--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart2 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(4 rows)++drop trigger f on trigpart1; -- fail+ERROR: cannot drop trigger f on table trigpart1 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart2; -- fail+ERROR: cannot drop trigger f on table trigpart2 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart3; -- fail+ERROR: cannot drop trigger f on table trigpart3 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(3 rows)++drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +---------+--------+--------+(0 rows)++drop table trigpart;+drop function trigger_nothing();+--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1864,7 +1931,7 @@ create or replace function trigger_notice() returns trigger as $$
return null;
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1877,36 +1944,62 @@ create trigger trig_del_before before delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
insert into parted2_stmt_trig values (1), (2) returning a
) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted2_stmt_trig BEFORE INSERT for STATEMENT-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW-NOTICE: trigger trig_ins_after on parted2_stmt_trig AFTER INSERT for STATEMENT+NOTICE: trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
-------------------+---
@@ -1918,25 +2011,161 @@ with upd as (
update parted2_stmt_trig set a = a
) update parted_stmt_trig set a = a;
NOTICE: trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT
-NOTICE: trigger trig_upd_before on parted_stmt_trig1 BEFORE UPDATE for ROW-NOTICE: trigger trig_upd_before on parted2_stmt_trig BEFORE UPDATE for STATEMENT-NOTICE: trigger trig_upd_after on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW+NOTICE: trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT
-NOTICE: trigger trig_upd_after on parted2_stmt_trig AFTER UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW+NOTICE: trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50), (1500);+NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW+NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW+drop table parted_trig;+-- test irregular partitions (i.e., different column definitions),+-- including that the WHEN clause works+create function bark(text) returns bool language plpgsql immutable+ as $$ begin raise notice '% <- woof!', $1; return true; end; $$;+create or replace function trigger_notice_ab() returns trigger as $$+ begin+ raise notice 'trigger % on % % % for %: (a,b)=(%,%)',+ TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,+ NEW.a, NEW.b;+ if TG_LEVEL = 'ROW' then+ return NEW;+ end if;+ return null;+ end;+ $$ language plpgsql;+create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int)+ partition by range (b);+alter table parted_irreg_ancestor drop column fd,+ drop column fd2, drop column fd3;+create table parted_irreg (fd int, a int, fd2 int, b text)+ partition by range (b);+alter table parted_irreg drop column fd, drop column fd2;+alter table parted_irreg_ancestor attach partition parted_irreg+ for values from ('aaaa') to ('zzzz');+create table parted1_irreg (b text, fd int, a int);+alter table parted1_irreg drop column fd;+alter table parted_irreg attach partition parted1_irreg+ for values from ('aaaa') to ('bbbb');+create trigger parted_trig after insert on parted_irreg+ for each row execute procedure trigger_notice_ab();+create trigger parted_trig_two after insert on parted_irreg for each row+ when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab();+insert into parted_irreg values (1, 'aardvark');+NOTICE: aardvark <- woof!+NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig_two on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark)+insert into parted1_irreg values ('aardwolf', 2);+NOTICE: aardwolf <- woof!+NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aardwolf)+insert into parted_irreg_ancestor values ('aasvogel', 3);+NOTICE: aasvogel <- woof!+NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)+NOTICE: trigger parted_trig_two on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)+drop table parted_irreg_ancestor;+--+-- Constraint triggers and partitioned tables+create table parted_constr_ancestor (a int, b text)+ partition by range (b);+create table parted_constr (a int, b text)+ partition by range (b);+alter table parted_constr_ancestor attach partition parted_constr+ for values from ('aaaa') to ('zzzz');+create table parted1_constr (a int, b text);+alter table parted_constr attach partition parted1_constr+ for values from ('aaaa') to ('bbbb');+create constraint trigger parted_trig after insert on parted_constr_ancestor+ deferrable+ for each row execute procedure trigger_notice_ab();+create constraint trigger parted_trig_two after insert on parted_constr+ deferrable initially deferred+ for each row when (bark(new.b) AND new.a % 2 = 1)+ execute procedure trigger_notice_ab();+-- The immediate constraint is fired immediately; the WHEN clause of the+-- deferred constraint is also called immediately. The deferred constraint+-- is fired at commit time.+begin;+insert into parted_constr values (1, 'aardvark');+NOTICE: aardvark <- woof!+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+insert into parted1_constr values (2, 'aardwolf');+NOTICE: aardwolf <- woof!+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf)+insert into parted_constr_ancestor values (3, 'aasvogel');+NOTICE: aasvogel <- woof!+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+commit;+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+-- The WHEN clause is immediate, and both constraint triggers are fired at+-- commit time.+begin;+set constraints parted_trig deferred;+insert into parted_constr values (1, 'aardvark');+NOTICE: aardvark <- woof!+insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel');+NOTICE: aardwolf <- woof!+NOTICE: aasvogel <- woof!+commit;+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf)+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+drop table parted_constr_ancestor;+drop function bark, trigger_notice_ab();
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.sourceindex dbab8f159b..98dd4210e9 100644--- a/src/test/regress/input/constraints.source+++ b/src/test/regress/input/constraints.source@@ -394,6 +394,22 @@ SET CONSTRAINTS ALL IMMEDIATE; -- should fail
COMMIT;
+-- test deferrable UNIQUE with a partitioned table+CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i);+CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10);+CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30);+SELECT conname, conrelid::regclass FROM pg_constraint+ WHERE conname LIKE 'parted_uniq%' ORDER BY conname;+BEGIN;+INSERT INTO parted_uniq_tbl VALUES (1);+SAVEPOINT f;+INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation+ROLLBACK TO f;+SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;+INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit+COMMIT;+DROP TABLE parted_uniq_tbl;+
-- test a HOT update that invalidates the conflicting tuple.
-- the trigger should still fire and catch the violation
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.sourceindex bb75165cc2..a6a1df18e7 100644--- a/src/test/regress/output/constraints.source+++ b/src/test/regress/output/constraints.source@@ -547,6 +547,32 @@ SET CONSTRAINTS ALL IMMEDIATE; -- should fail
ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
DETAIL: Key (i)=(3) already exists.
COMMIT;
+-- test deferrable UNIQUE with a partitioned table+CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i);+CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10);+CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30);+SELECT conname, conrelid::regclass FROM pg_constraint+ WHERE conname LIKE 'parted_uniq%' ORDER BY conname;+ conname | conrelid +-------------------------+-------------------+ parted_uniq_tbl_1_i_key | parted_uniq_tbl_1+ parted_uniq_tbl_2_i_key | parted_uniq_tbl_2+ parted_uniq_tbl_i_key | parted_uniq_tbl+(3 rows)++BEGIN;+INSERT INTO parted_uniq_tbl VALUES (1);+SAVEPOINT f;+INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"+DETAIL: Key (i)=(1) already exists.+ROLLBACK TO f;+SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;+INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit+COMMIT;+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"+DETAIL: Key (i)=(1) already exists.+DROP TABLE parted_uniq_tbl;
-- test a HOT update that invalidates the conflicting tuple.
-- the trigger should still fire and catch the violation
BEGIN;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex 3354f4899f..c2dea0042b 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -1286,7 +1286,46 @@ drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each row execute procedure trigger_nothing();+drop table parted_trig;++--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart1; -- fail+drop trigger f on trigpart2; -- fail+drop trigger f on trigpart3; -- fail+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;++drop table trigpart;+drop function trigger_nothing();++--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1306,7 +1345,7 @@ create or replace function trigger_notice() returns trigger as $$
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1320,28 +1359,48 @@ create trigger trig_del_before before delete on parted_stmt_trig
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();++-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
@@ -1365,8 +1424,103 @@ copy parted_stmt_trig1(a) from stdin;
1
\.
+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50), (1500);+drop table parted_trig;++-- test irregular partitions (i.e., different column definitions),+-- including that the WHEN clause works+create function bark(text) returns bool language plpgsql immutable+ as $$ begin raise notice '% <- woof!', $1; return true; end; $$;+create or replace function trigger_notice_ab() returns trigger as $$+ begin+ raise notice 'trigger % on % % % for %: (a,b)=(%,%)',+ TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,+ NEW.a, NEW.b;+ if TG_LEVEL = 'ROW' then+ return NEW;+ end if;+ return null;+ end;+ $$ language plpgsql;+create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int)+ partition by range (b);+alter table parted_irreg_ancestor drop column fd,+ drop column fd2, drop column fd3;+create table parted_irreg (fd int, a int, fd2 int, b text)+ partition by range (b);+alter table parted_irreg drop column fd, drop column fd2;+alter table parted_irreg_ancestor attach partition parted_irreg+ for values from ('aaaa') to ('zzzz');+create table parted1_irreg (b text, fd int, a int);+alter table parted1_irreg drop column fd;+alter table parted_irreg attach partition parted1_irreg+ for values from ('aaaa') to ('bbbb');+create trigger parted_trig after insert on parted_irreg+ for each row execute procedure trigger_notice_ab();+create trigger parted_trig_two after insert on parted_irreg for each row+ when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab();+insert into parted_irreg values (1, 'aardvark');+insert into parted1_irreg values ('aardwolf', 2);+insert into parted_irreg_ancestor values ('aasvogel', 3);+drop table parted_irreg_ancestor;++--+-- Constraint triggers and partitioned tables+create table parted_constr_ancestor (a int, b text)+ partition by range (b);+create table parted_constr (a int, b text)+ partition by range (b);+alter table parted_constr_ancestor attach partition parted_constr+ for values from ('aaaa') to ('zzzz');+create table parted1_constr (a int, b text);+alter table parted_constr attach partition parted1_constr+ for values from ('aaaa') to ('bbbb');+create constraint trigger parted_trig after insert on parted_constr_ancestor+ deferrable+ for each row execute procedure trigger_notice_ab();+create constraint trigger parted_trig_two after insert on parted_constr+ deferrable initially deferred+ for each row when (bark(new.b) AND new.a % 2 = 1)+ execute procedure trigger_notice_ab();++-- The immediate constraint is fired immediately; the WHEN clause of the+-- deferred constraint is also called immediately. The deferred constraint+-- is fired at commit time.+begin;+insert into parted_constr values (1, 'aardvark');+insert into parted1_constr values (2, 'aardwolf');+insert into parted_constr_ancestor values (3, 'aasvogel');+commit;++-- The WHEN clause is immediate, and both constraint triggers are fired at+-- commit time.+begin;+set constraints parted_trig deferred;+insert into parted_constr values (1, 'aardvark');+insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel');+commit;+drop table parted_constr_ancestor;+drop function bark, trigger_notice_ab();+
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
--
2.11.0
From 0a6dd371f89f41ea64d4bf6cd989e1df6bc738c8 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:54:14 -0300
Subject: [PATCH v3 4/4] [WIP] Allow foreign key triggers on partitioned tables
---
src/backend/catalog/pg_constraint.c | 212 +++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 108 +++++++++++++--
src/backend/parser/parse_utilcmd.c | 12 --
src/backend/utils/adt/ri_triggers.c | 50 +++----
src/bin/pg_dump/pg_dump.c | 19 ++-
src/include/catalog/pg_constraint_fn.h | 2 +
src/include/commands/tablecmds.h | 4 +
src/test/regress/expected/alter_table.out | 117 +++++++++++++++-
src/test/regress/expected/create_table.out | 10 --
src/test/regress/expected/foreign_key.out | 110 +++++++++++++++
src/test/regress/sql/alter_table.sql | 57 +++++++-
src/test/regress/sql/create_table.sql | 8 --
src/test/regress/sql/foreign_key.sql | 63 +++++++++
13 files changed, 685 insertions(+), 87 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 8bf8c2f4a6..1cde1ac7c9 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -27,6 +27,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"
@@ -378,6 +379,217 @@ CreateConstraintEntry(const char *constraintName,
return conOid;
}
+/*+ * For each foreign key constraint in relation parentId, create a cloned+ * copy of it for relationId.+ *+ * 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.+ */+void+CloneForeignKeyConstraints(Oid parentId, Oid relationId)+{+ 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;+ 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);++ 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);++ /*+ * XXX Normal constraint creation can be invoked during ALTER and+ * so it needs ALTER TABLE's phase 3 checking. Current caller is just+ * CREATE TABLE .. PARTITION OF so we don't need it, but maybe for+ * ALTER TABLE .. ATTACH PARTITION we'll need it.+ */+ }+ systable_endscan(scan);++ pfree(attmap);++ 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.cindex f5fc0938a6..34cc62186e 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, bool recurse, bool recursing,+ LOCKMODE lockmode);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
bool recurse, bool recursing,
@@ -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
@@ -917,8 +917,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.
@@ -970,6 +970,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (parent->trigdesc != NULL)
CloneRowTriggersToPartition(RelationGetRelid(parent), relationId);
+ /* And foreign keys too */+ CloneForeignKeyConstraints(parentId, relationId);+
heap_close(parent, NoLock);
}
@@ -7010,7 +7013,8 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
RelationGetNamespace(rel),
NIL);
- address = ATAddForeignKeyConstraint(tab, rel, newConstraint,+ address = ATAddForeignKeyConstraint(wqueue, tab, rel,+ newConstraint, recurse, false,
lockmode);
break;
@@ -7165,8 +7169,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, bool recurse,+ bool recursing, LOCKMODE lockmode)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -7194,18 +7199,30 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
pkrel = heap_open(fkconstraint->old_pktable_oid, ShareRowExclusiveLock);
else
pkrel = heap_openrv(fkconstraint->pktable, ShareRowExclusiveLock);
+ /* XXX if pkrel is partitioned, do we need to recurse this? */
/*
* Validity checks (permission checks wait till we have the column
* 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",
@@ -7566,6 +7583,45 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
}
/*
+ * If this is a partitioned table, recurse to create the constraint on the+ * partitions also.+ */+ if (recurse)+ {+ List *children;+ ListCell *child;++ /* XXX why not find_all_inheritors? */+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);++ foreach(child, children)+ {+ Oid childrelid = lfirst_oid(child);+ Relation childrel;+ AlteredTableInfo *childtab;+ ObjectAddress childAddr;++ /* find_inheritance_children already got lock */+ childrel = heap_open(childrelid, NoLock);+ CheckTableNotInUse(childrel, "ALTER TABLE"); /* XXX do we need this? */++ /* Find or create work queue entry for this table */+ childtab = ATGetQueueEntry(wqueue, childrel);++ /* Recurse to child */+ childAddr =+ ATAddForeignKeyConstraint(wqueue, childtab, childrel,+ fkconstraint, recurse, true,+ lockmode);++ /* make sure they go away together, or not at all */+ recordDependencyOn(&childAddr, &address, DEPENDENCY_INTERNAL);++ heap_close(childrel, NoLock);+ }+ }++ /*
* Close pk table, but keep lock until we've committed.
*/
heap_close(pkrel, NoLock);
@@ -7827,8 +7883,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)
@@ -8479,7 +8535,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)
{
@@ -8609,6 +8665,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);+ }+ }
}
/*
@@ -14055,6 +14130,9 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
/* and triggers */
CloneRowTriggersToPartition(RelationGetRelid(rel), RelationGetRelid(attachrel));
+ /* and foreign key constrains */+ CloneForeignKeyConstraints(RelationGetRelid(rel), RelationGetRelid(attachrel));+
/*
* Generate partition constraint from the partition bound specification.
* If the parent itself is a partition, make sure to include its
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex 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.cindex 8faae1d069..b3dd174d28 100644--- a/src/backend/utils/adt/ri_triggers.c+++ b/src/backend/utils/adt/ri_triggers.c@@ -401,7 +401,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.
@@ -409,7 +409,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++)
{
@@ -537,7 +537,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.
@@ -545,7 +545,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++)
{
@@ -793,7 +793,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.
@@ -801,7 +801,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++)
@@ -951,14 +951,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++)
{
@@ -1122,7 +1122,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
@@ -1133,7 +1133,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++)
@@ -1342,7 +1342,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.
@@ -1351,7 +1351,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++)
@@ -1559,7 +1559,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.
@@ -1568,7 +1568,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++)
@@ -1895,8 +1895,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:
@@ -1922,7 +1922,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.");
@@ -2345,22 +2345,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.cindex 566cbf2cda..a4a2205add 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -16370,18 +16370,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.hindex 06a2362003..acdc1eab04 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -57,6 +57,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conNoInherit,
bool is_internal);
+extern void 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.hindex 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.outindex ccd2c38dbc..b81acddf73 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -485,6 +485,119 @@ DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and 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);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+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_regular2 VALUES (1000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(1000) is not present in table "at_partitioned".+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);+INSERT INTO at_regular2 VALUES (42);+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_regular2+ Table "at_tst.at_regular2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ col3 | integer | | | +Foreign-key constraints:+ "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(col2)++\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)+Referenced by:+ TABLE "at_regular2" CONSTRAINT "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(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)++\d at_partitioned_1+ Table "at_tst.at_partitioned_1"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (10000) TO (20000)+Indexes:+ "at_partitioned_1_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)++\d at_partitioned_2+ Table "at_tst.at_partitioned_2"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ reg1_col1 | integer | | not null | + col2 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (20000) TO (30000)+Indexes:+ "at_partitioned_2_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);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(35000) is not present in table "at_partitioned".+-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;+-- disallowed: must drop it from parent instead+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 3 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 +3418,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.outindex 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.outindex fef072eddf..607cb398f3 100644--- a/src/test/regress/expected/foreign_key.out+++ b/src/test/regress/expected/foreign_key.out@@ -1415,3 +1415,113 @@ 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+--+-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f_drop1 int, f1 int primary key);+ALTER TABLE pkregular DROP COLUMN f_drop1;+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO fkpart1 VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+UPDATE pkregular SET f1 = 501;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ERROR: cannot drop inherited constraint "fkpartit_f1_fkey" of relation "fkpart1"+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)++ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE++DELETE FROM pkregular;+SELECT * FROM fkpartit;+ f1 +----+(0 rows)++CREATE TABLE fkpart2 (f_drop1 int, f_drop2 int, f1 int);+ALTER TABLE fkpart2 DROP COLUMN f_drop1, DROP COLUMN f_drop2;+ALTER TABLE fkpartit ATTACH PARTITION fkpart2 FOR VALUES FROM (1000) TO (2000);+\d fkpart2+ Table "public.fkpart2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (1000) TO (2000)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE++INSERT INTO fkpartit VALUES (1500); -- must fail: pk value is not there+ERROR: insert or update on table "fkpart2" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(1500) is not present in table "pkregular".+INSERT INTO pkregular VALUES (1500);+INSERT INTO fkpartit VALUES (1500); -- now it works+DROP TABLE fkpartit, pkregular;+-- Test that it's possible to have a FK from a regular table to a+-- partitioned one+CREATE TABLE pk_partit (f1 int) PARTITION BY RANGE (f1);+CREATE TABLE fk_regular (f1 int);+CREATE TABLE pkpartit1 PARTITION OF pk_partit FOR VALUES FROM (0) TO (1000);+ALTER TABLE pk_partit ADD PRIMARY KEY (f1);+ALTER TABLE fk_regular ADD FOREIGN KEY (f1) REFERENCES pk_partit;+CREATE TABLE pkpartit2 PARTITION OF pk_partit FOR VALUES FROM (1000) to (2000);+CREATE TABLE pkpartit3 (f0 int, f1 int NOT NULL);+ALTER TABLE pkpartit3 DROP COLUMN f0;+ALTER TABLE pk_partit ATTACH PARTITION pkpartit3 FOR VALUES FROM (2000) to (3000);+INSERT INTO fk_regular VALUES (500); -- must fail+ERROR: insert or update on table "fk_regular" violates foreign key constraint "fk_regular_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pk_partit".+INSERT INTO fk_regular VALUES (1500); -- must fail+ERROR: insert or update on table "fk_regular" violates foreign key constraint "fk_regular_f1_fkey"+DETAIL: Key (f1)=(1500) is not present in table "pk_partit".+INSERT INTO fk_regular VALUES (2500); -- must fail+ERROR: insert or update on table "fk_regular" violates foreign key constraint "fk_regular_f1_fkey"+DETAIL: Key (f1)=(2500) is not present in table "pk_partit".+INSERT INTO pk_partit VALUES (500), (1500), (2500);+INSERT INTO fk_regular VALUES (500); -- must work+INSERT INTO fk_regular VALUES (1500); -- must work+INSERT INTO fk_regular VALUES (2500); -- must workdiff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex b73f523e8a..f6dedf0287 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -394,6 +394,62 @@ DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and 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);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+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_regular2 VALUES (1000);+INSERT INTO at_partitioned VALUES (1000, 42);++-- these work:+INSERT INTO at_regular1 VALUES (1000);+INSERT INTO at_partitioned VALUES (42, 1000);+INSERT INTO at_regular2 VALUES (42);++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_regular2+\d at_partitioned+\d at_partitioned_0+\d at_partitioned_1+\d at_partitioned_2++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);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);++-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;++-- disallowed: must drop it from parent instead+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 +2091,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.sqlindex 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.sqlindex 5f19dad03c..6cdeb68497 100644--- a/src/test/regress/sql/foreign_key.sql+++ b/src/test/regress/sql/foreign_key.sql@@ -1055,3 +1055,66 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
commit;
drop table pktable2, fktable2;
+++--+-- Foreign keys and partitioned tables+--++-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f_drop1 int, f1 int primary key);+ALTER TABLE pkregular DROP COLUMN f_drop1;+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+UPDATE pkregular SET f1 = 501;++ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+\d fkpart1+ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+\d fkpart1+DELETE FROM pkregular;+SELECT * FROM fkpartit;++CREATE TABLE fkpart2 (f_drop1 int, f_drop2 int, f1 int);+ALTER TABLE fkpart2 DROP COLUMN f_drop1, DROP COLUMN f_drop2;+ALTER TABLE fkpartit ATTACH PARTITION fkpart2 FOR VALUES FROM (1000) TO (2000);+\d fkpart2+INSERT INTO fkpartit VALUES (1500); -- must fail: pk value is not there+INSERT INTO pkregular VALUES (1500);++INSERT INTO fkpartit VALUES (1500); -- now it works+DROP TABLE fkpartit, pkregular;++-- Test that it's possible to have a FK from a regular table to a+-- partitioned one++CREATE TABLE pk_partit (f1 int) PARTITION BY RANGE (f1);+CREATE TABLE fk_regular (f1 int);+CREATE TABLE pkpartit1 PARTITION OF pk_partit FOR VALUES FROM (0) TO (1000);+ALTER TABLE pk_partit ADD PRIMARY KEY (f1);+ALTER TABLE fk_regular ADD FOREIGN KEY (f1) REFERENCES pk_partit;+CREATE TABLE pkpartit2 PARTITION OF pk_partit FOR VALUES FROM (1000) to (2000);++CREATE TABLE pkpartit3 (f0 int, f1 int NOT NULL);+ALTER TABLE pkpartit3 DROP COLUMN f0;+ALTER TABLE pk_partit ATTACH PARTITION pkpartit3 FOR VALUES FROM (2000) to (3000);++INSERT INTO fk_regular VALUES (500); -- must fail+INSERT INTO fk_regular VALUES (1500); -- must fail+INSERT INTO fk_regular VALUES (2500); -- must fail++INSERT INTO pk_partit VALUES (500), (1500), (2500);++INSERT INTO fk_regular VALUES (500); -- must work+INSERT INTO fk_regular VALUES (1500); -- must work+INSERT INTO fk_regular VALUES (2500); -- must work
--
2.11.0
Attachments:
[text/plain] v3-0001-add-missing-CommandCounterIncrement-in-StoreParti.patch (876B, ../20180312024023.w2t37viip5wejz4y@alvherre.pgsql/2-v3-0001-add-missing-CommandCounterIncrement-in-StoreParti.patch)
download | inline diff:
From a34e786924b54d94dbf28c182aed27d0e92dba06 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:01:39 -0300
Subject: [PATCH v3 1/4] add missing CommandCounterIncrement in
StorePartitionBound
---
src/backend/catalog/heap.c | 3 +++
1 file changed, 3 insertions(+)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex cf36ce4add..2b5377bdf2 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -3299,6 +3299,9 @@ StorePartitionBound(Relation rel, Relation parent, PartitionBoundSpec *bound)
heap_freetuple(newtuple);
heap_close(classRel, RowExclusiveLock);
+ /* Make update visible */+ CommandCounterIncrement();+
/*
* The partition constraint for the default partition depends on the
* partition bounds of every other partition, so we must invalidate the
--
2.11.0
[text/plain] v3-0002-Add-missing-CommandCounterIncrement-in-partitione.patch (2.2K, ../20180312024023.w2t37viip5wejz4y@alvherre.pgsql/3-v3-0002-Add-missing-CommandCounterIncrement-in-partitione.patch)
download | inline diff:
From 1165c0438c627ea214de9ee4cffa83d89b0aa485 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:04:13 -0300
Subject: [PATCH v3 2/4] Add missing CommandCounterIncrement() in partitioned
index code
---
src/backend/catalog/pg_constraint.c | 4 ++++
src/backend/commands/indexcmds.c | 6 ++++++
src/backend/commands/tablecmds.c | 2 --
3 files changed, 10 insertions(+), 2 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 731c5e4317..38fdf72877 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -18,6 +18,7 @@
#include "access/heapam.h"
#include "access/htup_details.h"
#include "access/sysattr.h"
+#include "access/xact.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
@@ -781,6 +782,9 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
recordDependencyOn(&depender, &referenced, DEPENDENCY_INTERNAL_AUTO);
heap_close(constrRel, RowExclusiveLock);
++ /* make update visible */+ CommandCounterIncrement();
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.cindex 504806b25b..9ca632865b 100644--- a/src/backend/commands/indexcmds.c+++ b/src/backend/commands/indexcmds.c@@ -1003,6 +1003,9 @@ DefineIndex(Oid relationId,
ReleaseSysCache(tup);
heap_close(pg_index, RowExclusiveLock);
heap_freetuple(newtup);
++ /* make update visible */+ CommandCounterIncrement();
}
}
else
@@ -2512,5 +2515,8 @@ IndexSetParentIndex(Relation partitionIdx, Oid parentOid)
recordDependencyOn(&partIdx, &partitionTbl, DEPENDENCY_AUTO);
}
++ /* make our updates visible */+ CommandCounterIncrement();
}
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 74e020bffc..7ecfbc17a0 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -14571,8 +14571,6 @@ ATExecAttachPartitionIdx(List **wqueue, Relation parentIdx, RangeVar *name)
pfree(attmap);
- CommandCounterIncrement();-
validatePartitionedIndex(parentIdx, parentTbl);
}
--
2.11.0
[text/plain] v3-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch (59.5K, ../20180312024023.w2t37viip5wejz4y@alvherre.pgsql/4-v3-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch)
download | inline diff:
From ee640078fa9bd7662a28058b8b0affe49cdd336f Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH v3 3/4] Allow FOR EACH ROW triggers on partitioned tables
---
src/backend/catalog/heap.c | 1 +
src/backend/catalog/index.c | 4 +-
src/backend/catalog/pg_constraint.c | 3 +
src/backend/commands/tablecmds.c | 92 +++++++++-
src/backend/commands/trigger.c | 191 ++++++++++++++++++--
src/backend/commands/typecmds.c | 1 +
src/backend/tcop/utility.c | 3 +-
src/include/catalog/indexing.h | 2 +
src/include/catalog/pg_constraint.h | 39 ++--
src/include/catalog/pg_constraint_fn.h | 1 +
src/include/commands/trigger.h | 4 +-
src/test/regress/expected/triggers.out | 277 ++++++++++++++++++++++++++---
src/test/regress/input/constraints.source | 16 ++
src/test/regress/output/constraints.source | 26 +++
src/test/regress/sql/triggers.sql | 178 ++++++++++++++++--
15 files changed, 763 insertions(+), 75 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex 2b5377bdf2..0c97ff979b 100644--- a/src/backend/catalog/heap.c+++ b/src/backend/catalog/heap.c@@ -2117,6 +2117,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
false, /* Is Deferrable */
false, /* Is Deferred */
is_validated,
+ InvalidOid, /* no parent constraint */
RelationGetRelid(rel), /* relation */
attNos, /* attrs in the constraint */
keycount, /* # attrs in the constraint */
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 431bc31969..ca0a66753e 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -1283,6 +1283,7 @@ index_constraint_create(Relation heapRelation,
deferrable,
initdeferred,
true,
+ parentConstraintId,
RelationGetRelid(heapRelation),
indexInfo->ii_KeyAttrNumbers,
indexInfo->ii_NumIndexAttrs,
@@ -1361,7 +1362,8 @@ index_constraint_create(Relation heapRelation,
trigger->constrrel = NULL;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
- InvalidOid, conOid, indexRelationId, true);+ InvalidOid, conOid, indexRelationId, InvalidOid,+ InvalidOid, true, false);
}
/*
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 38fdf72877..8bf8c2f4a6 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -53,6 +53,7 @@ CreateConstraintEntry(const char *constraintName,
bool isDeferrable,
bool isDeferred,
bool isValidated,
+ Oid parentConstrId,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
@@ -171,6 +172,7 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId);
values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId);
values[Anum_pg_constraint_conindid - 1] = ObjectIdGetDatum(indexRelId);
+ values[Anum_pg_constraint_conparentid - 1] = ObjectIdGetDatum(parentConstrId);
values[Anum_pg_constraint_confrelid - 1] = ObjectIdGetDatum(foreignRelId);
values[Anum_pg_constraint_confupdtype - 1] = CharGetDatum(foreignUpdateType);
values[Anum_pg_constraint_confdeltype - 1] = CharGetDatum(foreignDeleteType);
@@ -773,6 +775,7 @@ ConstraintSetParentConstraint(Oid childConstrId, Oid parentConstrId)
constrForm = (Form_pg_constraint) GETSTRUCT(newtup);
constrForm->conislocal = false;
constrForm->coninhcount++;
+ constrForm->conparentid = parentConstrId;
CatalogTupleUpdate(constrRel, &tuple->t_self, newtup);
ReleaseSysCache(tuple);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 7ecfbc17a0..f5fc0938a6 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -487,6 +487,7 @@ static void ValidatePartitionConstraints(List **wqueue, Relation scanrel,
List *scanrel_children,
List *partConstraint,
bool validate_default);
+static void CloneRowTriggersToPartition(Oid parentId, Oid partitionId);
static ObjectAddress ATExecDetachPartition(Relation rel, RangeVar *name);
static ObjectAddress ATExecAttachPartitionIdx(List **wqueue, Relation rel,
RangeVar *name);
@@ -916,9 +917,11 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
/*
- * If we're creating a partition, create now all the indexes defined in- * the parent. We can't do it earlier, because DefineIndex wants to know- * the partition key which we just stored.+ * If we're creating a partition, create now all the indexes and triggers+ * defined in the parent.+ *+ * We can't do it earlier, because DefineIndex wants to know the partition+ * key which we just stored.
*/
if (stmt->partbound)
{
@@ -959,6 +962,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
list_free(idxlist);
++ /*+ * If there are any row-level triggers, clone them to the new+ * partition.+ */+ if (parent->trigdesc != NULL)+ CloneRowTriggersToPartition(RelationGetRelid(parent), relationId);+
heap_close(parent, NoLock);
}
@@ -7501,6 +7512,7 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
fkconstraint->deferrable,
fkconstraint->initdeferred,
fkconstraint->initially_valid,
+ InvalidOid, /* no parent constraint */
RelationGetRelid(rel),
fkattnum,
numfks,
@@ -8455,7 +8467,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8529,7 +8541,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -8584,7 +8596,7 @@ createForeignKeyTriggers(Relation rel, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->args = NIL;
(void) CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid, constraintOid,
- indexOid, true);+ indexOid, InvalidOid, InvalidOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -11124,7 +11136,7 @@ static void
ATExecEnableDisableTrigger(Relation rel, const char *trigname,
char fires_when, bool skip_system, LOCKMODE lockmode)
{
- EnableDisableTrigger(rel, trigname, fires_when, skip_system);+ EnableDisableTrigger(rel, trigname, fires_when, skip_system, lockmode);
}
/*
@@ -14040,6 +14052,9 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
/* Ensure there exists a correct set of indexes in the partition. */
AttachPartitionEnsureIndexes(rel, attachrel);
+ /* and triggers */+ CloneRowTriggersToPartition(RelationGetRelid(rel), RelationGetRelid(attachrel));+
/*
* Generate partition constraint from the partition bound specification.
* If the parent itself is a partition, make sure to include its
@@ -14256,6 +14271,69 @@ AttachPartitionEnsureIndexes(Relation rel, Relation attachrel)
}
/*
+ * CloneRowTriggersToPartition+ * subroutine for ATExecAttachPartition/DefineRelation to create row+ * triggers on partitions+ */+static void+CloneRowTriggersToPartition(Oid parentId, Oid partitionId)+{+ Relation pg_trigger;+ ScanKeyData key;+ SysScanDesc scan;+ HeapTuple tuple;++ ScanKeyInit(&key, Anum_pg_trigger_tgrelid, BTEqualStrategyNumber,+ F_OIDEQ, ObjectIdGetDatum(parentId));+ pg_trigger = heap_open(TriggerRelationId, RowExclusiveLock);+ scan = systable_beginscan(pg_trigger, TriggerRelidNameIndexId,+ true, NULL, 1, &key);++ while (HeapTupleIsValid(tuple = systable_getnext(scan)))+ {+ Form_pg_trigger trigForm;+ CreateTrigStmt *trigStmt;++ trigForm = (Form_pg_trigger) GETSTRUCT(tuple);++ /*+ * We only clone a) FOR EACH ROW triggers b) timed AFTER events, c)+ * that are not constraint triggers.+ */+ if (!TRIGGER_FOR_ROW(trigForm->tgtype) ||+ !TRIGGER_FOR_AFTER(trigForm->tgtype) ||+ OidIsValid(trigForm->tgconstraint))+ continue;++ trigStmt = makeNode(CreateTrigStmt);++ trigStmt->trigname = NameStr(trigForm->tgname);+ trigStmt->relation = NULL;+ trigStmt->funcname = NULL;+ trigStmt->args = NULL;+ trigStmt->row = true;+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;+ trigStmt->columns = NIL;+ trigStmt->whenClause = NULL;+ trigStmt->isconstraint = false;+ trigStmt->transitionRels = NIL;+ trigStmt->deferrable = trigForm->tgdeferrable;+ trigStmt->initdeferred = trigForm->tginitdeferred;+ trigStmt->constrrel = NULL;++ CreateTrigger(trigStmt, NULL, partitionId,+ InvalidOid, InvalidOid, InvalidOid,+ trigForm->tgfoid, HeapTupleGetOid(tuple), false, true);+ pfree(trigStmt);+ }++ systable_endscan(scan);++ heap_close(pg_trigger, RowExclusiveLock);+}++/*
* ALTER TABLE DETACH PARTITION
*
* Return the address of the relation that is no longer a partition of rel.
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.cindex fbd176b5d0..6a857df566 100644--- a/src/backend/commands/trigger.c+++ b/src/backend/commands/trigger.c@@ -20,6 +20,7 @@
#include "access/xact.h"
#include "catalog/catalog.h"
#include "catalog/dependency.h"
+#include "catalog/index.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
#include "catalog/pg_constraint.h"
@@ -125,6 +126,12 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* indexOid, if nonzero, is the OID of an index associated with the constraint.
* We do nothing with this except store it into pg_trigger.tgconstrindid.
*
+ * funcoid, if nonzero, is the OID of the function to invoke. When this is+ * given, stmt->funcname is ignored.+ *+ * parentTriggerOid, if nonzero, is a trigger that begets this one; so that+ * if that trigger is dropped, this one should be too.+ *
* If isInternal is true then this is an internally-generated trigger.
* This argument sets the tgisinternal field of the pg_trigger entry, and
* if true causes us to modify the given trigger name to ensure uniqueness.
@@ -133,6 +140,10 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
* relation, as well as ACL_EXECUTE on the trigger function. For internal
* triggers the caller must apply any required permission checks.
*
+ * When called on partitioned tables, this function recurses to create the+ * trigger on all the partitions, except if isInternal is true, in which+ * case caller is expected to execute recursion on its own.+ *
* Note: can return InvalidObjectAddress if we decided to not create a trigger
* at all, but a foreign-key constraint. This is a kluge for backwards
* compatibility.
@@ -140,7 +151,8 @@ static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal)+ Oid funcoid, Oid parentTriggerOid, bool isInternal,+ bool in_partition)
{
int16 tgtype;
int ncolumns;
@@ -159,7 +171,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Relation pgrel;
HeapTuple tuple;
Oid fargtypes[1]; /* dummy */
- Oid funcoid;
Oid funcrettype;
Oid trigoid;
char internaltrigname[NAMEDATALEN];
@@ -179,8 +190,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION ||- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ if (rel->rd_rel->relkind == RELKIND_RELATION)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -190,13 +200,49 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
- /* Disallow ROW triggers on partitioned tables */- if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ }+ else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ /* Partitioned tables can't have INSTEAD OF triggers */+ if (stmt->timing != TRIGGER_TYPE_BEFORE &&+ stmt->timing != TRIGGER_TYPE_AFTER)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is a partitioned table",+ errmsg("\"%s\" is a table",
RelationGetRelationName(rel)),
- errdetail("Partitioned tables cannot have ROW triggers.")));+ errdetail("Tables cannot have INSTEAD OF triggers.")));+ /*+ * FOR EACH ROW triggers have further restrictions+ */+ if (stmt->row)+ {+ /*+ * BEFORE triggers FOR EACH ROW are forbidden, because they would+ * allow the user to direct the row to another partition, which+ * isn't implemented in the executor.+ */+ if (stmt->timing != TRIGGER_TYPE_AFTER)+ ereport(ERROR,+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));+++ /*+ * Disallow use of transition tables. If this partitioned table+ * has any partitions, the error would occur below; but if it+ * doesn't then we would only hit that code when the first CREATE+ * TABLE ... PARTITION OF is executed, which is too late. Check+ * early to avoid the problem.+ */+ if (stmt->transitionRels != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("\"%s\" is a partitioned table",+ RelationGetRelationName(rel)),+ errdetail("Triggers on partitioned tables cannot have transition tables.")));+ }
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
{
@@ -587,7 +633,8 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Find and validate the trigger function.
*/
- funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);+ if (!OidIsValid(funcoid))+ funcoid = LookupFuncName(stmt->funcname, 0, fargtypes, false);
if (!isInternal)
{
aclresult = pg_proc_aclcheck(funcoid, GetUserId(), ACL_EXECUTE);
@@ -651,6 +698,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
stmt->deferrable,
stmt->initdeferred,
true,
+ InvalidOid, /* no parent */
RelationGetRelid(rel),
NULL, /* no conkey */
0,
@@ -733,6 +781,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
/*
* Build the new pg_trigger tuple.
+ *+ * When we're creating a trigger in a partition, we mark it as internal,+ * even though we don't do the isInternal magic in this function. This+ * makes the triggers in partitions identical to the ones in the+ * partitioned tables, except that they are marked internal.
*/
memset(nulls, false, sizeof(nulls));
@@ -742,7 +795,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid);
values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype);
values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN);
- values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);+ values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition);
values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid);
values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid);
values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid);
@@ -928,11 +981,18 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* User CREATE TRIGGER, so place dependencies. We make trigger be
* auto-dropped if its relation is dropped or if the FK relation is
* dropped. (Auto drop is compatible with our pre-7.3 behavior.)
+ *+ * Exception: if this trigger comes from a parent partitioned table,+ * then it's not separately drop-able, but goes away if the partition+ * does.
*/
referenced.classId = RelationRelationId;
referenced.objectId = RelationGetRelid(rel);
referenced.objectSubId = 0;
- recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);+ recordDependencyOn(&myself, &referenced, OidIsValid(parentTriggerOid) ?+ DEPENDENCY_INTERNAL_AUTO :+ DEPENDENCY_AUTO);+
if (OidIsValid(constrrelid))
{
referenced.classId = RelationRelationId;
@@ -954,6 +1014,13 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
referenced.objectSubId = 0;
recordDependencyOn(&referenced, &myself, DEPENDENCY_INTERNAL);
}
++ /* Depends on the parent trigger, if there is one. */+ if (OidIsValid(parentTriggerOid))+ {+ ObjectAddressSet(referenced, TriggerRelationId, parentTriggerOid);+ recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL_AUTO);+ }
}
/* If column-specific trigger, add normal dependencies on columns */
@@ -982,6 +1049,56 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
isInternal);
+ /*+ * If this is a non-internal FOR EACH ROW trigger on a partitioned table,+ * recurse for each partition.+ */+ if (!isInternal && stmt->row &&+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ {+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);+ List *idxs = NIL;+ List *childrenTbls = NIL;+ ListCell *l;+ int i;++ if (OidIsValid(indexOid))+ {+ idxs = find_inheritance_children(indexOid, AccessShareLock);+ foreach(l, idxs)+ childrenTbls = lappend_oid(childrenTbls,+ IndexGetRelation(lfirst_oid(l),+ false));+ }++ for (i = 0; i < partdesc->nparts; i++)+ {+ Oid indexOnChild = InvalidOid;+ ListCell *l2;++ /* Find which of the child indexes is the one on this partition */+ if (OidIsValid(indexOid))+ {+ forboth(l, idxs, l2, childrenTbls)+ {+ if (lfirst_oid(l2) == partdesc->oids[i])+ {+ indexOnChild = lfirst_oid(l);+ break;+ }+ }+ if (!OidIsValid(indexOnChild))+ elog(ERROR, "failed to find index matching index \"%s\" in partition \"%s\"",+ get_rel_name(indexOid), get_rel_name(partdesc->oids[i]));+ }++ CreateTrigger(copyObject(stmt), queryString,+ partdesc->oids[i], refRelOid,+ constraintOid, indexOnChild,+ InvalidOid, trigoid, isInternal, true);+ }+ }+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
@@ -1579,7 +1696,7 @@ renametrig(RenameStmt *stmt)
*/
void
EnableDisableTrigger(Relation rel, const char *tgname,
- char fires_when, bool skip_system)+ char fires_when, bool skip_system, LOCKMODE lockmode)
{
Relation tgrel;
int nkeys;
@@ -1642,6 +1759,27 @@ EnableDisableTrigger(Relation rel, const char *tgname,
heap_freetuple(newtup);
+ /*+ * When altering FOR EACH ROW triggers on a partitioned table,+ * do the same on the partitions as well.+ */+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ (TRIGGER_FOR_ROW(oldtrig->tgtype)))+ {+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);+ int i;++ for (i = 0; i < partdesc->nparts; i++)+ {+ Relation part;++ part = relation_open(partdesc->oids[i], lockmode);+ EnableDisableTrigger(part, NameStr(oldtrig->tgname),+ fires_when, skip_system, lockmode);+ heap_close(part, NoLock); /* keep lock till commit */+ }+ }+
changed = true;
}
@@ -5123,6 +5261,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
* constraints within the first search-path schema that has any
* matches, but disregard matches in schemas beyond the first match.
* (This is a bit odd but it's the historical behavior.)
+ *+ * A constraint in a partitioned table may have corresponding+ * constraints in the partitions. Grab those too.
*/
conrel = heap_open(ConstraintRelationId, AccessShareLock);
@@ -5217,6 +5358,32 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
constraint->relname)));
}
+ /*+ * Scan for any possible descendants of the constraints. We append+ * whatever we find to the same list that we're scanning; this has the+ * effect that we create new scans for those, too, so if there are+ * further descendents, we'll also catch them.+ */+ foreach(lc, conoidlist)+ {+ Oid parent = lfirst_oid(lc);+ ScanKeyData key;+ SysScanDesc scan;+ HeapTuple tuple;++ ScanKeyInit(&key,+ Anum_pg_constraint_conparentid,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(parent));++ scan = systable_beginscan(conrel, ConstraintParentIndexId, true, NULL, 1, &key);++ while (HeapTupleIsValid(tuple = systable_getnext(scan)))+ conoidlist = lappend_oid(conoidlist, HeapTupleGetOid(tuple));++ systable_endscan(scan);+ }+
heap_close(conrel, AccessShareLock);
/*
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.cindex bf3cd3a454..fa3c7099d2 100644--- a/src/backend/commands/typecmds.c+++ b/src/backend/commands/typecmds.c@@ -3153,6 +3153,7 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
false, /* Is Deferrable */
false, /* Is Deferred */
!constr->skip_validation, /* Is Validated */
+ InvalidOid, /* no parent constraint */
InvalidOid, /* not a relation constraint */
NULL,
0,
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.cindex f78efdf359..6df4211280 100644--- a/src/backend/tcop/utility.c+++ b/src/backend/tcop/utility.c@@ -1507,7 +1507,8 @@ ProcessUtilitySlow(ParseState *pstate,
case T_CreateTrigStmt:
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, false);+ InvalidOid, InvalidOid, InvalidOid,+ InvalidOid, false, false);
break;
case T_CreatePLangStmt:
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.hindex 0bb875441e..7dd9d108d6 100644--- a/src/include/catalog/indexing.h+++ b/src/include/catalog/indexing.h@@ -128,6 +128,8 @@ DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(c
#define ConstraintTypidIndexId 2666
DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
#define ConstraintOidIndexId 2667
+DECLARE_INDEX(pg_constraint_conparentid_index, 2579, on pg_constraint using btree(conparentid oid_ops));+#define ConstraintParentIndexId 2579
DECLARE_UNIQUE_INDEX(pg_conversion_default_index, 2668, on pg_conversion using btree(connamespace oid_ops, conforencoding int4_ops, contoencoding int4_ops, oid oid_ops));
#define ConversionDefaultIndexId 2668
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.hindex 8fca86d71e..45b26cdfa8 100644--- a/src/include/catalog/pg_constraint.h+++ b/src/include/catalog/pg_constraint.h@@ -73,6 +73,12 @@ CATALOG(pg_constraint,2606)
Oid conindid; /* index supporting this constraint */
/*
+ * if this constraint is on a partition inherited from a partitioned table,+ * this is the OID of the corresponding constraint in the parent.+ */+ Oid conparentid;++ /*
* These fields, plus confkey, are only meaningful for a foreign-key
* constraint. Otherwise confrelid is 0 and the char fields are spaces.
*/
@@ -150,7 +156,7 @@ typedef FormData_pg_constraint *Form_pg_constraint;
* compiler constants for pg_constraint
* ----------------
*/
-#define Natts_pg_constraint 24+#define Natts_pg_constraint 25
#define Anum_pg_constraint_conname 1
#define Anum_pg_constraint_connamespace 2
#define Anum_pg_constraint_contype 3
@@ -160,21 +166,22 @@ typedef FormData_pg_constraint *Form_pg_constraint;
#define Anum_pg_constraint_conrelid 7
#define Anum_pg_constraint_contypid 8
#define Anum_pg_constraint_conindid 9
-#define Anum_pg_constraint_confrelid 10-#define Anum_pg_constraint_confupdtype 11-#define Anum_pg_constraint_confdeltype 12-#define Anum_pg_constraint_confmatchtype 13-#define Anum_pg_constraint_conislocal 14-#define Anum_pg_constraint_coninhcount 15-#define Anum_pg_constraint_connoinherit 16-#define Anum_pg_constraint_conkey 17-#define Anum_pg_constraint_confkey 18-#define Anum_pg_constraint_conpfeqop 19-#define Anum_pg_constraint_conppeqop 20-#define Anum_pg_constraint_conffeqop 21-#define Anum_pg_constraint_conexclop 22-#define Anum_pg_constraint_conbin 23-#define Anum_pg_constraint_consrc 24+#define Anum_pg_constraint_conparentid 10+#define Anum_pg_constraint_confrelid 11+#define Anum_pg_constraint_confupdtype 12+#define Anum_pg_constraint_confdeltype 13+#define Anum_pg_constraint_confmatchtype 14+#define Anum_pg_constraint_conislocal 15+#define Anum_pg_constraint_coninhcount 16+#define Anum_pg_constraint_connoinherit 17+#define Anum_pg_constraint_conkey 18+#define Anum_pg_constraint_confkey 19+#define Anum_pg_constraint_conpfeqop 20+#define Anum_pg_constraint_conppeqop 21+#define Anum_pg_constraint_conffeqop 22+#define Anum_pg_constraint_conexclop 23+#define Anum_pg_constraint_conbin 24+#define Anum_pg_constraint_consrc 25
/* ----------------
* initial contents of pg_constraint
diff --git a/src/include/catalog/pg_constraint_fn.h b/src/include/catalog/pg_constraint_fn.hindex d3351f4a83..06a2362003 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -33,6 +33,7 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool isDeferrable,
bool isDeferred,
bool isValidated,
+ Oid parentConstrId,
Oid relId,
const int16 *constraintKey,
int constraintNKeys,
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.hindex ff5546cf28..fac450e967 100644--- a/src/include/commands/trigger.h+++ b/src/include/commands/trigger.h@@ -159,7 +159,7 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- bool isInternal);+ Oid funcid, Oid parentTriggerOid, bool isInternal, bool recursing);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
@@ -167,7 +167,7 @@ extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
extern ObjectAddress renametrig(RenameStmt *stmt);
extern void EnableDisableTrigger(Relation rel, const char *tgname,
- char fires_when, bool skip_system);+ char fires_when, bool skip_system, LOCKMODE lockmode);
extern void RelationBuildTriggers(Relation relation);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.outindex 99be9ac6e9..521bca7f01 100644--- a/src/test/regress/expected/triggers.out+++ b/src/test/regress/expected/triggers.out@@ -1847,7 +1847,74 @@ drop function my_trigger_function();
drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a table+DETAIL: Tables cannot have INSTEAD OF triggers.+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each row execute procedure trigger_nothing();+ERROR: "parted_trig" is a partitioned table+DETAIL: Triggers on partitioned tables cannot have transition tables.+drop table parted_trig;+--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart2 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(4 rows)++drop trigger f on trigpart1; -- fail+ERROR: cannot drop trigger f on table trigpart1 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart2; -- fail+ERROR: cannot drop trigger f on table trigpart2 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop trigger f on trigpart3; -- fail+ERROR: cannot drop trigger f on table trigpart3 because trigger f on table trigpart requires it+HINT: You can drop trigger f on table trigpart instead.+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +-----------+--------+-----------------+ trigpart | f | trigger_nothing+ trigpart1 | f | trigger_nothing+ trigpart3 | f | trigger_nothing+(3 rows)++drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+ tgrelid | tgname | tgfoid +---------+--------+--------+(0 rows)++drop table trigpart;+drop function trigger_nothing();+--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1864,7 +1931,7 @@ create or replace function trigger_notice() returns trigger as $$
return null;
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1877,36 +1944,62 @@ create trigger trig_del_before before delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+ERROR: "parted_stmt_trig" is a partitioned table+DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.+-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
insert into parted2_stmt_trig values (1), (2) returning a
) insert into parted_stmt_trig select a from ins returning tableoid::regclass, a;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted2_stmt_trig BEFORE INSERT for STATEMENT-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW-NOTICE: trigger trig_ins_after on parted2_stmt_trig AFTER INSERT for STATEMENT+NOTICE: trigger trig_ins_before_3 on parted2_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_3 on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
-------------------+---
@@ -1918,25 +2011,161 @@ with upd as (
update parted2_stmt_trig set a = a
) update parted_stmt_trig set a = a;
NOTICE: trigger trig_upd_before on parted_stmt_trig BEFORE UPDATE for STATEMENT
-NOTICE: trigger trig_upd_before on parted_stmt_trig1 BEFORE UPDATE for ROW-NOTICE: trigger trig_upd_before on parted2_stmt_trig BEFORE UPDATE for STATEMENT-NOTICE: trigger trig_upd_after on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_before_child on parted_stmt_trig1 BEFORE UPDATE for ROW+NOTICE: trigger trig_upd_before_3 on parted2_stmt_trig BEFORE UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_child on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig1 AFTER UPDATE for ROW+NOTICE: trigger trig_upd_after_parent on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger trig_upd_after on parted_stmt_trig AFTER UPDATE for STATEMENT
-NOTICE: trigger trig_upd_after on parted2_stmt_trig AFTER UPDATE for STATEMENT+NOTICE: trigger trig_upd_after_3 on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger trig_del_before on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger trig_del_before_child on parted_stmt_trig1 BEFORE DELETE for ROW+NOTICE: trigger trig_del_after_parent on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger trig_del_after on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
-NOTICE: trigger trig_ins_before on parted_stmt_trig1 BEFORE INSERT for ROW-NOTICE: trigger trig_ins_after on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+NOTICE: trigger trig_ins_before on parted_stmt_trig BEFORE INSERT for STATEMENT+NOTICE: trigger trig_ins_before_child on parted_stmt_trig1 BEFORE INSERT for ROW+NOTICE: trigger trig_ins_after_child on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after_parent on parted_stmt_trig1 AFTER INSERT for ROW+NOTICE: trigger trig_ins_after on parted_stmt_trig AFTER INSERT for STATEMENT
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50), (1500);+NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW+NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW+NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW+drop table parted_trig;+-- test irregular partitions (i.e., different column definitions),+-- including that the WHEN clause works+create function bark(text) returns bool language plpgsql immutable+ as $$ begin raise notice '% <- woof!', $1; return true; end; $$;+create or replace function trigger_notice_ab() returns trigger as $$+ begin+ raise notice 'trigger % on % % % for %: (a,b)=(%,%)',+ TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,+ NEW.a, NEW.b;+ if TG_LEVEL = 'ROW' then+ return NEW;+ end if;+ return null;+ end;+ $$ language plpgsql;+create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int)+ partition by range (b);+alter table parted_irreg_ancestor drop column fd,+ drop column fd2, drop column fd3;+create table parted_irreg (fd int, a int, fd2 int, b text)+ partition by range (b);+alter table parted_irreg drop column fd, drop column fd2;+alter table parted_irreg_ancestor attach partition parted_irreg+ for values from ('aaaa') to ('zzzz');+create table parted1_irreg (b text, fd int, a int);+alter table parted1_irreg drop column fd;+alter table parted_irreg attach partition parted1_irreg+ for values from ('aaaa') to ('bbbb');+create trigger parted_trig after insert on parted_irreg+ for each row execute procedure trigger_notice_ab();+create trigger parted_trig_two after insert on parted_irreg for each row+ when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab();+insert into parted_irreg values (1, 'aardvark');+NOTICE: aardvark <- woof!+NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig_two on parted1_irreg AFTER INSERT for ROW: (a,b)=(1,aardvark)+insert into parted1_irreg values ('aardwolf', 2);+NOTICE: aardwolf <- woof!+NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(2,aardwolf)+insert into parted_irreg_ancestor values ('aasvogel', 3);+NOTICE: aasvogel <- woof!+NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)+NOTICE: trigger parted_trig_two on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)+drop table parted_irreg_ancestor;+--+-- Constraint triggers and partitioned tables+create table parted_constr_ancestor (a int, b text)+ partition by range (b);+create table parted_constr (a int, b text)+ partition by range (b);+alter table parted_constr_ancestor attach partition parted_constr+ for values from ('aaaa') to ('zzzz');+create table parted1_constr (a int, b text);+alter table parted_constr attach partition parted1_constr+ for values from ('aaaa') to ('bbbb');+create constraint trigger parted_trig after insert on parted_constr_ancestor+ deferrable+ for each row execute procedure trigger_notice_ab();+create constraint trigger parted_trig_two after insert on parted_constr+ deferrable initially deferred+ for each row when (bark(new.b) AND new.a % 2 = 1)+ execute procedure trigger_notice_ab();+-- The immediate constraint is fired immediately; the WHEN clause of the+-- deferred constraint is also called immediately. The deferred constraint+-- is fired at commit time.+begin;+insert into parted_constr values (1, 'aardvark');+NOTICE: aardvark <- woof!+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+insert into parted1_constr values (2, 'aardwolf');+NOTICE: aardwolf <- woof!+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf)+insert into parted_constr_ancestor values (3, 'aasvogel');+NOTICE: aasvogel <- woof!+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+commit;+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+-- The WHEN clause is immediate, and both constraint triggers are fired at+-- commit time.+begin;+set constraints parted_trig deferred;+insert into parted_constr values (1, 'aardvark');+NOTICE: aardvark <- woof!+insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel');+NOTICE: aardwolf <- woof!+NOTICE: aasvogel <- woof!+commit;+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(1,aardvark)+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(2,aardwolf)+NOTICE: trigger parted_trig on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+NOTICE: trigger parted_trig_two on parted1_constr AFTER INSERT for ROW: (a,b)=(3,aasvogel)+drop table parted_constr_ancestor;+drop function bark, trigger_notice_ab();
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.sourceindex dbab8f159b..98dd4210e9 100644--- a/src/test/regress/input/constraints.source+++ b/src/test/regress/input/constraints.source@@ -394,6 +394,22 @@ SET CONSTRAINTS ALL IMMEDIATE; -- should fail
COMMIT;
+-- test deferrable UNIQUE with a partitioned table+CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i);+CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10);+CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30);+SELECT conname, conrelid::regclass FROM pg_constraint+ WHERE conname LIKE 'parted_uniq%' ORDER BY conname;+BEGIN;+INSERT INTO parted_uniq_tbl VALUES (1);+SAVEPOINT f;+INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation+ROLLBACK TO f;+SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;+INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit+COMMIT;+DROP TABLE parted_uniq_tbl;+
-- test a HOT update that invalidates the conflicting tuple.
-- the trigger should still fire and catch the violation
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.sourceindex bb75165cc2..a6a1df18e7 100644--- a/src/test/regress/output/constraints.source+++ b/src/test/regress/output/constraints.source@@ -547,6 +547,32 @@ SET CONSTRAINTS ALL IMMEDIATE; -- should fail
ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
DETAIL: Key (i)=(3) already exists.
COMMIT;
+-- test deferrable UNIQUE with a partitioned table+CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i);+CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10);+CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30);+SELECT conname, conrelid::regclass FROM pg_constraint+ WHERE conname LIKE 'parted_uniq%' ORDER BY conname;+ conname | conrelid +-------------------------+-------------------+ parted_uniq_tbl_1_i_key | parted_uniq_tbl_1+ parted_uniq_tbl_2_i_key | parted_uniq_tbl_2+ parted_uniq_tbl_i_key | parted_uniq_tbl+(3 rows)++BEGIN;+INSERT INTO parted_uniq_tbl VALUES (1);+SAVEPOINT f;+INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"+DETAIL: Key (i)=(1) already exists.+ROLLBACK TO f;+SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;+INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit+COMMIT;+ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key"+DETAIL: Key (i)=(1) already exists.+DROP TABLE parted_uniq_tbl;
-- test a HOT update that invalidates the conflicting tuple.
-- the trigger should still fire and catch the violation
BEGIN;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sqlindex 3354f4899f..c2dea0042b 100644--- a/src/test/regress/sql/triggers.sql+++ b/src/test/regress/sql/triggers.sql@@ -1286,7 +1286,46 @@ drop view my_view;
drop table my_table;
--
--- Verify that per-statement triggers are fired for partitioned tables+-- Verify cases that are unsupported with partitioned tables+--+create table parted_trig (a int) partition by list (a);+create function trigger_nothing() returns trigger+ language plpgsql as $$ begin end; $$;+create trigger failed before insert or update or delete on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed instead of update on parted_trig+ for each row execute procedure trigger_nothing();+create trigger failed after update on parted_trig+ referencing old table as old_table+ for each row execute procedure trigger_nothing();+drop table parted_trig;++--+-- Verify trigger creation for partitioned tables, and drop behavior+--+create table trigpart (a int, b int) partition by range (a);+create table trigpart1 partition of trigpart for values from (0) to (1000);+create trigger f after insert on trigpart for each row execute procedure trigger_nothing();+create table trigpart2 partition of trigpart for values from (1000) to (2000);+create table trigpart3 (like trigpart);+alter table trigpart attach partition trigpart3 for values from (2000) to (3000);+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart1; -- fail+drop trigger f on trigpart2; -- fail+drop trigger f on trigpart3; -- fail+drop table trigpart2; -- ok, trigger should be gone in that partition+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;+drop trigger f on trigpart; -- ok, all gone+select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger+ where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text;++drop table trigpart;+drop function trigger_nothing();++--+-- Verify that triggers are fired for partitioned tables
--
create table parted_stmt_trig (a int) partition by list (a);
create table parted_stmt_trig1 partition of parted_stmt_trig for values in (1);
@@ -1306,7 +1345,7 @@ create or replace function trigger_notice() returns trigger as $$
end;
$$ language plpgsql;
--- insert/update/delete statment-level triggers on the parent+-- insert/update/delete statement-level triggers on the parent
create trigger trig_ins_before before insert on parted_stmt_trig
for each statement execute procedure trigger_notice();
create trigger trig_ins_after after insert on parted_stmt_trig
@@ -1320,28 +1359,48 @@ create trigger trig_del_before before delete on parted_stmt_trig
create trigger trig_del_after after delete on parted_stmt_trig
for each statement execute procedure trigger_notice();
+-- these cases are disallowed+create trigger trig_ins_before_1 before insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_before_1 before update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_before_1 before delete on parted_stmt_trig+ for each row execute procedure trigger_notice();++-- insert/update/delete row-level triggers on the parent+create trigger trig_ins_after_parent after insert on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_upd_after_parent after update on parted_stmt_trig+ for each row execute procedure trigger_notice();+create trigger trig_del_after_parent after delete on parted_stmt_trig+ for each row execute procedure trigger_notice();+
-- insert/update/delete row-level triggers on the first partition
-create trigger trig_ins_before before insert on parted_stmt_trig1+create trigger trig_ins_before_child before insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted_stmt_trig1+create trigger trig_ins_after_child after insert on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted_stmt_trig1+create trigger trig_upd_before_child before update on parted_stmt_trig1
for each row execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted_stmt_trig1+create trigger trig_upd_after_child after update on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_before_child before delete on parted_stmt_trig1+ for each row execute procedure trigger_notice();+create trigger trig_del_after_child after delete on parted_stmt_trig1
for each row execute procedure trigger_notice();
-- insert/update/delete statement-level triggers on the parent
-create trigger trig_ins_before before insert on parted2_stmt_trig+create trigger trig_ins_before_3 before insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_ins_after after insert on parted2_stmt_trig+create trigger trig_ins_after_3 after insert on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_before before update on parted2_stmt_trig+create trigger trig_upd_before_3 before update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_upd_after after update on parted2_stmt_trig+create trigger trig_upd_after_3 after update on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_before before delete on parted2_stmt_trig+create trigger trig_del_before_3 before delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
-create trigger trig_del_after after delete on parted2_stmt_trig+create trigger trig_del_after_3 after delete on parted2_stmt_trig
for each statement execute procedure trigger_notice();
with ins (a) as (
@@ -1365,8 +1424,103 @@ copy parted_stmt_trig1(a) from stdin;
1
\.
+-- Disabling a trigger in the parent table should disable children triggers too+alter table parted_stmt_trig disable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+alter table parted_stmt_trig enable trigger trig_ins_after_parent;+insert into parted_stmt_trig values (1);+
drop table parted_stmt_trig, parted2_stmt_trig;
+-- Verify that triggers fire in alphabetical order+create table parted_trig (a int) partition by range (a);+create table parted_trig_1 partition of parted_trig for values from (0) to (1000)+ partition by range (a);+create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);+create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);+create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();+create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();+create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();+create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();+insert into parted_trig values (50), (1500);+drop table parted_trig;++-- test irregular partitions (i.e., different column definitions),+-- including that the WHEN clause works+create function bark(text) returns bool language plpgsql immutable+ as $$ begin raise notice '% <- woof!', $1; return true; end; $$;+create or replace function trigger_notice_ab() returns trigger as $$+ begin+ raise notice 'trigger % on % % % for %: (a,b)=(%,%)',+ TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,+ NEW.a, NEW.b;+ if TG_LEVEL = 'ROW' then+ return NEW;+ end if;+ return null;+ end;+ $$ language plpgsql;+create table parted_irreg_ancestor (fd text, b text, fd2 int, fd3 int, a int)+ partition by range (b);+alter table parted_irreg_ancestor drop column fd,+ drop column fd2, drop column fd3;+create table parted_irreg (fd int, a int, fd2 int, b text)+ partition by range (b);+alter table parted_irreg drop column fd, drop column fd2;+alter table parted_irreg_ancestor attach partition parted_irreg+ for values from ('aaaa') to ('zzzz');+create table parted1_irreg (b text, fd int, a int);+alter table parted1_irreg drop column fd;+alter table parted_irreg attach partition parted1_irreg+ for values from ('aaaa') to ('bbbb');+create trigger parted_trig after insert on parted_irreg+ for each row execute procedure trigger_notice_ab();+create trigger parted_trig_two after insert on parted_irreg for each row+ when (bark(new.b) AND new.a % 2 = 1) execute procedure trigger_notice_ab();+insert into parted_irreg values (1, 'aardvark');+insert into parted1_irreg values ('aardwolf', 2);+insert into parted_irreg_ancestor values ('aasvogel', 3);+drop table parted_irreg_ancestor;++--+-- Constraint triggers and partitioned tables+create table parted_constr_ancestor (a int, b text)+ partition by range (b);+create table parted_constr (a int, b text)+ partition by range (b);+alter table parted_constr_ancestor attach partition parted_constr+ for values from ('aaaa') to ('zzzz');+create table parted1_constr (a int, b text);+alter table parted_constr attach partition parted1_constr+ for values from ('aaaa') to ('bbbb');+create constraint trigger parted_trig after insert on parted_constr_ancestor+ deferrable+ for each row execute procedure trigger_notice_ab();+create constraint trigger parted_trig_two after insert on parted_constr+ deferrable initially deferred+ for each row when (bark(new.b) AND new.a % 2 = 1)+ execute procedure trigger_notice_ab();++-- The immediate constraint is fired immediately; the WHEN clause of the+-- deferred constraint is also called immediately. The deferred constraint+-- is fired at commit time.+begin;+insert into parted_constr values (1, 'aardvark');+insert into parted1_constr values (2, 'aardwolf');+insert into parted_constr_ancestor values (3, 'aasvogel');+commit;++-- The WHEN clause is immediate, and both constraint triggers are fired at+-- commit time.+begin;+set constraints parted_trig deferred;+insert into parted_constr values (1, 'aardvark');+insert into parted1_constr values (2, 'aardwolf'), (3, 'aasvogel');+commit;+drop table parted_constr_ancestor;+drop function bark, trigger_notice_ab();+
--
-- Test the interaction between transition tables and both kinds of
-- inheritance. We'll dump the contents of the transition tables in a
--
2.11.0
[text/plain] v3-0004-WIP-Allow-foreign-key-triggers-on-partitioned-tab.patch (45.1K, ../20180312024023.w2t37viip5wejz4y@alvherre.pgsql/5-v3-0004-WIP-Allow-foreign-key-triggers-on-partitioned-tab.patch)
download | inline diff:
From 0a6dd371f89f41ea64d4bf6cd989e1df6bc738c8 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:54:14 -0300
Subject: [PATCH v3 4/4] [WIP] Allow foreign key triggers on partitioned tables
---
src/backend/catalog/pg_constraint.c | 212 +++++++++++++++++++++++++++++
src/backend/commands/tablecmds.c | 108 +++++++++++++--
src/backend/parser/parse_utilcmd.c | 12 --
src/backend/utils/adt/ri_triggers.c | 50 +++----
src/bin/pg_dump/pg_dump.c | 19 ++-
src/include/catalog/pg_constraint_fn.h | 2 +
src/include/commands/tablecmds.h | 4 +
src/test/regress/expected/alter_table.out | 117 +++++++++++++++-
src/test/regress/expected/create_table.out | 10 --
src/test/regress/expected/foreign_key.out | 110 +++++++++++++++
src/test/regress/sql/alter_table.sql | 57 +++++++-
src/test/regress/sql/create_table.sql | 8 --
src/test/regress/sql/foreign_key.sql | 63 +++++++++
13 files changed, 685 insertions(+), 87 deletions(-)
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.cindex 8bf8c2f4a6..1cde1ac7c9 100644--- a/src/backend/catalog/pg_constraint.c+++ b/src/backend/catalog/pg_constraint.c@@ -27,6 +27,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"
@@ -378,6 +379,217 @@ CreateConstraintEntry(const char *constraintName,
return conOid;
}
+/*+ * For each foreign key constraint in relation parentId, create a cloned+ * copy of it for relationId.+ *+ * 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.+ */+void+CloneForeignKeyConstraints(Oid parentId, Oid relationId)+{+ 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;+ 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);++ 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);++ /*+ * XXX Normal constraint creation can be invoked during ALTER and+ * so it needs ALTER TABLE's phase 3 checking. Current caller is just+ * CREATE TABLE .. PARTITION OF so we don't need it, but maybe for+ * ALTER TABLE .. ATTACH PARTITION we'll need it.+ */+ }+ systable_endscan(scan);++ pfree(attmap);++ 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.cindex f5fc0938a6..34cc62186e 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, bool recurse, bool recursing,+ LOCKMODE lockmode);
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
bool recurse, bool recursing,
@@ -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
@@ -917,8 +917,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.
@@ -970,6 +970,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (parent->trigdesc != NULL)
CloneRowTriggersToPartition(RelationGetRelid(parent), relationId);
+ /* And foreign keys too */+ CloneForeignKeyConstraints(parentId, relationId);+
heap_close(parent, NoLock);
}
@@ -7010,7 +7013,8 @@ ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
RelationGetNamespace(rel),
NIL);
- address = ATAddForeignKeyConstraint(tab, rel, newConstraint,+ address = ATAddForeignKeyConstraint(wqueue, tab, rel,+ newConstraint, recurse, false,
lockmode);
break;
@@ -7165,8 +7169,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, bool recurse,+ bool recursing, LOCKMODE lockmode)
{
Relation pkrel;
int16 pkattnum[INDEX_MAX_KEYS];
@@ -7194,18 +7199,30 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
pkrel = heap_open(fkconstraint->old_pktable_oid, ShareRowExclusiveLock);
else
pkrel = heap_openrv(fkconstraint->pktable, ShareRowExclusiveLock);
+ /* XXX if pkrel is partitioned, do we need to recurse this? */
/*
* Validity checks (permission checks wait till we have the column
* 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",
@@ -7566,6 +7583,45 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
}
/*
+ * If this is a partitioned table, recurse to create the constraint on the+ * partitions also.+ */+ if (recurse)+ {+ List *children;+ ListCell *child;++ /* XXX why not find_all_inheritors? */+ children = find_inheritance_children(RelationGetRelid(rel), lockmode);++ foreach(child, children)+ {+ Oid childrelid = lfirst_oid(child);+ Relation childrel;+ AlteredTableInfo *childtab;+ ObjectAddress childAddr;++ /* find_inheritance_children already got lock */+ childrel = heap_open(childrelid, NoLock);+ CheckTableNotInUse(childrel, "ALTER TABLE"); /* XXX do we need this? */++ /* Find or create work queue entry for this table */+ childtab = ATGetQueueEntry(wqueue, childrel);++ /* Recurse to child */+ childAddr =+ ATAddForeignKeyConstraint(wqueue, childtab, childrel,+ fkconstraint, recurse, true,+ lockmode);++ /* make sure they go away together, or not at all */+ recordDependencyOn(&childAddr, &address, DEPENDENCY_INTERNAL);++ heap_close(childrel, NoLock);+ }+ }++ /*
* Close pk table, but keep lock until we've committed.
*/
heap_close(pkrel, NoLock);
@@ -7827,8 +7883,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)
@@ -8479,7 +8535,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)
{
@@ -8609,6 +8665,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);+ }+ }
}
/*
@@ -14055,6 +14130,9 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd)
/* and triggers */
CloneRowTriggersToPartition(RelationGetRelid(rel), RelationGetRelid(attachrel));
+ /* and foreign key constrains */+ CloneForeignKeyConstraints(RelationGetRelid(rel), RelationGetRelid(attachrel));+
/*
* Generate partition constraint from the partition bound specification.
* If the parent itself is a partition, make sure to include its
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.cindex 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.cindex 8faae1d069..b3dd174d28 100644--- a/src/backend/utils/adt/ri_triggers.c+++ b/src/backend/utils/adt/ri_triggers.c@@ -401,7 +401,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.
@@ -409,7 +409,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++)
{
@@ -537,7 +537,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.
@@ -545,7 +545,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++)
{
@@ -793,7 +793,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.
@@ -801,7 +801,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++)
@@ -951,14 +951,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++)
{
@@ -1122,7 +1122,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
@@ -1133,7 +1133,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++)
@@ -1342,7 +1342,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.
@@ -1351,7 +1351,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++)
@@ -1559,7 +1559,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.
@@ -1568,7 +1568,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++)
@@ -1895,8 +1895,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:
@@ -1922,7 +1922,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.");
@@ -2345,22 +2345,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.cindex 566cbf2cda..a4a2205add 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -16370,18 +16370,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.hindex 06a2362003..acdc1eab04 100644--- a/src/include/catalog/pg_constraint_fn.h+++ b/src/include/catalog/pg_constraint_fn.h@@ -57,6 +57,8 @@ extern Oid CreateConstraintEntry(const char *constraintName,
bool conNoInherit,
bool is_internal);
+extern void 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.hindex 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.outindex ccd2c38dbc..b81acddf73 100644--- a/src/test/regress/expected/alter_table.out+++ b/src/test/regress/expected/alter_table.out@@ -485,6 +485,119 @@ DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and 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);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+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_regular2 VALUES (1000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(1000) is not present in table "at_partitioned".+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);+INSERT INTO at_regular2 VALUES (42);+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_regular2+ Table "at_tst.at_regular2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ col3 | integer | | | +Foreign-key constraints:+ "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(col2)++\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)+Referenced by:+ TABLE "at_regular2" CONSTRAINT "at_regular2_col3_fkey" FOREIGN KEY (col3) REFERENCES at_partitioned(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)++\d at_partitioned_1+ Table "at_tst.at_partitioned_1"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ col2 | integer | | not null | + reg1_col1 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (10000) TO (20000)+Indexes:+ "at_partitioned_1_pkey" PRIMARY KEY, btree (col2)+Foreign-key constraints:+ "at_partitioned_reg1_col1_fkey" FOREIGN KEY (reg1_col1) REFERENCES at_regular1(col1)++\d at_partitioned_2+ Table "at_tst.at_partitioned_2"+ Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+---------+ reg1_col1 | integer | | not null | + col2 | integer | | not null | +Partition of: at_partitioned FOR VALUES FROM (20000) TO (30000)+Indexes:+ "at_partitioned_2_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);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);+ERROR: insert or update on table "at_regular2" violates foreign key constraint "at_regular2_col3_fkey"+DETAIL: Key (col3)=(35000) is not present in table "at_partitioned".+-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;+-- disallowed: must drop it from parent instead+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 3 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 +3418,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.outindex 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.outindex fef072eddf..607cb398f3 100644--- a/src/test/regress/expected/foreign_key.out+++ b/src/test/regress/expected/foreign_key.out@@ -1415,3 +1415,113 @@ 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+--+-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f_drop1 int, f1 int primary key);+ALTER TABLE pkregular DROP COLUMN f_drop1;+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO fkpart1 VALUES (500);+ERROR: insert or update on table "fkpart1" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pkregular".+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+UPDATE pkregular SET f1 = 501;+ERROR: update or delete on table "pkregular" violates foreign key constraint "fkpartit_f1_fkey" on table "fkpartit"+DETAIL: Key (f1)=(500) is still referenced from table "fkpartit".+ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ERROR: cannot drop inherited constraint "fkpartit_f1_fkey" of relation "fkpart1"+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)++ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+ Table "public.fkpartit"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition key: RANGE (f1)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE+Number of partitions: 1 (Use \d+ to list them.)++\d fkpart1+ Table "public.fkpart1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (0) TO (1000)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE++DELETE FROM pkregular;+SELECT * FROM fkpartit;+ f1 +----+(0 rows)++CREATE TABLE fkpart2 (f_drop1 int, f_drop2 int, f1 int);+ALTER TABLE fkpart2 DROP COLUMN f_drop1, DROP COLUMN f_drop2;+ALTER TABLE fkpartit ATTACH PARTITION fkpart2 FOR VALUES FROM (1000) TO (2000);+\d fkpart2+ Table "public.fkpart2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ f1 | integer | | | +Partition of: fkpartit FOR VALUES FROM (1000) TO (2000)+Foreign-key constraints:+ "fkpartit_f1_fkey" FOREIGN KEY (f1) REFERENCES pkregular(f1) ON DELETE CASCADE++INSERT INTO fkpartit VALUES (1500); -- must fail: pk value is not there+ERROR: insert or update on table "fkpart2" violates foreign key constraint "fkpartit_f1_fkey"+DETAIL: Key (f1)=(1500) is not present in table "pkregular".+INSERT INTO pkregular VALUES (1500);+INSERT INTO fkpartit VALUES (1500); -- now it works+DROP TABLE fkpartit, pkregular;+-- Test that it's possible to have a FK from a regular table to a+-- partitioned one+CREATE TABLE pk_partit (f1 int) PARTITION BY RANGE (f1);+CREATE TABLE fk_regular (f1 int);+CREATE TABLE pkpartit1 PARTITION OF pk_partit FOR VALUES FROM (0) TO (1000);+ALTER TABLE pk_partit ADD PRIMARY KEY (f1);+ALTER TABLE fk_regular ADD FOREIGN KEY (f1) REFERENCES pk_partit;+CREATE TABLE pkpartit2 PARTITION OF pk_partit FOR VALUES FROM (1000) to (2000);+CREATE TABLE pkpartit3 (f0 int, f1 int NOT NULL);+ALTER TABLE pkpartit3 DROP COLUMN f0;+ALTER TABLE pk_partit ATTACH PARTITION pkpartit3 FOR VALUES FROM (2000) to (3000);+INSERT INTO fk_regular VALUES (500); -- must fail+ERROR: insert or update on table "fk_regular" violates foreign key constraint "fk_regular_f1_fkey"+DETAIL: Key (f1)=(500) is not present in table "pk_partit".+INSERT INTO fk_regular VALUES (1500); -- must fail+ERROR: insert or update on table "fk_regular" violates foreign key constraint "fk_regular_f1_fkey"+DETAIL: Key (f1)=(1500) is not present in table "pk_partit".+INSERT INTO fk_regular VALUES (2500); -- must fail+ERROR: insert or update on table "fk_regular" violates foreign key constraint "fk_regular_f1_fkey"+DETAIL: Key (f1)=(2500) is not present in table "pk_partit".+INSERT INTO pk_partit VALUES (500), (1500), (2500);+INSERT INTO fk_regular VALUES (500); -- must work+INSERT INTO fk_regular VALUES (1500); -- must work+INSERT INTO fk_regular VALUES (2500); -- must workdiff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex b73f523e8a..f6dedf0287 100644--- a/src/test/regress/sql/alter_table.sql+++ b/src/test/regress/sql/alter_table.sql@@ -394,6 +394,62 @@ DROP TABLE tmp3;
DROP TABLE tmp2;
+-- Ensure we can add foreign keys to and 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);+CREATE TABLE at_regular2 (col3 INT);+ALTER TABLE at_regular2 ADD FOREIGN KEY (col3) REFERENCES at_partitioned;+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_regular2 VALUES (1000);+INSERT INTO at_partitioned VALUES (1000, 42);++-- these work:+INSERT INTO at_regular1 VALUES (1000);+INSERT INTO at_partitioned VALUES (42, 1000);+INSERT INTO at_regular2 VALUES (42);++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_regular2+\d at_partitioned+\d at_partitioned_0+\d at_partitioned_1+\d at_partitioned_2++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);+INSERT INTO at_regular2 VALUES (5000), (15000), (25000);+INSERT INTO at_regular2 VALUES (35000);++-- ok+ALTER TABLE at_regular2 DROP CONSTRAINT at_regular2_col3_fkey;++-- disallowed: must drop it from parent instead+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 +2091,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.sqlindex 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.sqlindex 5f19dad03c..6cdeb68497 100644--- a/src/test/regress/sql/foreign_key.sql+++ b/src/test/regress/sql/foreign_key.sql@@ -1055,3 +1055,66 @@ alter table fktable2 drop constraint fktable2_f1_fkey;
commit;
drop table pktable2, fktable2;
+++--+-- Foreign keys and partitioned tables+--++-- Test that it's possible to have a FK from a partitioned table to a regular+-- one+CREATE TABLE pkregular (f_drop1 int, f1 int primary key);+ALTER TABLE pkregular DROP COLUMN f_drop1;+CREATE TABLE fkpartit (f1 int references pkregular) PARTITION BY RANGE (f1);+CREATE TABLE fkpart1 PARTITION OF fkpartit FOR VALUES FROM (0) TO (1000);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+INSERT INTO pkregular VALUES (500);+INSERT INTO fkpartit VALUES (500);+INSERT INTO fkpart1 VALUES (500);+DELETE FROM pkregular;+UPDATE pkregular SET f1 = 501;++ALTER TABLE fkpart1 DROP CONSTRAINT fkpartit_f1_fkey; -- nope+ALTER TABLE fkpartit DROP CONSTRAINT fkpartit_f1_fkey;+\d fkpartit+\d fkpart1+ALTER TABLE fkpartit ADD CONSTRAINT fkpartit_f1_fkey FOREIGN KEY (f1) REFERENCES pkregular ON DELETE CASCADE;+\d fkpartit+\d fkpart1+DELETE FROM pkregular;+SELECT * FROM fkpartit;++CREATE TABLE fkpart2 (f_drop1 int, f_drop2 int, f1 int);+ALTER TABLE fkpart2 DROP COLUMN f_drop1, DROP COLUMN f_drop2;+ALTER TABLE fkpartit ATTACH PARTITION fkpart2 FOR VALUES FROM (1000) TO (2000);+\d fkpart2+INSERT INTO fkpartit VALUES (1500); -- must fail: pk value is not there+INSERT INTO pkregular VALUES (1500);++INSERT INTO fkpartit VALUES (1500); -- now it works+DROP TABLE fkpartit, pkregular;++-- Test that it's possible to have a FK from a regular table to a+-- partitioned one++CREATE TABLE pk_partit (f1 int) PARTITION BY RANGE (f1);+CREATE TABLE fk_regular (f1 int);+CREATE TABLE pkpartit1 PARTITION OF pk_partit FOR VALUES FROM (0) TO (1000);+ALTER TABLE pk_partit ADD PRIMARY KEY (f1);+ALTER TABLE fk_regular ADD FOREIGN KEY (f1) REFERENCES pk_partit;+CREATE TABLE pkpartit2 PARTITION OF pk_partit FOR VALUES FROM (1000) to (2000);++CREATE TABLE pkpartit3 (f0 int, f1 int NOT NULL);+ALTER TABLE pkpartit3 DROP COLUMN f0;+ALTER TABLE pk_partit ATTACH PARTITION pkpartit3 FOR VALUES FROM (2000) to (3000);++INSERT INTO fk_regular VALUES (500); -- must fail+INSERT INTO fk_regular VALUES (1500); -- must fail+INSERT INTO fk_regular VALUES (2500); -- must fail++INSERT INTO pk_partit VALUES (500), (1500), (2500);++INSERT INTO fk_regular VALUES (500); -- must work+INSERT INTO fk_regular VALUES (1500); -- must work+INSERT INTO fk_regular VALUES (2500); -- must work--
2.11.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com
Subject: Re: Foreign keys and partitioned tables
In-Reply-To: <20180312024023.w2t37viip5wejz4y@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox