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

Commit8f4a6b9

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 parentdf73ca3 commit8f4a6b9

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
@@ -2860,10 +2860,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
28602860
* must be converted, and
28612861
* - the root partitioned table used for tuple routing.
28622862
*
2863-
* If it's a partitioned table, the root partitiondoesn't appear
2864-
* elsewhere in the plan and its RT index is given explicitly in
2865-
* node->rootRelation. Otherwise (i.e. table inheritance)the target
2866-
*relationis thefirst relation in the node->resultRelations list.
2863+
* If it's a partitionedor inheritedtable, the root partitionor
2864+
*appendrel RTE doesn't appearelsewhere in the plan and its RT index is
2865+
*given explicitly innode->rootRelation. Otherwise,the target relation
2866+
* is thesole relation in the node->resultRelations list.
28672867
*----------
28682868
*/
28692869
if (node->rootRelation>0)
@@ -2874,6 +2874,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
28742874
}
28752875
else
28762876
{
2877+
Assert(list_length(node->resultRelations)==1);
28772878
mtstate->rootResultRelInfo=mtstate->resultRelInfo;
28782879
ExecInitResultRelation(estate,mtstate->resultRelInfo,
28792880
linitial_int(node->resultRelations));

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

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

1728+
/* Pass the root result rel forward to the executor. */
1729+
rootRelation=parse->resultRelation;
1730+
17281731
/* Add only leaf children to ModifyTable. */
17291732
while ((resultRelation=bms_next_member(root->leaf_result_relids,
17301733
resultRelation)) >=0)
@@ -1809,6 +1812,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
18091812
else
18101813
{
18111814
/* Single-relation INSERT/UPDATE/DELETE. */
1815+
rootRelation=0;/* there's no separate root rel */
18121816
resultRelations=list_make1_int(parse->resultRelation);
18131817
if (parse->commandType==CMD_UPDATE)
18141818
updateColnosLists=list_make1(root->update_colnos);
@@ -1818,16 +1822,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
18181822
returningLists=list_make1(parse->returningList);
18191823
}
18201824

1821-
/*
1822-
* If target is a partition root table, we need to mark the
1823-
* ModifyTable node appropriately for that.
1824-
*/
1825-
if (rt_fetch(parse->resultRelation,parse->rtable)->relkind==
1826-
RELKIND_PARTITIONED_TABLE)
1827-
rootRelation=parse->resultRelation;
1828-
else
1829-
rootRelation=0;
1830-
18311825
/*
18321826
* If there was a FOR [KEY] UPDATE/SHARE clause, the LockRows node
18331827
* 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
@@ -3609,7 +3609,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel,
36093609
* 'operation' is the operation type
36103610
* 'canSetTag' is true if we set the command tag/es_processed
36113611
* 'nominalRelation' is the parent RT index for use of EXPLAIN
3612-
* 'rootRelation' is the partitioned table rootRT index, or 0 if none
3612+
* 'rootRelation' is the partitioned/inherited table rootRTI, or 0 if none
36133613
* 'partColsUpdated' is true if any partitioning columns are being updated,
36143614
*either from the target relation or a descendent partitioned table.
36153615
* '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
@@ -1884,7 +1884,7 @@ typedef struct ModifyTablePath
18841884
CmdTypeoperation;/* INSERT, UPDATE, or DELETE */
18851885
boolcanSetTag;/* do we set the command tag/es_processed? */
18861886
IndexnominalRelation;/* Parent RT index for use of EXPLAIN */
1887-
IndexrootRelation;/* Root RT index, iftarget ispartitioned */
1887+
IndexrootRelation;/* Root RT index, if partitioned/inherited */
18881888
boolpartColsUpdated;/* some part key in hierarchy updated? */
18891889
List*resultRelations;/* integer list of RT indexes */
18901890
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
@@ -204,11 +204,12 @@ typedef struct ProjectSet
204204
*Apply rows produced by outer plan to result table(s),
205205
*by inserting, updating, or deleting.
206206
*
207-
* If the originally named target table is a partitioned table, both
208-
* nominalRelation and rootRelation contain the RT index of the partition
209-
* root, which is not otherwise mentioned in the plan. Otherwise rootRelation
210-
* is zero. However, nominalRelation will always be set, as it's the rel that
211-
* EXPLAIN should claim is the INSERT/UPDATE/DELETE target.
207+
* If the originally named target table is a partitioned table or inheritance
208+
* tree, both nominalRelation and rootRelation contain the RT index of the
209+
* partition root or appendrel RTE, which is not otherwise mentioned in the
210+
* plan. Otherwise rootRelation is zero. However, nominalRelation will
211+
* always be set, as it's the rel that EXPLAIN should claim is the
212+
* INSERT/UPDATE/DELETE target.
212213
*
213214
* Note that rowMarks and epqParam are presumed to be valid for all the
214215
* table(s); they can't contain any info that varies across tables.
@@ -220,7 +221,7 @@ typedef struct ModifyTable
220221
CmdTypeoperation;/* INSERT, UPDATE, or DELETE */
221222
boolcanSetTag;/* do we set the command tag/es_processed? */
222223
IndexnominalRelation;/* Parent RT index for use of EXPLAIN */
223-
IndexrootRelation;/* Root RT index, iftarget ispartitioned */
224+
IndexrootRelation;/* Root RT index, if partitioned/inherited */
224225
boolpartColsUpdated;/* some part key in hierarchy updated? */
225226
List*resultRelations;/* integer list of RT indexes */
226227
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