Peter Eisentraut wrote:
> On 12/29/17 17:53, Alvaro Herrera wrote:
> > This patch enables FOR EACH ROW triggers on partitioned tables.
> >
> > As presented, this patch is sufficient to discuss the semantics that we
> > want for triggers on partitioned tables, which is the most pressing
> > question here ISTM.
>
> This seems pretty straightforward. What semantics questions do you have?
The main question is this: when running the trigger function, it is
going to look as it is running in the context of the partition, not in
the context of the parent partitioned table (TG_RELNAME etc). That
seems mildly ugly: some users may be expecting that the partitioning
stuff is invisible to the rest of the system, so if you have triggers on
a regular table and later on decide to partition that table, the
behavior of triggers will change, which is maybe unexpected. Maybe this
is not really a problem, but I'm not sure and would like further
opinions.
Anyway, the attached v2 has the following changes
1. ALTER TABLE ATTACH PARTITION and CREATE TABLE PARTITION OF now clone
any triggers from the main table, as if the trigger had been created
with the partitions in place.
2. dependencies work correctly: dropping the trigger on a partition is
disallowed; dropping the table removes the trigger. This is pretty
much the same behavior we have for indexes in partitions; I've reused
the new dependency type.
While existing pg_dump tests pass, I have not verified that it does
anything remotely sensible.
> Also, does ALTER TABLE ... ENABLE/DISABLE TRIGGER do the right things on
> partitioned tables?
Haven't done this yet, either. I like Simon's suggestion of outright
disallowing this.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 165c0e740917d01eec21722142b4268f2ae0f4da 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 v2] Allow FOR EACH ROW triggers on partitioned tables
---
src/backend/catalog/index.c | 3 +-
src/backend/commands/tablecmds.c | 87 ++++++++++++++++++++--
src/backend/commands/trigger.c | 124 ++++++++++++++++++++++++++++---
src/backend/tcop/utility.c | 3 +-
src/include/commands/trigger.h | 2 +-
src/test/regress/expected/triggers.out | 130 ++++++++++++++++++++++++++++++---
src/test/regress/sql/triggers.sql | 87 +++++++++++++++++++---
7 files changed, 393 insertions(+), 43 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 849a469127..1b29ff48eb 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1319,7 +1319,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/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2e768dd5e4..51a1d80b9d 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 CloneRowTriggersOnPartition(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)
{
@@ -956,6 +959,14 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
}
list_free(idxlist);
+
+ /*
+ * If there are any triggers, clone the appropriate ones to the
new
+ * partition.
+ */
+ if (parent->trigdesc != NULL)
+ CloneRowTriggersOnPartition(RelationGetRelid(parent),
relationId);
+
heap_close(parent, NoLock);
}
@@ -8426,7 +8437,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();
@@ -8500,7 +8511,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();
@@ -8555,7 +8566,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();
@@ -14010,6 +14021,8 @@ ATExecAttachPartition(List **wqueue, Relation rel,
PartitionCmd *cmd)
/* Ensure there exists a correct set of indexes in the partition. */
AttachPartitionEnsureIndexes(rel, attachrel);
+ /* and triggers */
+ CloneRowTriggersOnPartition(RelationGetRelid(rel),
RelationGetRelid(attachrel));
/*
* Generate partition constraint from the partition bound specification.
@@ -14194,6 +14207,9 @@ AttachPartitionEnsureIndexes(Relation rel, Relation
attachrel)
index_close(idxRel, AccessShareLock);
}
+ /* Make this all visible */
+ CommandCounterIncrement();
+
/* Clean up. */
for (i = 0; i < list_length(attachRelIdxs); i++)
index_close(attachrelIdxRels[i], AccessShareLock);
@@ -14202,6 +14218,65 @@ AttachPartitionEnsureIndexes(Relation rel, Relation
attachrel)
}
/*
+ * CloneRowTriggersOnPartition
+ * subroutine for ATExecAttachPartition/DefineRelation to create
row
+ * triggers on partitions
+ */
+static void
+CloneRowTriggersOnPartition(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);
+ if (!TRIGGER_FOR_ROW(trigForm->tgtype) ||
+ TRIGGER_FOR_BEFORE(trigForm->tgtype) ||
+ TRIGGER_FOR_INSTEAD(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 160d941c00..7cb709ea26 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -125,6 +125,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 +139,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 +150,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 +169,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 +188,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 +198,69 @@ 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)
+ {
+ /*
+ * Disallow WHEN clauses; I think it's okay, but
disallow for now
+ * to reduce testing surface.
+ */
+ if (stmt->whenClause)
+ ereport(ERROR,
+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is a
partitioned table",
+
RelationGetRelationName(rel)),
+ errdetail("Triggers FOR EACH
ROW on partitioned table cannot have WHEN clauses.")));
+
+ /*
+ * 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.")));
+
+ /*
+ * Constraint triggers are not allowed, either.
+ */
+ if (stmt->isconstraint)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("\"%s\" is a
partitioned table",
+
RelationGetRelationName(rel)),
+ errdetail("Partitioned tables
cannot have CONSTRAINT triggers FOR EACH ROW.")));
+
+ /*
+ * 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 +651,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);
@@ -928,11 +993,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 +1026,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 +1061,31 @@ CreateTrigger(CreateTrigStmt *stmt, const char
*queryString,
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
isInternal);
+ /*
+ * If this is a FOR EACH ROW trigger on a partitioned table, recurse for
+ * each partition if invoked directly by user (otherwise, caller must do
+ * its own recursion).
+ */
+ if (stmt->row && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
+ !isInternal)
+ {
+ PartitionDesc partdesc = RelationGetPartitionDesc(rel);
+ int i;
+
+ for (i = 0; i < partdesc->nparts; i++)
+ {
+ /* XXX must create a separate constraint for each child
*/
+ Assert(constraintOid == InvalidOid);
+ /* XXX must create a separate index for each child */
+ Assert(indexOid == InvalidOid);
+
+ CreateTrigger(copyObject(stmt), queryString,
+ partdesc->oids[i], refRelOid,
+ constraintOid, indexOid,
+ InvalidOid, trigoid,
isInternal);
+ }
+ }
+
/* Keep lock on target rel until end of xact */
heap_close(rel, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3abe7d6155..52648b687a 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1506,7 +1506,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/commands/trigger.h b/src/include/commands/trigger.h
index ff5546cf28..3970ab06b4 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);
diff --git a/src/test/regress/expected/triggers.out
b/src/test/regress/expected/triggers.out
index 9a7aafcc96..54116064b7 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1815,7 +1815,80 @@ 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 after update on parted_trig
+ for each row when (OLD.a <> NEW.a) execute procedure trigger_nothing();
+ERROR: "parted_trig" is a partitioned table
+DETAIL: Triggers FOR EACH ROW on partitioned table cannot have WHEN clauses.
+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 statement execute procedure trigger_nothing();
+create constraint trigger failed after insert on parted_trig
+ for each row execute procedure trigger_nothing();
+ERROR: "parted_trig" is a partitioned table
+DETAIL: Partitioned tables cannot have CONSTRAINT triggers FOR EACH ROW.
+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);
@@ -1832,7 +1905,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
@@ -1845,27 +1918,51 @@ 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
@@ -1874,6 +1971,8 @@ NOTICE: trigger on parted_stmt_trig BEFORE INSERT for
STATEMENT
NOTICE: trigger on parted2_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger on parted2_stmt_trig AFTER INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT
tableoid | a
@@ -1889,21 +1988,28 @@ NOTICE: trigger on parted_stmt_trig BEFORE UPDATE for
STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE UPDATE for ROW
NOTICE: trigger on parted2_stmt_trig BEFORE UPDATE for STATEMENT
NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER UPDATE for ROW
+NOTICE: trigger on parted_stmt_trig2 AFTER UPDATE for ROW
NOTICE: trigger on parted_stmt_trig AFTER UPDATE for STATEMENT
NOTICE: trigger on parted2_stmt_trig AFTER UPDATE for STATEMENT
delete from parted_stmt_trig;
NOTICE: trigger on parted_stmt_trig BEFORE DELETE for STATEMENT
+NOTICE: trigger on parted_stmt_trig1 BEFORE DELETE for ROW
+NOTICE: trigger on parted_stmt_trig2 AFTER DELETE for ROW
NOTICE: trigger on parted_stmt_trig AFTER DELETE for STATEMENT
-- insert via copy on the parent
copy parted_stmt_trig(a) from stdin;
NOTICE: trigger on parted_stmt_trig BEFORE INSERT for STATEMENT
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig2 AFTER INSERT for ROW
NOTICE: trigger on parted_stmt_trig AFTER INSERT for STATEMENT
-- insert via copy on the first partition
copy parted_stmt_trig1(a) from stdin;
NOTICE: trigger on parted_stmt_trig1 BEFORE INSERT for ROW
NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
+NOTICE: trigger on parted_stmt_trig1 AFTER INSERT for ROW
drop table parted_stmt_trig, parted2_stmt_trig;
--
-- Test the interaction between transition tables and both kinds of
diff --git a/src/test/regress/sql/triggers.sql
b/src/test/regress/sql/triggers.sql
index 47b5bde390..8dee659757 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1292,7 +1292,50 @@ 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 after update on parted_trig
+ for each row when (OLD.a <> NEW.a) 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 statement execute procedure trigger_nothing();
+create constraint trigger failed after insert on parted_trig
+ 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);
@@ -1312,7 +1355,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
@@ -1326,28 +1369,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 (
--
2.11.0
