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

Commit58947fb

Browse files
committed
Fix plan created for inherited UPDATE/DELETE with all tables excluded.
In the case where inheritance_planner() finds that every table hasbeen excluded by constraints, it thought it could get away withmaking a plan consisting of just a dummy Result node. While certainlythere's no updating or deleting to be done, this had two user-visibleproblems: the plan did not report the correct set of output columnswhen a RETURNING clause was present, and if there were anystatement-level triggers that should be fired, it didn't fire them.Hence, rather than only generating the dummy Result, we need tostick a valid ModifyTable node on top, which requires a tad moreeffort here.It's been broken this way for as long as inheritance_planner() hasknown about deleting excluded subplans at all (cf commit635d42e),so back-patch to all supported branches.Amit Langote and Tom Lane, per a report from Petr Fedorov.Discussion:https://postgr.es/m/5da6f0f0-1364-1876-6978-907678f89a3e@phystech.edu
1 parent398cc6f commit58947fb

File tree

5 files changed

+163
-21
lines changed

5 files changed

+163
-21
lines changed

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

Lines changed: 46 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1398,32 +1398,57 @@ inheritance_planner(PlannerInfo *root)
13981398
* to get control here.
13991399
*/
14001400

1401-
/*
1402-
* If we managed to exclude every child rel, return a dummy plan; it
1403-
* doesn't even need a ModifyTable node.
1404-
*/
14051401
if (subpaths==NIL)
14061402
{
1407-
set_dummy_rel_pathlist(final_rel);
1408-
return;
1409-
}
1403+
/*
1404+
* We managed to exclude every child rel, so generate a dummy path
1405+
* representing the empty set. Although it's clear that no data will
1406+
* be updated or deleted, we will still need to have a ModifyTable
1407+
* node so that any statement triggers are executed. (This could be
1408+
* cleaner if we fixed nodeModifyTable.c to support zero child nodes,
1409+
* but that probably wouldn't be a net win.)
1410+
*/
1411+
List*tlist;
1412+
Path*dummy_path;
14101413

1411-
/*
1412-
* Put back the final adjusted rtable into the master copy of the Query.
1413-
* (We mustn't do this if we found no non-excluded children.)
1414-
*/
1415-
parse->rtable=final_rtable;
1416-
root->simple_rel_array_size=save_rel_array_size;
1417-
root->simple_rel_array=save_rel_array;
1418-
/* Must reconstruct master's simple_rte_array, too */
1419-
root->simple_rte_array= (RangeTblEntry**)
1420-
palloc0((list_length(final_rtable)+1)*sizeof(RangeTblEntry*));
1421-
rti=1;
1422-
foreach(lc,final_rtable)
1414+
/* tlist processing never got done, either */
1415+
tlist=root->processed_tlist=preprocess_targetlist(root);
1416+
final_rel->reltarget=create_pathtarget(root,tlist);
1417+
1418+
/* Make a dummy path, cf set_dummy_rel_pathlist() */
1419+
dummy_path= (Path*)create_append_path(final_rel,NIL,
1420+
NULL,0,NIL);
1421+
1422+
/* These lists must be nonempty to make a valid ModifyTable node */
1423+
subpaths=list_make1(dummy_path);
1424+
subroots=list_make1(root);
1425+
resultRelations=list_make1_int(parse->resultRelation);
1426+
if (parse->withCheckOptions)
1427+
withCheckOptionLists=list_make1(parse->withCheckOptions);
1428+
if (parse->returningList)
1429+
returningLists=list_make1(parse->returningList);
1430+
}
1431+
else
14231432
{
1424-
RangeTblEntry*rte= (RangeTblEntry*)lfirst(lc);
1433+
/*
1434+
* Put back the final adjusted rtable into the master copy of the
1435+
* Query. (We mustn't do this if we found no non-excluded children,
1436+
* since we never saved an adjusted rtable at all.)
1437+
*/
1438+
parse->rtable=final_rtable;
1439+
root->simple_rel_array_size=save_rel_array_size;
1440+
root->simple_rel_array=save_rel_array;
1441+
1442+
/* Must reconstruct master's simple_rte_array, too */
1443+
root->simple_rte_array= (RangeTblEntry**)
1444+
palloc0((list_length(final_rtable)+1)*sizeof(RangeTblEntry*));
1445+
rti=1;
1446+
foreach(lc,final_rtable)
1447+
{
1448+
RangeTblEntry*rte=lfirst_node(RangeTblEntry,lc);
14251449

1426-
root->simple_rte_array[rti++]=rte;
1450+
root->simple_rte_array[rti++]=rte;
1451+
}
14271452
}
14281453

