Thomas Munro wrote:
> What is this test for?
>
> +create trigger failed after update on parted_trig
> + referencing old table as old_table
> + for each statement execute procedure trigger_nothing();
>
> It doesn't fail as you apparently expected. Perhaps it was supposed
> to be "for each row" so you could hit your new error with
> errdetail("Triggers on partitioned tables cannot have transition
> tables.")?
You're absolutely right. Fixed in the attached version.
I also include two requisite fixes for missing CCI calls in existing
code: one is in StorePartitionBounds which I think is backpatchable to
pg10 (this is the one that was causing me to add the one Peter
complained about in [1]), and the others are in the partition indexing
code. In terms of the current tests, the first one is necessary in
order for things to work after this patch; the ones in the second patch
I only added after code review in order to understand where the first
one was. (In that second patch I also remove one which now seems
unnecessary and in hindsight was probably there because I was lacking
the others.)
Patch 0003 is the feature at hand. Compared to v3, this version adds
some recursing logic during ENABLE/DISABLE TRIGGER, so the test that was
previously failing now works correctly.
I kept the test on "irregular" partitioning from v5, too; it works here
without any further changes.
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.
[1] https://postgr.es/m/[email protected]
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 994e69105fc341add1e5b5cc76e8fa039f81d6a4 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <[email protected]>
Date: Thu, 8 Mar 2018 14:01:39 -0300
Subject: [PATCH v6 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 4d68f3ef71667696c41ede27fe8d3fd0dcec7844 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <[email protected]>
Date: Thu, 8 Mar 2018 14:04:13 -0300
Subject: [PATCH v6 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 40301760e580901379953f400c8c992917234556 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <[email protected]>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH v6 3/3] Allow FOR EACH ROW triggers on partitioned tables
---
src/backend/catalog/index.c | 3 +-
src/backend/commands/tablecmds.c | 91 ++++++++++++-
src/backend/commands/trigger.c | 137 ++++++++++++++++++--
src/backend/tcop/utility.c | 3 +-
src/include/commands/trigger.h | 4 +-
src/test/regress/expected/triggers.out | 228 +++++++++++++++++++++++++++++----
src/test/regress/sql/triggers.sql | 142 ++++++++++++++++++--
7 files changed, 550 insertions(+), 58 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 431bc31969..1195064954 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1361,7 +1361,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 7ecfbc17a0..480f1b3996 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);
}
@@ -8455,7 +8466,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 +8540,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 +8595,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 +11135,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 +14051,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 +14270,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..36f6dc0db0 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,58 @@ 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.")));
+
+ /*
+ * 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 +640,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 +982,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 +1015,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 +1050,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);
@@ -1579,7 +1672,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 +1735,28 @@ 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)))
+ {
+ List *children;
+ ListCell *l;
+
+ children =
find_inheritance_children(RelationGetRelid(rel),
+
lockmode);
+ foreach(l, children)
+ {
+ Relation child =
relation_open(lfirst_oid(l), NoLock);
+
+ EnableDisableTrigger(child,
NameStr(oldtrig->tgname),
+
fires_when, skip_system, lockmode);
+ heap_close(child, NoLock);
+ }
+ }
+
changed = true;
}
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/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..49458b186b 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -1847,7 +1847,78 @@ 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.
+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);
@@ -1864,7 +1935,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 +1948,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 +2015,108 @@ 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 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);
+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
+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_irreg() 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_irreg();
+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_irreg();
+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;
+drop function trigger_notice_irreg();
--
-- 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/sql/triggers.sql
b/src/test/regress/sql/triggers.sql
index 3354f4899f..b72a5a28c2 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1286,7 +1286,48 @@ 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();
+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);
@@ -1306,7 +1347,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 +1361,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 +1426,65 @@ 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 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);
+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_irreg() 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_irreg();
+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_irreg();
+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;
+drop function trigger_notice_irreg();
+
--
-- 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
