Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit1268e73

Browse files
committed
Fix problems when a plain-inheritance parent table is excluded.
When an UPDATE/DELETE/MERGE's target table is an old-styleinheritance tree, it's possible for the parent to get excludedfrom the plan while some children are not. (I believe this isonly possible if we can prove that a CHECK ... NO INHERITconstraint on the parent contradicts the query WHERE clause,so it's a very unusual case.) In such a case, ExecInitModifyTablemistakenly concluded that the first surviving child is the targettable, 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 perminfoindex0 in RTE with relid nnnn" due to the child RTE not having permissionsdata included in the query plan. This was hard to reproduce reliablybecause it did not occur unless the update triggered some non-HOTindex updates.In v14 and up, this is easy to fix by defining ModifyTable.rootRelationto be the parent RTE in plain inheritance as well as partitioned cases.While the wrong-triggers bug also appears in older branches, therelevant code in both the planner and executor is quite a bitdifferent, so it would take a good deal of effort to develop andtest a suitable patch. Given the lack of field complaints about thetrigger issue, I'll desist for now. (Patching v11 for this seemsunwise anyway, given that it will have no more releases after nextmonth.)Per bug #18147 from Hans Buschmann.Amit Langote and Tom LaneDiscussion:https://postgr.es/m/18147-6fc796538913ee88@postgresql.org
1 parentbe25029 commit1268e73

File tree

7 files changed

+64
-22
lines changed

7 files changed

+64
-22
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3947,10 +3947,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
39473947
* must be converted, and
39483948
* - the root partitioned table used for tuple routing.
39493949
*
3950-
* If it's a partitioned table, the root partitiondoesn't appear
3951-
* elsewhere in the plan and its RT index is given explicitly in
3952-
* node->rootRelation. Otherwise (i.e. table inheritance)the target
3953-
*relationis thefirst relation in the node->resultRelations list.
3950+
* If it's a partitionedor inheritedtable, the root partitionor
3951+
*appendrel RTE doesn't appearelsewhere in the plan and its RT index is
3952+
*given explicitly innode->rootRelation. Otherwise,the target relation
3953+
* is thesole relation in the node->resultRelations list.
39543954
*----------
39553955
*/
39563956
if (node->rootRelation>0)
@@ -3961,6 +3961,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
39613961
}
39623962
else
39633963
{
3964+
Assert(list_length(node->resultRelations)==1);
39643965
mtstate->rootResultRelInfo=mtstate->resultRelInfo;
39653966
ExecInitResultRelation(estate,mtstate->resultRelInfo,
39663967
linitial_int(node->resultRelations));

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

Lines changed: 4 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1755,6 +1755,9 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
17551755
parse->resultRelation);
17561756
intresultRelation=-1;
17571757

1758+
/* Pass the root result rel forward to the executor. */
1759+
rootRelation=parse->resultRelation;
1760+
17581761
/* Add only leaf children to ModifyTable. */
17591762
while ((resultRelation=bms_next_member(root->leaf_result_relids,
17601763
resultRelation)) >=0)
@@ -1878,6 +1881,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
18781881
else
18791882
{
18801883
/* Single-relation INSERT/UPDATE/DELETE/MERGE. */
1884+
rootRelation=0;/* there's no separate root rel */
18811885
resultRelations=list_make1_int(parse->resultRelation);
18821886
if (parse->commandType==CMD_UPDATE)
18831887
updateColnosLists=list_make1(root->update_colnos);
@@ -1889,16 +1893,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
18891893
mergeActionLists=list_make1(parse->mergeActionList);
18901894
}
18911895

1892-
/*
1893-
* If target is a partition root table, we need to mark the
1894-
* ModifyTable node appropriately for that.
1895-
*/
1896-
if (rt_fetch(parse->resultRelation,parse->rtable)->relkind==
1897-
RELKIND_PARTITIONED_TABLE)
1898-
rootRelation=parse->resultRelation;
1899-
else
1900-
rootRelation=0;
1901-
19021896
/*
19031897
* If there was a FOR [KEY] UPDATE/SHARE clause, the LockRows node
19041898
* will have dealt with fetching non-locked marked rows, else we

‎src/backend/optimizer/util/pathnode.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3621,7 +3621,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel,
36213621
* 'operation' is the operation type
36223622
* 'canSetTag' is true if we set the command tag/es_processed
36233623
* 'nominalRelation' is the parent RT index for use of EXPLAIN
3624-
* 'rootRelation' is the partitioned table rootRT index, or 0 if none
3624+
* 'rootRelation' is the partitioned/inherited table rootRTI, or 0 if none
36253625
* 'partColsUpdated' is true if any partitioning columns are being updated,
36263626
*either from the target relation or a descendent partitioned table.
36273627
* 'resultRelations' is an integer list of actual RT indexes of target rel(s)

‎src/include/nodes/pathnodes.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1895,7 +1895,7 @@ typedef struct ModifyTablePath
18951895
CmdTypeoperation;/* INSERT, UPDATE, DELETE, or MERGE */
18961896
boolcanSetTag;/* do we set the command tag/es_processed? */
18971897
IndexnominalRelation;/* Parent RT index for use of EXPLAIN */
1898-
IndexrootRelation;/* Root RT index, iftarget ispartitioned */
1898+
IndexrootRelation;/* Root RT index, if partitioned/inherited */
18991899
boolpartColsUpdated;/* some part key in hierarchy updated? */
19001900
List*resultRelations;/* integer list of RT indexes */
19011901
List*updateColnosLists;/* per-target-table update_colnos lists */

