Alvaro Herrera wrote:
> One thing I'd like to add before claiming this committable (backend-
> side) is enabling constraint triggers. AFAICT that requires a bit of
> additional logic, but it shouldn't be too terrible. This would allow
> for deferrable unique constraints, for example.
v7 supports constraint triggers. I added an example using a UNIQUE
DEFERRABLE constraint, and another one using plain CREATE CONSTRAINT TRIGGER.
It's neat to see that the WHEN clause is executed at the time of the
operation, and the trigger action is deferred (or not) till COMMIT time.
--
Á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 <alvhe...@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:01:39 -0300
Subject: [PATCH v7 1/3] 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.c
index 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 <alvhe...@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:04:13 -0300
Subject: [PATCH v7 2/3] 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.c
index 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.c
index 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.c
index 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 975902aa033877165d0aaec556edc09bb02808c7 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH v7 3/3] 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 | 183 +++++++++++++++++--
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, 756 insertions(+), 74 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 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.c
index 431bc31969..867bbe8f1e 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);
}
/*
diff --git a/src/backend/catalog/pg_constraint.c
b/src/backend/catalog/pg_constraint.c
index 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.c
index 7ecfbc17a0..4303c5a131 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);
/* 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);
/* 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);
/* 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);
+ 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.c
index fbd176b5d0..c4f63c8b90 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,7 @@ 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)
{
int16 tgtype;
int ncolumns;
@@ -159,7 +170,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 +189,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 +199,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 +632,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 +697,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
stmt->deferrable,
stmt->initdeferred,
true,
+
InvalidOid, /* no parent */
RelationGetRelid(rel),
NULL, /* no conkey */
0,
@@ -928,11 +975,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 +1008,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 +1043,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);
+ }
+ }
+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
@@ -1579,7 +1690,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 +1753,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 +5255,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 +5352,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.c
index 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.c
index f78efdf359..9b197aecc5 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);
break;
case T_CreatePLangStmt:
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 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.h
index 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.h
index 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.h
index ff5546cf28..048bb8d988 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);
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.out
index 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.source
index 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.source
index 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.sql
index 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
