This is an automated email from the ASF dual-hosted git repository. tuhaihe pushed a commit to branch REL_2_STABLE in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 7637edb7411d4a6092cf137019422e79eb8aa93e Author: Tom Lane <[email protected]> AuthorDate: Tue Oct 24 14:48:28 2023 -0400 Fix problems when a plain-inheritance parent table is excluded. When an UPDATE/DELETE/MERGE's target table is an old-style inheritance tree, it's possible for the parent to get excluded from the plan while some children are not. (I believe this is only possible if we can prove that a CHECK ... NO INHERIT constraint on the parent contradicts the query WHERE clause, so it's a very unusual case.) In such a case, ExecInitModifyTable mistakenly concluded that the first surviving child is the target table, leading to at least two bugs: 1. The wrong table's statement-level triggers would get fired. 2. In v16 and up, it was possible to fail with "invalid perminfoindex 0 in RTE with relid nnnn" due to the child RTE not having permissions data included in the query plan. This was hard to reproduce reliably because it did not occur unless the update triggered some non-HOT index updates. In v14 and up, this is easy to fix by defining ModifyTable.rootRelation to be the parent RTE in plain inheritance as well as partitioned cases. While the wrong-triggers bug also appears in older branches, the relevant code in both the planner and executor is quite a bit different, so it would take a good deal of effort to develop and test a suitable patch. Given the lack of field complaints about the trigger issue, I'll desist for now. (Patching v11 for this seems unwise anyway, given that it will have no more releases after next month.) Per bug #18147 from Hans Buschmann. Amit Langote and Tom Lane Discussion: https://postgr.es/m/[email protected] (cherry picked from commit f752045231ea1bbd4f9afaa7dfbbf123f8cd91e5) --- src/backend/executor/nodeModifyTable.c | 9 +++++---- src/backend/optimizer/plan/planner.c | 14 ++++---------- src/backend/optimizer/util/pathnode.c | 2 +- src/include/nodes/pathnodes.h | 2 +- src/include/nodes/plannodes.h | 13 +++++++------ src/test/regress/expected/inherit.out | 27 +++++++++++++++++++++++++++ src/test/regress/sql/inherit.sql | 19 +++++++++++++++++++ 7 files changed, 64 insertions(+), 22 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 4e718bd7b31..8c11a1e826e 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -3172,10 +3172,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) * must be converted, and * - the root partitioned table used for tuple routing. * - * If it's a partitioned table, the root partition doesn't appear - * elsewhere in the plan and its RT index is given explicitly in - * node->rootRelation. Otherwise (i.e. table inheritance) the target - * relation is the first relation in the node->resultRelations list. + * If it's a partitioned or inherited table, the root partition or + * appendrel RTE doesn't appear elsewhere in the plan and its RT index is + * given explicitly in node->rootRelation. Otherwise, the target relation + * is the sole relation in the node->resultRelations list. *---------- */ if (node->rootRelation > 0) @@ -3186,6 +3186,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } else { + Assert(list_length(node->resultRelations) == 1); mtstate->rootResultRelInfo = mtstate->resultRelInfo; ExecInitResultRelation(estate, mtstate->resultRelInfo, linitial_int(node->resultRelations)); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 7b55eded430..cdb2ed911e1 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2463,6 +2463,9 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) parse->resultRelation); int resultRelation = -1; + /* Pass the root result rel forward to the executor. */ + rootRelation = parse->resultRelation; + /* Add only leaf children to ModifyTable. */ while ((resultRelation = bms_next_member(root->leaf_result_relids, resultRelation)) >= 0) @@ -2547,6 +2550,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) else { /* Single-relation INSERT/UPDATE/DELETE. */ + rootRelation = 0; /* there's no separate root rel */ resultRelations = list_make1_int(parse->resultRelation); if (parse->commandType == CMD_UPDATE) updateColnosLists = list_make1(root->update_colnos); @@ -2556,16 +2560,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) returningLists = list_make1(parse->returningList); } - /* - * If target is a partition root table, we need to mark the - * ModifyTable node appropriately for that. - */ - if (rt_fetch(parse->resultRelation, parse->rtable)->relkind == - RELKIND_PARTITIONED_TABLE) - rootRelation = parse->resultRelation; - else - rootRelation = 0; - /* * If there was a FOR [KEY] UPDATE/SHARE clause, the LockRows node * will have dealt with fetching non-locked marked rows, else we diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 4648a2f2719..04db89e92f9 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -5815,7 +5815,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, * 'operation' is the operation type * 'canSetTag' is true if we set the command tag/es_processed * 'nominalRelation' is the parent RT index for use of EXPLAIN - * 'rootRelation' is the partitioned table root RT index, or 0 if none + * 'rootRelation' is the partitioned/inherited table root RTI, or 0 if none * 'partColsUpdated' is true if any partitioning columns are being updated, * either from the target relation or a descendent partitioned table. * 'resultRelations' is an integer list of actual RT indexes of target rel(s) diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 9281ea90729..d08a5841b18 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2369,7 +2369,7 @@ typedef struct ModifyTablePath CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ - Index rootRelation; /* Root RT index, if target is partitioned */ + Index rootRelation; /* Root RT index, if partitioned/inherited */ bool partColsUpdated; /* some part key in hierarchy updated? */ bool splitUpdate; /* if distribution key is updated */ List *resultRelations; /* integer list of RT indexes */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index dd214cb9996..5392015a738 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -389,11 +389,12 @@ typedef struct ProjectSet * Apply rows produced by outer plan to result table(s), * by inserting, updating, or deleting. * - * If the originally named target table is a partitioned table, both - * nominalRelation and rootRelation contain the RT index of the partition - * root, which is not otherwise mentioned in the plan. Otherwise rootRelation - * is zero. However, nominalRelation will always be set, as it's the rel that - * EXPLAIN should claim is the INSERT/UPDATE/DELETE target. + * If the originally named target table is a partitioned table or inheritance + * tree, both nominalRelation and rootRelation contain the RT index of the + * partition root or appendrel RTE, which is not otherwise mentioned in the + * plan. Otherwise rootRelation is zero. However, nominalRelation will + * always be set, as it's the rel that EXPLAIN should claim is the + * INSERT/UPDATE/DELETE target. * * Note that rowMarks and epqParam are presumed to be valid for all the * table(s); they can't contain any info that varies across tables. @@ -405,7 +406,7 @@ typedef struct ModifyTable CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ - Index rootRelation; /* Root RT index, if target is partitioned */ + Index rootRelation; /* Root RT index, if partitioned/inherited */ bool partColsUpdated; /* some part key in hierarchy updated? */ bool splitUpdate; /* if it's split update */ List *resultRelations; /* integer list of RT indexes */ diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 7d20cc2bdc4..e74710dd51d 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -539,6 +539,33 @@ CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); INSERT INTO z VALUES (NULL, 'text'); -- should fail ERROR: null value in column "aa" of relation "z" violates not-null constraint DETAIL: Failing row contains (null, text). +-- Check inherited UPDATE with first child excluded +create table some_tab (f1 int, f2 int, f3 int, check (f1 < 10) no inherit); +create table some_tab_child () inherits(some_tab); +insert into some_tab_child select i, i+1, 0 from generate_series(1,1000) i; +create index on some_tab_child(f1, f2); +-- while at it, also check that statement-level triggers fire +create function some_tab_stmt_trig_func() returns trigger as +$$begin raise notice 'updating some_tab'; return NULL; end;$$ +language plpgsql; +create trigger some_tab_stmt_trig + before update on some_tab execute function some_tab_stmt_trig_func(); +explain (costs off) +update some_tab set f3 = 11 where f1 = 12 and f2 = 13; + QUERY PLAN +------------------------------------------------------------------------------------ + Update on some_tab + Update on some_tab_child some_tab_1 + -> Result + -> Index Scan using some_tab_child_f1_f2_idx on some_tab_child some_tab_1 + Index Cond: ((f1 = 12) AND (f2 = 13)) +(5 rows) + +update some_tab set f3 = 11 where f1 = 12 and f2 = 13; +NOTICE: updating some_tab +drop table some_tab cascade; +NOTICE: drop cascades to table some_tab_child +drop function some_tab_stmt_trig_func(); -- Check inherited UPDATE with all children excluded create table some_tab (a int, b int) distributed randomly; create table some_tab_child () inherits (some_tab); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index e5f9b980776..c6acd74cb84 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -97,6 +97,25 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); INSERT INTO z VALUES (NULL, 'text'); -- should fail +-- Check inherited UPDATE with first child excluded +create table some_tab (f1 int, f2 int, f3 int, check (f1 < 10) no inherit); +create table some_tab_child () inherits(some_tab); +insert into some_tab_child select i, i+1, 0 from generate_series(1,1000) i; +create index on some_tab_child(f1, f2); +-- while at it, also check that statement-level triggers fire +create function some_tab_stmt_trig_func() returns trigger as +$$begin raise notice 'updating some_tab'; return NULL; end;$$ +language plpgsql; +create trigger some_tab_stmt_trig + before update on some_tab execute function some_tab_stmt_trig_func(); + +explain (costs off) +update some_tab set f3 = 11 where f1 = 12 and f2 = 13; +update some_tab set f3 = 11 where f1 = 12 and f2 = 13; + +drop table some_tab cascade; +drop function some_tab_stmt_trig_func(); + -- Check inherited UPDATE with all children excluded create table some_tab (a int, b int) distributed randomly; create table some_tab_child () inherits (some_tab); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