‎src/include/nodes/plannodes.h

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -205,11 +205,12 @@ typedef struct ProjectSet
205205
*Apply rows produced by outer plan to result table(s),
206206
*by inserting, updating, or deleting.
207207
*
208-
* If the originally named target table is a partitioned table, both
209-
* nominalRelation and rootRelation contain the RT index of the partition
210-
* root, which is not otherwise mentioned in the plan. Otherwise rootRelation
211-
* is zero. However, nominalRelation will always be set, as it's the rel that
212-
* EXPLAIN should claim is the INSERT/UPDATE/DELETE/MERGE target.
208+
* If the originally named target table is a partitioned table or inheritance
209+
* tree, both nominalRelation and rootRelation contain the RT index of the
210+
* partition root or appendrel RTE, which is not otherwise mentioned in the
211+
* plan. Otherwise rootRelation is zero. However, nominalRelation will
212+
* always be set, as it's the rel that EXPLAIN should claim is the
213+
* INSERT/UPDATE/DELETE/MERGE target.
213214
*
214215
* Note that rowMarks and epqParam are presumed to be valid for all the
215216
* table(s); they can't contain any info that varies across tables.
@@ -221,7 +222,7 @@ typedef struct ModifyTable
221222
CmdTypeoperation;/* INSERT, UPDATE, DELETE, or MERGE */
222223
boolcanSetTag;/* do we set the command tag/es_processed? */
223224
IndexnominalRelation;/* Parent RT index for use of EXPLAIN */
224-
IndexrootRelation;/* Root RT index, iftarget ispartitioned */
225+
IndexrootRelation;/* Root RT index, if partitioned/inherited */
225226
boolpartColsUpdated;/* some part key in hierarchy updated? */
226227
List*resultRelations;/* integer list of RT indexes */
227228
List*updateColnosLists;/* per-target-table update_colnos lists */

‎src/test/regress/expected/inherit.out

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -539,6 +539,33 @@ CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
539539
INSERT INTO z VALUES (NULL, 'text'); -- should fail
540540
ERROR: null value in column "aa" of relation "z" violates not-null constraint
541541
DETAIL: Failing row contains (null, text).
542+
-- Check inherited UPDATE with first child excluded
543+
create table some_tab (f1 int, f2 int, f3 int, check (f1 < 10) no inherit);
544+
create table some_tab_child () inherits(some_tab);
545+
insert into some_tab_child select i, i+1, 0 from generate_series(1,1000) i;
546+
create index on some_tab_child(f1, f2);
547+
-- while at it, also check that statement-level triggers fire
548+
create function some_tab_stmt_trig_func() returns trigger as
549+
$$begin raise notice 'updating some_tab'; return NULL; end;$$
550+
language plpgsql;
551+
create trigger some_tab_stmt_trig
552+
before update on some_tab execute function some_tab_stmt_trig_func();
553+
explain (costs off)
554+
update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
555+
QUERY PLAN
556+
------------------------------------------------------------------------------------
557+
Update on some_tab
558+
Update on some_tab_child some_tab_1
559+
-> Result
560+
-> Index Scan using some_tab_child_f1_f2_idx on some_tab_child some_tab_1
561+
Index Cond: ((f1 = 12) AND (f2 = 13))
562+
(5 rows)
563+
564+
update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
565+
NOTICE: updating some_tab
566+
drop table some_tab cascade;
567+
NOTICE: drop cascades to table some_tab_child
568+
drop function some_tab_stmt_trig_func();
542569
-- Check inherited UPDATE with all children excluded
543570
create table some_tab (a int, b int);
544571
create table some_tab_child () inherits (some_tab);

‎src/test/regress/sql/inherit.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,6 +97,25 @@ SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
9797
CREATE TEMP TABLE z (bTEXT,PRIMARY KEY(aa, b)) inherits (a);
9898
INSERT INTO zVALUES (NULL,'text');-- should fail
9999

100+
-- Check inherited UPDATE with first child excluded
101+
createtablesome_tab (f1int, f2int, f3int,check (f1<10) no inherit);
102+
createtablesome_tab_child () inherits(some_tab);
103+
insert into some_tab_childselect i, i+1,0from generate_series(1,1000) i;
104+
createindexon some_tab_child(f1, f2);
105+
-- while at it, also check that statement-level triggers fire
106+
createfunctionsome_tab_stmt_trig_func() returns triggeras
107+
$$begin raise notice'updating some_tab'; returnNULL; end;$$
108+
language plpgsql;
109+
createtriggersome_tab_stmt_trig
110+
beforeupdateon some_tab execute function some_tab_stmt_trig_func();
111+
112+
explain (costs off)
113+
update some_tabset f3=11where f1=12and f2=13;
114+
update some_tabset f3=11where f1=12and f2=13;
115+
116+
droptable some_tab cascade;
117+
dropfunction some_tab_stmt_trig_func();
118+
100119
-- Check inherited UPDATE with all children excluded
101120
createtablesome_tab (aint, bint);
102121
createtablesome_tab_child () inherits (some_tab);

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp