8000 Fire per-statement triggers on partitioned tables. · postgrespro/postgres_cluster@e180c8a · GitHub
[go: up one dir, main page]
Skip to content

Commit e180c8a

Browse files
robertmhaasrobertmhaas
committed
Fire per-statement triggers on partitioned tables.
Even though no actual tuples are ever inserted into a partitioned table (the actual tuples are in the partitions, not the partitioned table itself), we still need to have a ResultRelInfo for the partitioned table, or per-statement triggers won't get fired. Amit Langote, per a report from Rajkumar Raghuwanshi. Reviewed by me. Discussion: http://postgr.es/m/CAKcux6%3DwYospCRY2J4XEFuVy0L41S%3Dfic7rmkbsU-GXhhSbmBg%40mail.gmail.com
1 parent e18b2c4 commit e180c8a

File tree

14 files changed

+296
-27
lines changed
  • outfuncs.c
  • readfuncs.c
  • optimizer/plan
  • include/nodes
  • test/regress

    • 14 files changed

    +296
    -27
    lines changed

    ‎doc/src/sgml/trigger.sgml

    Lines changed: 10 additions & 9 deletions
    Original file line numberDiff line numberDiff line change
    @@ -33,7 +33,8 @@
    3333
    <para>
    3434
    A trigger is a specification that the database should automatically
    3535
    execute a particular function whenever a certain type of operation is
    36-
    performed. Triggers can be attached to tables, views, and foreign tables.
    36+
    performed. Triggers can be attached to tables (partitioned or not),
    37+
    views, and foreign tables.
    3738
    </para>
    3839

    3940
    <para>
    @@ -111,14 +112,14 @@
    111112
    Statement-level <literal>BEFORE</> triggers naturally fire before the
    112113
    6D40 statement starts to do anything, while statement-level <literal>AFTER</>
    113114
    triggers fire at the very end of the statement. These types of
    114-
    triggers may be defined on tables or views. Row-level <literal>BEFORE</>
    115-
    triggers fire immediately before a particular row is operated on,
    116-
    while row-level <literal>AFTER</> triggers fire at the end of the
    117-
    statement (but before any statement-level <literal>AFTER</> triggers).
    118-
    These types of triggers may only be defined on tables and foreign tables.
    119-
    Row-level <literal>INSTEAD OF</> triggers may only be defined on views,
    120-
    and fire immediately as each row in the view is identified as needing to
    121-
    be operated on.
    115+
    triggers may be defined on tables, views, or foreign tables. Row-level
    116+
    <literal>BEFORE</> triggers fire immediately before a particular row is
    117+
    operated on, while row-level <literal>AFTER</> triggers fire at the end of
    118+
    the statement (but before any statement-level <literal>AFTER</> triggers).
    119+
    These types of triggers may only be defined on non-partitioned tables and
    120+
    foreign tables. Row-level <literal>INSTEAD OF</> triggers may only be
    121+
    defined on views, and fire immediately as each row in the view is
    122+
    identified as needing to be operated on.
    122123
    </para>
    123124

    124125
    <para>

    ‎src/backend/executor/execMain.c

    Lines changed: 50 additions & 5 deletions
    Original file line numberDiff line numberDiff line change
    @@ -861,17 +861,52 @@ InitPlan(QueryDesc *queryDesc, int eflags)
    861861

    862862
    /*
    863863
    * In the partitioned result relation case, lock the non-leaf result
    864-
    * relations too. We don't however need ResultRelInfos for them.
    864+
    * relations too. A subset of these are the roots of respective
    865+
    * partitioned tables, for which we also allocate ResulRelInfos.
    865866
    */
    867+
    estate->es_root_result_relations = NULL;
    868+
    estate->es_num_root_result_relations = 0;
    866869
    if (plannedstmt->nonleafResultRelations)
    867870
    {
    871+
    int num_roots = list_length(plannedstmt->rootResultRelations);
    872+
    873+
    /*
    874+
    * Firstly, build ResultRelInfos for all the partitioned table
    875+
    * roots, because we will need them to fire the statement-level
    876+
    * triggers, if any.
    877+
    */
    878+
    resultRelInfos = (ResultRelInfo *)
    879+
    palloc(num_roots * sizeof(ResultRelInfo));
    880+
    resultRelInfo = resultRelInfos;
    881+
    foreach(l, plannedstmt->rootResultRelations)
    882+
    {
    883+
    Index resultRelIndex = lfirst_int(l);
    884+
    Oid resultRelOid;
    885+
    Relation resultRelDesc;
    886+
    887+
    resultRelOid = getrelid(resultRelIndex, rangeTable);
    888+
    resultRelDesc = heap_open(resultRelOid, RowExclusiveLock);
    889+
    InitResultRelInfo(resultRelInfo,
    890+
    resultRelDesc,
    891+
    lfirst_int(l),
    892+
    NULL,
    893+
    estate->es_instrument);
    894+
    resultRelInfo++;
    895+
    }
    896+
    897+
    estate->es_root_result_relations = resultRelInfos;
    898+
    estate->es_num_root_result_relations = num_roots;
    899+
    900+
    /* Simply lock the rest of them. */
    868901
    foreach(l, plannedstmt->nonleafResultRelations)
    869902
    {
    870-
    Index resultRelationIndex = lfirst_int(l);
    871-
    Oid resultRelationOid;
    903+
    Index resultRelIndex = lfirst_int(l);
    872904

    873-
    resultRelationOid = getrelid(resultRelationIndex, rangeTable);
    874-
    LockRelationOid(resultRelationOid, RowExclusiveLock);
    905+
    /* We locked the roots above. */
    906+
    if (!list_member_int(plannedstmt->rootResultRelations,
    907+
    resultRelIndex))
    908+
    LockRelationOid(getrelid(resultRelIndex, rangeTable),
    909+
    RowExclusiveLock);
    875910
    }
    876911
    }
    877912
    }
    @@ -883,6 +918,8 @@ InitPlan(QueryDesc *queryDesc, int eflags)
    883918
    estate->es_result_relations = NULL;
    884919
    estate->es_num_result_relations = 0;
    885920
    estate->es_result_relation_info = NULL;
    921+
    estate->es_root_result_relations = NULL;
    922+
    estate->es_num_root_result_relations = 0;
    886923
    }
    887924

    888925
    /*
    @@ -1565,6 +1602,14 @@ ExecEndPlan(PlanState *planstate, EState *estate)
    15651602
    resultRelInfo++;
    15661603
    }
    15671604

    1605+
    /* Close the root target relation(s). */
    1606+
    resultRelInfo = estate->es_root_result_relations;
    1607+
    for (i = estate->es_num_root_result_relations; i > 0; i--)
    1608+
    {
    1609+
    heap_close(resultRelInfo->ri_RelationDesc, NoLock);
    1610+
    resultRelInfo++;
    1611+
    }
    1612+
    15681613
    /*
    15691614
    * likewise close any trigger target relations
    15701615
    */

    ‎src/backend/executor/nodeModifyTable.c

    Lines changed: 34 additions & 8 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1328,19 +1328,29 @@ ExecOnConflictUpdate(ModifyTableState *mtstate,
    13281328
    static void
    13291329
    fireBSTriggers(ModifyTableState *node)
    13301330
    {
    1331+
    ResultRelInfo *resultRelInfo = node->resultRelInfo;
    1332+
    1333+
    /*
    1334+
    * If the node modifies a partitioned table, we must fire its triggers.
    1335+
    * Note that in that case, node->resultRelInfo points to the first leaf
    1336+
    * partition, not the root table.
    1337+
    */
    1338+
    if (node->rootResultRelInfo != NULL)
    1339+
    resultRelInfo = node->rootResultRelInfo;
    1340+
    13311341
    switch (node->operation)
    13321342
    {
    13331343
    case CMD_INSERT:
    1334-
    ExecBSInsertTriggers(node->ps.state, node->resultRelInfo);
    1344+
    ExecBSInsertTriggers(node->ps.state, resultRelInfo);
    13351345
    if (node->mt_onconflict == ONCONFLICT_UPDATE)
    13361346
    ExecBSUpdateTriggers(node->ps.state,
    1337-
    node->resultRelInfo);
    1347+
    resultRelInfo);
    13381348
    break;
    13391349
    case CMD_UPDATE:
    1340-
    ExecBSUpdateTriggers(node->ps.state, node->resultRelInfo);
    1350+
    ExecBSUpdateTriggers(node->ps.state, resultRelInfo);
    13411351
    break;
    13421352
    case CMD_DELETE:
    1343-
    ExecBSDeleteTriggers(node->ps.state, node->resultRelInfo);
    1353+
    ExecBSDeleteTriggers(node->ps.state, resultRelInfo);
    13441354
    break;
    13451355
    default:
    13461356
    elog(ERROR, "unknown operation");
    @@ -1354,19 +1364,29 @@ fireBSTriggers(ModifyTableState *node)
    13541364
    static void
    13551365
    fireASTriggers(ModifyTableState *node)
    13561366
    {
    1367+
    ResultRelInfo *resultRelInfo = node->resultRelInfo;
    1368+
    1369+
    /*
    1370+
    * If the node modifies a partitioned table, we must fire its triggers.
    1371+
    * Note that in that case, node->resultRelInfo points to the first leaf
    1372+
    * partition, not the root table.
    1373+
    */
    1374+
    if (node->rootResultRelInfo != NULL)
    1375+
    resultRelInfo = node->rootResultRelInfo;
    1376+
    13571377
    switch (node->operation)
    13581378
    {
    13591379
    case CMD_INSERT:
    13601380
    if (node->mt_onconflict == ONCONFLICT_UPDATE)
    13611381
    ExecASUpdateTriggers(node->ps.state,
    1362-
    node->resultRelInfo);
    1363-
    ExecASInsertTriggers(node->ps.state, node->resultRelInfo);
    1382+
    resultRelInfo);
    1383+
    ExecASInsertTriggers(node->ps.state, resultRelInfo);
    13641384
    break;
    13651385
    case CMD_UPDATE:
    1366-
    ExecASUpdateTriggers(node->ps.state, node->resultRelInfo);
    1386+
    ExecASUpdateTriggers(node->ps.state, resultRelInfo);
    13671387
    break;
    13681388
    case CMD_DELETE:
    1369-
    ExecASDeleteTriggers(node->ps.state, node->resultRelInfo);
    1389+
    ExecASDeleteTriggers(node->ps.state, resultRelInfo);
    13701390
    break;
    13711391
    default:
    13721392
    elog(ERROR, "unknown operation");
    @@ -1652,6 +1672,12 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
    16521672

    16531673
    mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans);
    16541674
    mtstate->resultRelInfo = estate->es_result_relations + node->resultRelIndex;
    1675+
    1676+
    /* If modifying a partitioned table, initialize the root table info */
    1677+
    if (node->rootResultRelIndex >= 0)
    1678+
    mtstate->rootResultRelInfo = estate->es_root_result_relations +
    1679+
    node->rootResultRelIndex;
    1680+
    16551681
    mtstate->mt_arowmarks = (List **) palloc0(sizeof(List *) * nplans);
    16561682
    mtstate->mt_nplans = nplans;
    16571683
    mtstate->mt_onconflict = node->onConflictAction;

    ‎src/backend/nodes/copyfuncs.c

    Lines changed: 2 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -91,6 +91,7 @@ _copyPlannedStmt(const PlannedStmt *from)
    9191
    COPY_NODE_FIELD(rtable);
    9292
    COPY_NODE_FIELD(resultRelations);
    9393
    COPY_NODE_FIELD(nonleafResultRelations);
    94+
    COPY_NODE_FIELD(rootResultRelations);
    9495
    COPY_NODE_FIELD(subplans);
    9596
    COPY_BITMAPSET_FIELD(rewindPlanIDs);
    9697
    COPY_NODE_FIELD(rowMarks);
    @@ -205,6 +206,7 @@ _copyModifyTable(const ModifyTable *from)
    205206
    COPY_NODE_FIELD(partitioned_rels);
    206207
    COPY_NODE_FIELD(resultRelations);
    207208
    COPY_SCALAR_FIELD(resultRelIndex);
    209+
    COPY_SCALAR_FIELD(rootResultRelIndex);
    208210
    COPY_NODE_FIELD(plans);
    209211
    COPY_NODE_FIELD(withCheckOptionLists);
    210212
    COPY_NODE_FIELD(returningLists);

    ‎src/backend/nodes/outfuncs.c

    Lines changed: 3 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -253,6 +253,7 @@ _outPlannedStmt(StringInfo str, const PlannedStmt *node)
    253253
    WRITE_NODE_FIELD(rtable);
    254254
    WRITE_NODE_FIELD(resultRelations);
    255255
    WRITE_NODE_FIELD(nonleafResultRelations);
    256+
    WRITE_NODE_FIELD(rootResultRelations);
    256257
    WRITE_NODE_FIELD(subplans);
    257258
    WRITE_BITMAPSET_FIELD(rewindPlanIDs);
    258259
    WRITE_NODE_FIELD(rowMarks);
    @@ -350,6 +351,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node)
    350351
    WRITE_NODE_FIELD(partitioned_rels);
    351352
    WRITE_NODE_FIELD(resultRelations);
    352353
    WRITE_INT_FIELD(resultRelIndex);
    354+
    WRITE_INT_FIELD(rootResultRelIndex);
    353355
    WRITE_NODE_FIELD(plans);
    354356
    WRITE_NODE_FIELD(withCheckOptionLists);
    355357
    WRITE_NODE_FIELD(returningLists);
    @@ -2145,6 +2147,7 @@ _outPlannerGlobal(StringInfo str, const PlannerGlobal *node)
    21452147
    WRITE_NODE_FIELD(finalrowmarks);
    21462148
    WRITE_NODE_FIELD(resultRelations);
    21472149
    WRITE_NODE_FIELD(nonleafResultRelations);
    2150+
    WRITE_NODE_FIELD(rootResultRelations);
    21482151
    WRITE_NODE_FIELD(relationOids);
    21492152
    WRITE_NODE_FIELD(invalItems);
    21502153
    WRITE_INT_FIELD(nParamExec);

    ‎src/backend/nodes/readfuncs.c

    Lines changed: 2 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1453,6 +1453,7 @@ _readPlannedStmt(void)
    14531453
    READ_NODE_FIELD(rtable);
    14541454
    READ_NODE_FIELD(resultRelations);
    14551455
    READ_NODE_FIELD(nonleafResultRelations);
    1456+
    READ_NODE_FIELD(rootResultRelations);
    14561457
    READ_NODE_FIELD(subplans);
    14571458
    READ_BITMAPSET_FIELD(rewindPlanIDs);
    14581459
    READ_NODE_FIELD(rowMarks);
    @@ -1548,6 +1549,7 @@ _readModifyTable(void)
    15481549
    READ_NODE_FIELD(partitioned_rels);
    15491550
    READ_NODE_FIELD(resultRelations);
    15501551
    READ_INT_FIELD(resultRelIndex);
    1552+
    READ_INT_FIELD(rootResultRelIndex);
    15511553
    READ_NODE_FIELD(plans);
    15521554
    READ_NODE_FIELD(withCheckOptionLists);
    15531555
    READ_NODE_FIELD(returningLists);

    ‎src/backend/optimizer/plan/createplan.c

    Lines changed: 1 addition & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -6437,6 +6437,7 @@ make_modifytable(PlannerInfo *root,
    64376437
    node->partitioned_rels = partitioned_rels;
    64386438
    node->resultRelations = resultRelations;
    64396439
    node->resultRelIndex = -1; /* will be set correctly in setrefs.c */
    6440+
    node->rootResultRelIndex = -1; /* will be set correctly in setrefs.c */
    64406441
    node->plans = subplans;
    64416442
    if (!onconflict)
    64426443
    {

    ‎src/backend/optimizer/plan/planner.c

    Lines changed: 3 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -240,6 +240,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
    240240
    glob->finalrowmarks = NIL;
    241241
    glob->resultRelations = NIL;
    242242
    glob->nonleafResultRelations = NIL;
    243+
    glob->rootResultRelations = NIL;
    243244
    glob->relationOids = NIL;
    244245
    glob->invalItems = NIL;
    245246
    glob->nParamExec = 0;
    @@ -408,6 +409,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
    408409
    Assert(glob->finalrowmarks == NIL);
    409410
    Assert(glob->resultRelations == NIL);
    410411
    Assert(glob->nonleafResultRelations == NIL);
    412+
    Assert(glob->rootResultRelations == NIL);
    411413
    top_plan = set_plan_references(root, top_plan);
    412414
    /* ... and the subplans (both regular subplans and initplans) */
    413415
    Assert(list_length(glob->subplans) == list_length(glob->subroots));
    @@ -434,6 +436,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
    434436
    result->rtable = glob->finalrtable;
    435437
    result->resultRelations = glob->resultRelations;
    436438
    result->nonleafResultRelations = glob->nonleafResultRelations;
    439+
    result->rootResultRelations = glob->rootResultRelations;
    437440
    result->subplans = glob->subplans;
    438441
    result->rewindPlanIDs = glob->rewindPlanIDs;
    439442
    result->rowMarks = glob->finalrowmarks;

    ‎src/backend/optimizer/plan/setrefs.c

    Lines changed: 15 additions & 4 deletions
    Original file line numberDiff line numberDiff line change
    @@ -882,11 +882,22 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
    882882
    /*
    883883
    * If the main target relation is a partitioned table, the
    884884
    * following list contains the RT indexes of partitioned child
    885-
    * relations, which are not included in the above list.
    885+
    * relations including the root, which are not included in the
    886+
    * above list. We also keep RT indexes of the roots separately
    887+
    * to be identitied as such during the executor initialization.
    886888
    */
    887-
    root->glob->nonleafResultRelations =
    888-
    list_concat(root->glob->nonleafResultRelations,
    889-
    list_copy(splan->partitioned_rels));
    889+
    if (splan->partitioned_rels != NIL)
    890+
    {
    891+
    root->glob->nonleafResultRelations =
    892+
    list_concat(root->glob->nonleafResultRelations,
    893+
    list_copy(splan->partitioned_rels));
    894+
    /* Remember where this root will be in the global list. */
    895+
    splan->rootResultRelIndex =
    896+
    list_length(root->glob->rootResultRelations);
    897+
    root->glob->rootResultRelations =
    898+
    lappend_int(root->glob->rootResultRelations,
    899+
    linitial_int(splan->partitioned_rels));
    900+
    }
    890901
    }
    891902
    break;
    892903
    case T_Append:

    ‎src/include/nodes/execnodes.h

    Lines changed: 12 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -422,6 +422,16 @@ typedef struct EState
    422422
    int es_num_result_relations; /* length of array */
    423423
    ResultRelInfo *es_result_relation_info; /* currently active array elt */
    424424

    425+
    /*
    426+
    * Info about the target partitioned target table root(s) for
    427+
    * update/delete queries. They required only to fire any per-statement
    428+
    * triggers defined on the table. It exists separately from
    429+
    * es_result_relations, because partitioned tables don't appear in the
    430+
    * plan tree for the update/delete cases.
    431+
    */
    432+
    ResultRelInfo *es_root_result_relations; /* array of ResultRelInfos */
    433+
    int es_num_root_result_relations; /* length of the array */
    434+
    4254 10000 35
    /* Stuff used for firing triggers: */
    426436
    List *es_trig_target_relations; /* trigger-only ResultRelInfos */
    427437
    TupleTableSlot *es_trig_tuple_slot; /* for trigger output tuples */
    @@ -914,6 +924,8 @@ typedef struct ModifyTableState
    914924
    int mt_nplans; /* number of plans in the array */
    915925
    int mt_whichplan; /* which one is being executed (0..n-1) */
    916926
    ResultRelInfo *resultRelInfo; /* per-subplan target relations */
    927+
    ResultRelInfo *rootResultRelInfo; /* root target relation (partitioned
    928+
    * table root) */
    917929
    List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */
    918930
    EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */
    919931
    bool fireBSTriggers; /* do we need to fire stmt triggers? */

    ‎src/include/nodes/plannodes.h

    Lines changed: 12 additions & 1 deletion
    Original file line numberDiff line numberDiff line change
    @@ -65,9 +65,19 @@ typedef struct PlannedStmt
    6565
    /* rtable indexes of target relations for INSERT/UPDATE/DELETE */
    6666
    List *resultRelations; /* integer list of RT indexes, or NIL */
    6767

    68-
    /* rtable indexes of non-leaf target relations for INSERT/UPDATE/DELETE */
    68+
    /*
    69+
    * rtable indexes of non-leaf target relations for UPDATE/DELETE on
    70+
    * all the partitioned table mentioned in the query.
    71+
    */
    6972
    List *nonleafResultRelations;
    7073

    74+
    /*
    75+
    * rtable indexes of root target relations for UPDATE/DELETE; this list
    76+
    * maintains a subset of the RT indexes in nonleafResultRelations,
    77+
    * indicating the roots of the respective partition hierarchies.
    78+
    */
    79+
    List *rootResultRelations;
    80+
    7181
    List *subplans; /* Plan trees for SubPlan expressions; note
    7282
    * that some could be NULL */
    7383

    @@ -211,6 +221,7 @@ typedef struct ModifyTable
    211221
    List *partitioned_rels;
    212222
    List *resultRelations; /* integer list of RT indexes */
    213223
    int resultRelIndex; /* index of first resultRel in plan's list */
    224+
    int rootResultRelIndex; /* index of the partitioned table root */
    214225
    List *plans; /* plan(s) producing source data */
    215226
    List *withCheckOptionLists; /* per-target-table WCO lists */
    216227
    List *returningLists; /* per-target-table RETURNING tlists */

    ‎src/include/nodes/relation.h

    Lines changed: 1 addition & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -108,6 +108,7 @@ typedef struct PlannerGlobal
    108108
    List *resultRelations; /* "flat" list of integer RT indexes */
    109109

    110110
    List *nonleafResultRelations; /* "flat" list of integer RT indexes */
    111+
    List *rootResultRelations; /* "flat" list of integer RT indexes */
    111112

    112113
    List *relationOids; /* OIDs of relations the plan depends on */
    113114

    0 commit comments

    Comments
     (0)
    0