14291454
/*

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -539,6 +539,47 @@ 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" violates not-null constraint
541541
DETAIL: Failing row contains (null, text).
542+
-- Check inherited UPDATE with all children excluded
543+
create table some_tab (a int, b int);
544+
create table some_tab_child () inherits (some_tab);
545+
insert into some_tab_child values(1,2);
546+
explain (verbose, costs off)
547+
update some_tab set a = a + 1 where false;
548+
QUERY PLAN
549+
----------------------------------
550+
Update on public.some_tab
551+
Update on public.some_tab
552+
-> Result
553+
Output: (a + 1), b, ctid
554+
One-Time Filter: false
555+
(5 rows)
556+
557+
update some_tab set a = a + 1 where false;
558+
explain (verbose, costs off)
559+
update some_tab set a = a + 1 where false returning b, a;
560+
QUERY PLAN
561+
----------------------------------
562+
Update on public.some_tab
563+
Output: b, a
564+
Update on public.some_tab
565+
-> Result
566+
Output: (a + 1), b, ctid
567+
One-Time Filter: false
568+
(6 rows)
569+
570+
update some_tab set a = a + 1 where false returning b, a;
571+
b | a
572+
---+---
573+
(0 rows)
574+
575+
table some_tab;
576+
a | b
577+
---+---
578+
1 | 2
579+
(1 row)
580+
581+
drop table some_tab cascade;
582+
NOTICE: drop cascades to table some_tab_child
542583
-- Check UPDATE with inherited target and an inherited source table
543584
create temp table foo(f1 int, f2 int);
544585
create temp table foo2(f3 int) inherits (foo);

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1700,6 +1700,40 @@ drop table self_ref_trigger;
17001700
drop function self_ref_trigger_ins_func();
17011701
drop function self_ref_trigger_del_func();
17021702
--
1703+
-- Check that statement triggers work correctly even with all children excluded
1704+
--
1705+
create table stmt_trig_on_empty_upd (a int);
1706+
create table stmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd);
1707+
create function update_stmt_notice() returns trigger as $$
1708+
begin
1709+
raise notice 'updating %', TG_TABLE_NAME;
1710+
return null;
1711+
end;
1712+
$$ language plpgsql;
1713+
create trigger before_stmt_trigger
1714+
before update on stmt_trig_on_empty_upd
1715+
execute procedure update_stmt_notice();
1716+
create trigger before_stmt_trigger
1717+
before update on stmt_trig_on_empty_upd1
1718+
execute procedure update_stmt_notice();
1719+
-- inherited no-op update
1720+
update stmt_trig_on_empty_upd set a = a where false returning a+1 as aa;
1721+
NOTICE: updating stmt_trig_on_empty_upd
1722+
aa
1723+
----
1724+
(0 rows)
1725+
1726+
-- simple no-op update
1727+
update stmt_trig_on_empty_upd1 set a = a where false returning a+1 as aa;
1728+
NOTICE: updating stmt_trig_on_empty_upd1
1729+
aa
1730+
----
1731+
(0 rows)
1732+
1733+
drop table stmt_trig_on_empty_upd cascade;
1734+
NOTICE: drop cascades to table stmt_trig_on_empty_upd1
1735+
drop function update_stmt_notice();
1736+
--
17031737
-- Check that index creation (or DDL in general) is prohibited in a trigger
17041738
--
17051739
create table trigger_ddl_table (

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,6 +97,21 @@ 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 all children excluded
101+
createtablesome_tab (aint, bint);
102+
createtablesome_tab_child () inherits (some_tab);
103+
insert into some_tab_childvalues(1,2);
104+
105+
explain (verbose, costs off)
106+
update some_tabset a= a+1where false;
107+
update some_tabset a= a+1where false;
108+
explain (verbose, costs off)
109+
update some_tabset a= a+1where false returning b, a;
110+
update some_tabset a= a+1where false returning b, a;
111+
table some_tab;
112+
113+
droptable some_tab cascade;
114+
100115
-- Check UPDATE with inherited target and an inherited source table
101116
create temp table foo(f1int, f2int);
102117
create temp table foo2(f3int) inherits (foo);

‎src/test/regress/sql/triggers.sql

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1199,6 +1199,33 @@ drop table self_ref_trigger;
11991199
dropfunction self_ref_trigger_ins_func();
12001200
dropfunction self_ref_trigger_del_func();
12011201

1202+
--
1203+
-- Check that statement triggers work correctly even with all children excluded
1204+
--
1205+
1206+
createtablestmt_trig_on_empty_upd (aint);
1207+
createtablestmt_trig_on_empty_upd1 () inherits (stmt_trig_on_empty_upd);
1208+
createfunctionupdate_stmt_notice() returns triggeras $$
1209+
begin
1210+
raise notice'updating %', TG_TABLE_NAME;
1211+
returnnull;
1212+
end;
1213+
$$ language plpgsql;
1214+
createtriggerbefore_stmt_trigger
1215+
beforeupdateon stmt_trig_on_empty_upd
1216+
execute procedure update_stmt_notice();
1217+
createtriggerbefore_stmt_trigger
1218+
beforeupdateon stmt_trig_on_empty_upd1
1219+
execute procedure update_stmt_notice();
1220+
1221+
-- inherited no-op update
1222+
update stmt_trig_on_empty_updset a= awhere false returning a+1as aa;
1223+
-- simple no-op update
1224+
update stmt_trig_on_empty_upd1set a= awhere false returning a+1as aa;
1225+
1226+
droptable stmt_trig_on_empty_upd cascade;
1227+
dropfunction update_stmt_notice();
1228+
12021229
--
12031230
-- Check that index creation (or DDL in general) is prohibited in a trigger
12041231
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp