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

Commitdc7521d

Browse files
committed
Fix planner's handling of RETURNING lists in writable CTEs.
setrefs.c failed to do "rtoffset" adjustment of Vars in RETURNING lists,which meant they were left with the wrong varnos when the RETURNING listwas in a subquery. That was never possible before writable CTEs, ofcourse, but now it's broken. The executor fails to notice any problembecause ExecEvalVar just references the ecxt_scantuple for any normalvarno; but EXPLAIN breaks when the varno is wrong, as illustrated in arecent complaint from Bartosz Dmytrak.Since the eventual rtoffset of the subquery is not known at the timewe are preparing its plan node, the previous scheme of executingset_returning_clause_references() at that time cannot handle thisadjustment. Fortunately, it turns out that we don't really need to do itthat way, because all the needed information is available during normalsetrefs.c execution; we just have to dig it out of the ModifyTable node.So, do that, and get rid of the kluge of early setrefs processing ofRETURNING lists. (This is a little bit of a cheat in the case of inheritedUPDATE/DELETE, because we are not passing a "root" struct that correspondsexactly to what the subplan was built with. But that doesn't matter, andanyway this is less ugly than early setrefs processing was.)Back-patch to 9.1, where the problem became possible to hit.
1 parentbf0d462 commitdc7521d

File tree

6 files changed

+118
-50
lines changed

6 files changed

+118
-50
lines changed

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

Lines changed: 2 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -4416,16 +4416,8 @@ make_modifytable(CmdType operation, bool canSetTag,
44164416
node->plan.lefttree=NULL;
44174417
node->plan.righttree=NULL;
44184418
node->plan.qual=NIL;
4419-
4420-
/*
4421-
* Set up the visible plan targetlist as being the same as the first
4422-
* RETURNING list.This is for the use of EXPLAIN; the executor won't pay
4423-
* any attention to the targetlist.
4424-
*/
4425-
if (returningLists)
4426-
node->plan.targetlist=copyObject(linitial(returningLists));
4427-
else
4428-
node->plan.targetlist=NIL;
4419+
/* setrefs.c will fill in the targetlist, if needed */
4420+
node->plan.targetlist=NIL;
44294421

44304422
node->operation=operation;
44314423
node->canSetTag=canSetTag;

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

Lines changed: 4 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -544,22 +544,10 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
544544
List*rowMarks;
545545

546546
/*
547-
* Deal with the RETURNING clause if any. It's convenient to pass
548-
* the returningList through setrefs.c now rather than at top
549-
* level (if we waited, handling inherited UPDATE/DELETE would be
550-
* much harder).
547+
* Set up the RETURNING list-of-lists, if needed.
551548
*/
552549
if (parse->returningList)
553-
{
554-
List*rlist;
555-
556-
Assert(parse->resultRelation);
557-
rlist=set_returning_clause_references(root->glob,
558-
parse->returningList,
559-
plan,
560-
parse->resultRelation);
561-
returningLists=list_make1(rlist);
562-
}
550+
returningLists=list_make1(parse->returningList);
563551
else
564552
returningLists=NIL;
565553

@@ -795,15 +783,8 @@ inheritance_planner(PlannerInfo *root)
795783

796784
/* Build list of per-relation RETURNING targetlists */
797785
if (parse->returningList)
798-
{
799-
List*rlist;
800-
801-
rlist=set_returning_clause_references(root->glob,
802-
subroot.parse->returningList,
803-
subplan,
804-
appinfo->child_relid);
805-
returningLists=lappend(returningLists,rlist);
806-
}
786+
returningLists=lappend(returningLists,
787+
subroot.parse->returningList);
807788
}
808789

809790
/* Mark result as unordered (probably unnecessary) */

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

Lines changed: 57 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -116,6 +116,11 @@ static Node *fix_upper_expr(PlannerGlobal *glob,
116116
intrtoffset);
117117
staticNode*fix_upper_expr_mutator(Node*node,
118118
fix_upper_expr_context*context);
119+
staticList*set_returning_clause_references(PlannerGlobal*glob,
120+
List*rlist,
121+
Plan*topplan,
122+
IndexresultRelation,
123+
intrtoffset);
119124
staticboolfix_opfuncids_walker(Node*node,void*context);
120125
staticboolextract_query_dependencies_walker(Node*node,
121126
PlannerGlobal*context);
@@ -530,13 +535,50 @@ set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset)
530535
{
531536
ModifyTable*splan= (ModifyTable*)plan;
532537

533-
/*
534-
* planner.c already called set_returning_clause_references,
535-
* so we should not process either the targetlist or the
536-
* returningLists.
537-
*/
538+
Assert(splan->plan.targetlist==NIL);
538539
Assert(splan->plan.qual==NIL);
539540

541+
if (splan->returningLists)
542+
{
543+
List*newRL=NIL;
544+
ListCell*lcrl,
545+
*lcrr,
546+
*lcp;
547+
548+
/*
549+
* Pass each per-subplan returningList through
550+
* set_returning_clause_references().
551+
*/
552+
Assert(list_length(splan->returningLists)==list_length(splan->resultRelations));
553+
Assert(list_length(splan->returningLists)==list_length(splan->plans));
554+
forthree(lcrl,splan->returningLists,
555+
lcrr,splan->resultRelations,
556+
lcp,splan->plans)
557+
{
558+
List*rlist= (List*)lfirst(lcrl);
559+
Indexresultrel=lfirst_int(lcrr);
560+
Plan*subplan= (Plan*)lfirst(lcp);
561+
562+
rlist=set_returning_clause_references(glob,
563+
rlist,
564+
subplan,
565+
resultrel,
566+
rtoffset);
567+
newRL=lappend(newRL,rlist);
568+
}
569+
splan->returningLists=newRL;
570+
571+
/*
572+
* Set up the visible plan targetlist as being the same as
573+
* the first RETURNING list. This is for the use of
574+
* EXPLAIN; the executor won't pay any attention to the
575+
* targetlist. We postpone this step until here so that
576+
* we don't have to do set_returning_clause_references()
577+
* twice on identical targetlists.
578+
*/
579+
splan->plan.targetlist=copyObject(linitial(newRL));
580+
}
581+
540582
foreach(l,splan->resultRelations)
541583
{
542584
lfirst_int(l)+=rtoffset;
@@ -1463,6 +1505,7 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
14631505
if (var->varno==context->acceptable_rel)
14641506
{
14651507
var=copyVar(var);
1508+
var->varno+=context->rtoffset;
14661509
if (var->varnoold>0)
14671510
var->varnoold+=context->rtoffset;
14681511
return (Node*)var;
@@ -1619,25 +1662,26 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
16191662
* entries in the top subplan's targetlist. Vars referencing the result
16201663
* table should be left alone, however (the executor will evaluate them
16211664
* using the actual heap tuple, after firing triggers if any).In the
1622-
* adjusted RETURNING list, result-table Vars willstillhave their
1623-
*originalvarno, but Vars for other rels will have varno OUTER.
1665+
* adjusted RETURNING list, result-table Vars will have their original
1666+
* varno (plus rtoffset), but Vars for other rels will have varno OUTER.
16241667
*
16251668
* We also must perform opcode lookup and add regclass OIDs to
16261669
* glob->relationOids.
16271670
*
16281671
* 'rlist': the RETURNING targetlist to be fixed
16291672
* 'topplan': the top subplan node that will be just below the ModifyTable
1630-
*node (note it's not yet passed throughset_plan_references)
1673+
*node (note it's not yet passed throughset_plan_refs)
16311674
* 'resultRelation': RT index of the associated result relation
1675+
* 'rtoffset': how much to increment varnos by
16321676
*
1633-
* Note: we assume that result relations will have rtoffset zero, that is,
1634-
* they are not coming from a subplan.
1677+
* Note: resultRelation is not yet adjusted by rtoffset.
16351678
*/
1636-
List*
1679+
staticList*
16371680
set_returning_clause_references(PlannerGlobal*glob,
16381681
List*rlist,
16391682
Plan*topplan,
1640-
IndexresultRelation)
1683+
IndexresultRelation,
1684+
intrtoffset)
16411685
{
16421686
indexed_tlist*itlist;
16431687

@@ -1662,7 +1706,7 @@ set_returning_clause_references(PlannerGlobal *glob,
16621706
itlist,
16631707
NULL,
16641708
resultRelation,
1665-
0);
1709+
rtoffset);
16661710

16671711
pfree(itlist);
16681712

‎src/include/optimizer/planmain.h

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -122,10 +122,6 @@ extern Plan *set_plan_references(PlannerGlobal *glob,
122122
Plan*plan,
123123
List*rtable,
124124
List*rowmarks);
125-
externList*set_returning_clause_references(PlannerGlobal*glob,
126-
List*rlist,
127-
Plan*topplan,
128-
IndexresultRelation);
129125
externvoidfix_opfuncids(Node*node);
130126
externvoidset_opfuncid(OpExpr*opexpr);
131127
externvoidset_sa_opfuncid(ScalarArrayOpExpr*opexpr);

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

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1856,6 +1856,55 @@ SELECT * FROM parent;
18561856
42 | new2
18571857
(5 rows)
18581858

1859+
-- check EXPLAIN VERBOSE for a wCTE with RETURNING
1860+
EXPLAIN (VERBOSE, COSTS OFF)
1861+
WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
1862+
DELETE FROM a USING wcte WHERE aa = q2;
1863+
QUERY PLAN
1864+
--------------------------------------------------
1865+
Delete on public.a
1866+
CTE wcte
1867+
-> Insert on public.int8_tbl
1868+
Output: int8_tbl.q2
1869+
-> Result
1870+
Output: 42::bigint, 47::bigint
1871+
-> Nested Loop
1872+
Output: public.a.ctid, wcte.*
1873+
Join Filter: (public.a.aa = wcte.q2)
1874+
-> Seq Scan on public.a
1875+
Output: public.a.ctid, public.a.aa
1876+
-> CTE Scan on wcte
1877+
Output: wcte.*, wcte.q2
1878+
-> Nested Loop
1879+
Output: public.a.ctid, wcte.*
1880+
Join Filter: (public.a.aa = wcte.q2)
1881+
-> Seq Scan on public.b a
1882+
Output: public.a.ctid, public.a.aa
1883+
-> CTE Scan on wcte
1884+
Output: wcte.*, wcte.q2
1885+
-> Nested Loop
1886+
Output: public.a.ctid, wcte.*
1887+
Join Filter: (public.a.aa = wcte.q2)
1888+
-> Seq Scan on public.c a
1889+
Output: public.a.ctid, public.a.aa
1890+
-> CTE Scan on wcte
1891+
Output: wcte.*, wcte.q2
1892+
-> Nested Loop
1893+
Output: public.a.ctid, wcte.*
1894+
Join Filter: (public.a.aa = wcte.q2)
1895+
-> Seq Scan on public.d a
1896+
Output: public.a.ctid, public.a.aa
1897+
-> CTE Scan on wcte
1898+
Output: wcte.*, wcte.q2
1899+
-> Nested Loop
1900+
Output: public.a.ctid, wcte.*
1901+
Join Filter: (public.a.aa = wcte.q2)
1902+
-> Seq Scan on public.inhe a
1903+
Output: public.a.ctid, public.a.aa
1904+
-> CTE Scan on wcte
1905+
Output: wcte.*, wcte.q2
1906+
(41 rows)
1907+
18591908
-- error cases
18601909
-- data-modifying WITH tries to use its own output
18611910
WITH RECURSIVE t AS (

‎src/test/regress/sql/with.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -791,6 +791,12 @@ DELETE FROM parent USING wcte WHERE id = newid;
791791

792792
SELECT*FROM parent;
793793

794+
-- check EXPLAIN VERBOSE for a wCTE with RETURNING
795+
796+
EXPLAIN (VERBOSE, COSTS OFF)
797+
WITH wcteAS (INSERT INTO int8_tblVALUES (42,47 ) RETURNING q2 )
798+
DELETEFROM a USING wcteWHERE aa= q2;
799+
794800
-- error cases
795801

796802
-- data-modifying WITH tries to use its own output

0 commit comments

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