pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: FOR EACH ROW triggers on partitioned tables
Date: Thu, 8 Mar 2018 15:06:49 -0300
Message-ID: <20180308180649.zdcubcx76tymwnrh@alvherre.pgsql> (raw)
In-Reply-To: <CAEepm=3BKJZX3fax3bdM=cWfH9BS+sa1kLdeFS6qvGvYzrkqcQ@mail.gmail.com>

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/171cb95a-35ec-2ace-3add-a8d16279f0bf@2ndquadrant.com

-- 
Á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 <alvherre@alvh.no-ip.org>
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 <alvherre@alvh.no-ip.org>
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 <alvherre@alvh.no-ip.org>
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

Attachments:

  [text/plain] v6-0001-add-missing-CommandCounterIncrement-in-StoreParti.patch (876B, ../20180308180649.zdcubcx76tymwnrh@alvherre.pgsql/2-v6-0001-add-missing-CommandCounterIncrement-in-StoreParti.patch)
  download | inline diff:
From 994e69105fc341add1e5b5cc76e8fa039f81d6a4 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:01:39 -0300
Subject: [PATCH 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

  [text/plain] v6-0002-Add-missing-CommandCounterIncrement-in-partitione.patch (2.2K, ../20180308180649.zdcubcx76tymwnrh@alvherre.pgsql/3-v6-0002-Add-missing-CommandCounterIncrement-in-partitione.patch)
  download | inline diff:
From 4d68f3ef71667696c41ede27fe8d3fd0dcec7844 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu, 8 Mar 2018 14:04:13 -0300
Subject: [PATCH 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

  [text/plain] v6-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch (42.2K, ../20180308180649.zdcubcx76tymwnrh@alvherre.pgsql/4-v6-0003-Allow-FOR-EACH-ROW-triggers-on-partitioned-tables.patch)
  download | inline diff:
From 40301760e580901379953f400c8c992917234556 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Tue, 21 Nov 2017 15:53:11 -0300
Subject: [PATCH 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

view thread (39+ messages)  latest in thread

Message-ID: <20180308180649.zdcubcx76tymwnrh@alvherre.pgsql>
Permalink:  ../20180308180649.zdcubcx76tymwnrh@alvherre.pgsql/
Also on:    postgresql.org/message-id/20180308180649.zdcubcx76tymwnrh@alvherre.pgsql

copy link · copy postgr.es · copy git am

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: alvherre@alvh.no-ip.org, thomas.munro@enterprisedb.com, robertmhaas@gmail.com, peter.eisentraut@2ndquadrant.com, simon@2ndquadrant.com
  Subject: Re: FOR EACH ROW triggers on partitioned tables
  In-Reply-To: <20180308180649.zdcubcx76tymwnrh@alvherre.pgsql>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox