Hi hackers,
I noticed that runtime stats for BEFORE ROW INSERT triggers on leaf
partitions of partitioned tables aren't reported in EXPLAIN ANALYZE.
Here is an example:
postgres=# create table trigger_test (a int, b text) partition by list (a);
CREATE TABLE
postgres=# create table trigger_test1 partition of trigger_test for
values in (1);
CREATE TABLE
postgres=# create trigger before_ins_row_trig BEFORE INSERT ON
trigger_test1 FOR EACH ROW EXECUTE PROCEDURE trigger_data();
CREATE TRIGGER
postgres=# create trigger after_ins_row_trig AFTER INSERT ON
trigger_test1 FOR EACH ROW EXECUTE PROCEDURE trigger_data();
CREATE TRIGGER
postgres=# explain analyze insert into trigger_test values (1, 'foo');
NOTICE: before_ins_row_trig() BEFORE ROW INSERT ON trigger_test1
NOTICE: NEW: (1,foo)
NOTICE: after_ins_row_trig() AFTER ROW INSERT ON trigger_test1
NOTICE: NEW: (1,foo)
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Insert on trigger_test (cost=0.00..0.01 rows=1 width=36) (actual
time=0.193..0.193 rows=0 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=36) (actual
time=0.002..0.003 rows=1 loops=1)
Planning time: 0.027 ms
Trigger after_ins_row_trig on trigger_test1: time=0.075 calls=1
Execution time: 0.310 ms
(5 rows)
where trig_data() is borrowed from the regression test in postgres_fdw.
The stats for the AFTER ROW INSERT trigger after_ins_row_trig are well
shown in the output, but the stats for the BEFORE ROW INSERT trigger
before_ins_row_trig aren't at all. I think we should show the latter as
well.
Another thing I noticed is: runtime stats for BEFORE STATEMENT
UPDATE/DELETE triggers on partitioned table roots aren't reported in
EXPLAIN ANALYZE, either, as shown in a below example:
postgres=# create trigger before_upd_stmt_trig BEFORE UPDATE ON
trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
postgres=# create trigger after_upd_stmt_trig AFTER UPDATE ON
trigger_test FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER
postgres=# explain analyze update trigger_test set b = 'bar' where a = 1;
NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = BEFORE,
level = STATEMENT
NOTICE: trigger_func(<NULL>) called: action = UPDATE, when = AFTER,
level = STATEMENT
QUERY PLAN
----------------------------------------------------------------------------------------------------------
-----
Update on trigger_test (cost=0.00..25.88 rows=6 width=42) (actual
time=0.296..0.296 rows=0 loops=1)
Update on trigger_test1
-> Seq Scan on trigger_test1 (cost=0.00..25.88 rows=6 width=42)
(actual time=0.010..0.011 rows=1 loops=1)
Filter: (a = 1)
Planning time: 0.152 ms
Trigger after_upd_stmt_trig on trigger_test: time=0.141 calls=1
Execution time: 0.476 ms
(7 rows)
where trigger_func() is borrowed from the regression test, too. The
stats for the BEFORE STATEMENT UPDATE trigger before_upd_stmt_trig
aren't shown at all in the output. I think this should also be fixed.
So here is a patch for fixing both issues. Changes I made are:
* To fix the former, I added a new List member es_leaf_result_relations
to EState, modified ExecSetupPartitionTupleRouting so that it creates
ResultRelInfos with the EState's es_instrument and then saves them in
that list, and modified ExplainPrintTriggers to show stats for BEFORE
ROW INSERT triggers on leaf partitions (if any) by looking at that list.
I also modified copy.c so that ExecSetupPartitionTupleRouting and
related things are performed in CopyFrom after its EState creation.
* To fix the latter, I modified ExplainPrintTriggers to show stats for
BEFORE STATEMENT UPDATE/DELETE triggers on partitioned table roots (if
any) by looking at the es_root_result_relations array.
* While fixing these, I noticed that AFTER ROW INSERT triggers on leaf
partitions and BEFORE STATEMENT UPDATE/DELETE triggers on partitioned
table roots re-open relations and re-create ResultRelInfos (trigger-only
ResultRelInfos!) in ExecGetTriggerResultRel when executing triggers (and
that in the above examples, the stats for AFTER ROW INSERT trigger/AFTER
STATEMENT UPDATE trigger are shown the result for
es_trig_target_relations in ExplainPrintTriggers). But that wouldn't be
efficient (and EXPLAIN ANALYZE might produce odd outputs), so I modified
ExecGetTriggerResultRel so that it searches es_leaf_result_relations and
es_root_result_relations in addition to es_result_relations.
Best regards,
Etsuro Fujita
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***************
*** 1415,1473 **** BeginCopy(ParseState *pstate,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("table \"%s\" does not have
OIDs",
RelationGetRelationName(cstate->rel))));
-
- /*
- * If there are any triggers with transition tables on the named
- * relation, we need to be prepared to capture transition
tuples.
- */
- cstate->transition_capture =
MakeTransitionCaptureState(rel->trigdesc);
-
- /* Initialize state for CopyFrom tuple routing. */
- if (is_from && rel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE)
- {
- PartitionDispatch *partition_dispatch_info;
- ResultRelInfo *partitions;
- TupleConversionMap **partition_tupconv_maps;
- TupleTableSlot *partition_tuple_slot;
- int num_parted,
- num_partitions;
-
- ExecSetupPartitionTupleRouting(rel,
-
1,
-
&partition_dispatch_info,
-
&partitions,
-
&partition_tupconv_maps,
-
&partition_tuple_slot,
-
&num_parted, &num_partitions);
- cstate->partition_dispatch_info =
partition_dispatch_info;
- cstate->num_dispatch = num_parted;
- cstate->partitions = partitions;
- cstate->num_partitions = num_partitions;
- cstate->partition_tupconv_maps = partition_tupconv_maps;
- cstate->partition_tuple_slot = partition_tuple_slot;
-
- /*
- * If we are capturing transition tuples, they may need
to be
- * converted from partition format back to partitioned
table
- * format (this is only ever necessary if a BEFORE
trigger
- * modifies the tuple).
- */
- if (cstate->transition_capture != NULL)
- {
- int i;
-
- cstate->transition_tupconv_maps =
(TupleConversionMap **)
- palloc0(sizeof(TupleConversionMap *) *
- cstate->num_partitions);
- for (i = 0; i < cstate->num_partitions; ++i)
- {
- cstate->transition_tupconv_maps[i] =
-
convert_tuples_by_name(RelationGetDescr(cstate->partitions[i].ri_RelationDesc),
-
RelationGetDescr(rel),
-
gettext_noop("could not convert row type"));
- }
- }
- }
}
else
{
--- 1415,1420 ----
***************
*** 2483,2488 **** CopyFrom(CopyState cstate)
--- 2430,2492 ----
estate->es_trig_tuple_slot = ExecInitExtraTupleSlot(estate);
/*
+ * If there are any triggers with transition tables on the named
relation,
+ * we need to be prepared to capture transition tuples.
+ */
+ cstate->transition_capture =
+ MakeTransitionCaptureState(cstate->rel->trigdesc);
+
+ /*
+ * If the named relation is a partitioned table, initialize state for
+ * CopyFrom tuple routing.
+ */
+ if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ PartitionDispatch *partition_dispatch_info;
+ ResultRelInfo *partitions;
+ TupleConversionMap **partition_tupconv_maps;
+ TupleTableSlot *partition_tuple_slot;
+ int num_parted,
+ num_partitions;
+
+ ExecSetupPartitionTupleRouting(cstate->rel,
+ 1,
+
estate,
+
&partition_dispatch_info,
+
&partitions,
+
&partition_tupconv_maps,
+
&partition_tuple_slot,
+
&num_parted, &num_partitions);
+ cstate->partition_dispatch_info = partition_dispatch_info;
+ cstate->num_dispatch = num_parted;
+ cstate->partitions = partitions;
+ cstate->num_partitions = num_partitions;
+ cstate->partition_tupconv_maps = partition_tupconv_maps;
+ cstate->partition_tuple_slot = partition_tuple_slot;
+
+ /*
+ * If we are capturing transition tuples, they may need to be
+ * converted from partition format back to partitioned table
format
+ * (this is only ever necessary if a BEFORE trigger modifies the
+ * tuple).
+ */
+ if (cstate->transition_capture != NULL)
+ {
+ int i;
+
+ cstate->transition_tupconv_maps = (TupleConversionMap
**)
+ palloc0(sizeof(TupleConversionMap *) *
cstate->num_partitions);
+ for (i = 0; i < cstate->num_partitions; ++i)
+ {
+ cstate->transition_tupconv_maps[i] =
+
convert_tuples_by_name(RelationGetDescr(cstate->partitions[i].ri_RelationDesc),
+
RelationGetDescr(cstate->rel),
+
gettext_noop("could not convert row type"));
+ }
+ }
+ }
+
+ /*
* It's more efficient to prepare a bunch of tuples for insertion, and
* insert them in one heap_multi_insert() call, than call heap_insert()
* separately for every tuple. However, we can't do that if there are
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 656,672 **** ExplainPrintTriggers(ExplainState *es, QueryDesc *queryDesc)
ResultRelInfo *rInfo;
bool show_relname;
int numrels =
queryDesc->estate->es_num_result_relations;
List *targrels = queryDesc->estate->es_trig_target_relations;
int nr;
ListCell *l;
ExplainOpenGroup("Triggers", "Triggers", false, es);
! show_relname = (numrels > 1 || targrels != NIL);
rInfo = queryDesc->estate->es_result_relations;
for (nr = 0; nr < numrels; rInfo++, nr++)
report_triggers(rInfo, show_relname, es);
foreach(l, targrels)
{
rInfo = (ResultRelInfo *) lfirst(l);
--- 656,685 ----
ResultRelInfo *rInfo;
bool show_relname;
int numrels =
queryDesc->estate->es_num_result_relations;
+ int numrootrels =
queryDesc->estate->es_num_root_result_relations;
+ List *leafrels = queryDesc->estate->es_leaf_result_relations;
List *targrels = queryDesc->estate->es_trig_target_relations;
int nr;
ListCell *l;
ExplainOpenGroup("Triggers", "Triggers", false, es);
! show_relname = (numrels > 1 || numrootrels > 0 ||
! leafrels != NIL || targrels != NIL);
rInfo = queryDesc->estate->es_result_relations;
for (nr = 0; nr < numrels; rInfo++, nr++)
report_triggers(rInfo, show_relname, es);
+ rInfo = queryDesc->estate->es_root_result_relations;
+ for (nr = 0; nr < numrootrels; rInfo++, nr++)
+ report_triggers(rInfo, show_relname, es);
+
+ foreach(l, leafrels)
+ {
+ rInfo = (ResultRelInfo *) lfirst(l);
+ report_triggers(rInfo, show_relname, es);
+ }
+
foreach(l, targrels)
{
rInfo = (ResultRelInfo *) lfirst(l);
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 1364,1379 **** InitResultRelInfo(ResultRelInfo *resultRelInfo,
*
* Get a ResultRelInfo for a trigger target relation. Most of the time,
* triggers are fired on one of the result relations of the query, and so
! * we can just return a member of the es_result_relations array. (Note: in
! * self-join situations there might be multiple members with the same OID;
! * if so it doesn't matter which one we pick.) However, it is sometimes
! * necessary to fire triggers on other relations; this happens mainly when an
! * RI update trigger queues additional triggers on other relations, which will
! * be processed in the context of the outer query. For efficiency's sake,
! * we want to have a ResultRelInfo for those triggers too; that can avoid
! * repeated re-opening of the relation. (It also provides a way for EXPLAIN
! * ANALYZE to report the runtimes of such triggers.) So we make additional
! * ResultRelInfo's as needed, and save them in es_trig_target_relations.
*/
ResultRelInfo *
ExecGetTriggerResultRel(EState *estate, Oid relid)
--- 1364,1381 ----
*
* Get a ResultRelInfo for a trigger target relation. Most of the time,
* triggers are fired on one of the result relations of the query, and so
! * we can just return a member of the es_result_relations array, the
! * es_root_result_relations array (if any), or the es_leaf_result_relations
! * list (if any). (Note: in self-join situations there might be multiple
! * members with the same OID; if so it doesn't matter which one we pick.)
! * However, it is sometimes necessary to fire triggers on other relations;
! * this happens mainly when an RI update trigger queues additional triggers
! * on other relations, which will be processed in the context of the outer
! * query. For efficiency's sake, we want to have a ResultRelInfo for those
! * triggers too; that can avoid repeated re-opening of the relation. (It
! * also provides a way for EXPLAIN ANALYZE to report the runtimes of such
! * triggers.) So we make additional ResultRelInfo's as needed, and save them
! * in es_trig_target_relations.
*/
ResultRelInfo *
ExecGetTriggerResultRel(EState *estate, Oid relid)
***************
*** 1394,1399 **** ExecGetTriggerResultRel(EState *estate, Oid relid)
--- 1396,1418 ----
rInfo++;
nr--;
}
+ /* Second, search through the root result relations, if any */
+ rInfo = estate->es_root_result_relations;
+ nr = estate->es_num_root_result_relations;
+ while (nr > 0)
+ {
+ if (RelationGetRelid(rInfo->ri_RelationDesc) == relid)
+ return rInfo;
+ rInfo++;
+ nr--;
+ }
+ /* Third, search through the leaf result relations, if any */
+ foreach(l, estate->es_leaf_result_relations)
+ {
+ rInfo = (ResultRelInfo *) lfirst(l);
+ if (RelationGetRelid(rInfo->ri_RelationDesc) == relid)
+ return rInfo;
+ }
/* Nope, but maybe we already made an extra ResultRelInfo for it */
foreach(l, estate->es_trig_target_relations)
{
***************
*** 3237,3242 **** EvalPlanQualEnd(EPQState *epqstate)
--- 3256,3262 ----
void
ExecSetupPartitionTupleRouting(Relation rel,
Index resultRTindex,
+ EState *estate,
PartitionDispatch
**pd,
ResultRelInfo
**partitions,
TupleConversionMap
***tup_conv_maps,
***************
*** 3297,3303 **** ExecSetupPartitionTupleRouting(Relation rel,
partrel,
resultRTindex,
rel,
! 0);
/*
* Open partition indices (remember we do not support ON
CONFLICT in
--- 3317,3326 ----
partrel,
resultRTindex,
rel,
! estate->es_instrument);
!
! estate->es_leaf_result_relations =
! lappend(estate->es_leaf_result_relations,
leaf_part_rri);
/*
* Open partition indices (remember we do not support ON
CONFLICT in
*** a/src/backend/executor/execUtils.c
--- b/src/backend/executor/execUtils.c
***************
*** 115,120 **** CreateExecutorState(void)
--- 115,125 ----
estate->es_num_result_relations = 0;
estate->es_result_relation_info = NULL;
+ estate->es_root_result_relations = NULL;
+ estate->es_num_root_result_relations = 0;
+
+ estate->es_leaf_result_relations = NIL;
+
estate->es_trig_target_relations = NIL;
estate->es_trig_tuple_slot = NULL;
estate->es_trig_oldtup_slot = NULL;
*** a/src/backend/executor/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 1919,1924 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int
eflags)
--- 1919,1925 ----
ExecSetupPartitionTupleRouting(rel,
node->nominalRelation,
+
estate,
&partition_dispatch_info,
&partitions,
&partition_tupconv_maps,
*** a/src/include/executor/executor.h
--- b/src/include/executor/executor.h
***************
*** 208,213 **** extern void EvalPlanQualSetTuple(EPQState *epqstate, Index rti,
--- 208,214 ----
extern HeapTuple EvalPlanQualGetTuple(EPQState *epqstate, Index rti);
extern void ExecSetupPartitionTupleRouting(Relation rel,
Index resultRTindex,
+ EState *estate,
PartitionDispatch
**pd,
ResultRelInfo
**partitions,
TupleConversionMap
***tup_conv_maps,
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 452,457 **** typedef struct EState
--- 452,460 ----
ResultRelInfo *es_root_result_relations; /* array of
ResultRelInfos */
int es_num_root_result_relations; /* length of
the array */
+ /* Info about leaf partitions of partitioned table(s) for insert
queries: */
+ List *es_leaf_result_relations; /* List of ResultRelInfos */
+
/* Stuff used for firing triggers: */
List *es_trig_target_relations; /* trigger-only ResultRelInfos
*/
TupleTableSlot *es_trig_tuple_slot; /* for trigger output tuples */
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
