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

Commitce4f46f

Browse files
committed
Change mechanism to set up source targetlist in MERGE
We were setting MERGE source subplan's targetlist by expanding theindividual attributes of the source relation completely, early in theparse analysis phase. This failed to work when the condition of anaction included a whole-row reference, causing setrefs.c to error outwith ERROR: variable not found in subplan target listsbecause at that point there is nothing to resolve the whole-rowreference with. We can fix this by having preprocess_targetlist expandthe source targetlist for Vars required from the source rel by allactions. Moreover, by using this expansion mechanism we can do awaywith the targetlist expansion in transformMergeStmt, which is goodbecause then we no longer pull in columns that aren't needed foranything.Add a test case for the problem.While at it, remove some redundant code in preprocess_targetlist():MERGE was doing separately what is already being done for UPDATE/DELETE,so we can just rely on the latter and remove the former. (The handlingof inherited rels was different for MERGE, but that was a no-longer-necessary hack.)Fix outdated, related comments for fix_join_expr also.Author: Richard Guo <guofenglinux@gmail.com>Author: Álvaro Herrera <alvherre@alvh.no-ip.org>Reported-by: Joe Wildish <joe@lateraljoin.com>Discussion:https://postgr.es/m/fab3b90a-914d-46a9-beb0-df011ee39ee5@www.fastmail.com
1 parenta4b5754 commitce4f46f

File tree

6 files changed

+80
-27
lines changed

6 files changed

+80
-27
lines changed

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

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2748,7 +2748,7 @@ search_indexed_tlist_for_sortgroupref(Expr *node,
27482748
* relation target lists. Also perform opcode lookup and add
27492749
* regclass OIDs to root->glob->relationOids.
27502750
*
2751-
* This is used inthree different scenarios:
2751+
* This is used infour different scenarios:
27522752
* 1) a normal join clause, where all the Vars in the clause *must* be
27532753
* replaced by OUTER_VAR or INNER_VAR references. In this case
27542754
* acceptable_rel should be zero so that any failure to match a Var will be
@@ -2763,6 +2763,11 @@ search_indexed_tlist_for_sortgroupref(Expr *node,
27632763
* to-be-updated relation) alone. Correspondingly inner_itlist is to be
27642764
* EXCLUDED elements, outer_itlist = NULL and acceptable_rel the target
27652765
* relation.
2766+
* 4) MERGE. In this case, references to the source relation are to be
2767+
* replaced with INNER_VAR references, leaving Vars of the target
2768+
* relation (the to-be-modified relation) alone. So inner_itlist is to be
2769+
* the source relation elements, outer_itlist = NULL and acceptable_rel
2770+
* the target relation.
27662771
*
27672772
* 'clauses' is the targetlist or list of join clauses
27682773
* 'outer_itlist' is the indexed target list of the outer join relation,

‎src/backend/optimizer/prep/preptlist.c

Lines changed: 44 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -107,14 +107,15 @@ preprocess_targetlist(PlannerInfo *root)
107107
root->update_colnos=extract_update_targetlist_colnos(tlist);
108108

109109
/*
110-
* For non-inherited UPDATE/DELETE, register any junk column(s) needed to
111-
* allow the executor to identify the rows to be updated or deleted. In
112-
* the inheritance case, we do nothing now, leaving this to be dealt with
113-
* when expand_inherited_rtentry() makes the leaf target relations. (But
114-
* there might not be any leaf target relations, in which case we must do
115-
* this in distribute_row_identity_vars().)
110+
* For non-inherited UPDATE/DELETE/MERGE, register any junk column(s)
111+
*needed toallow the executor to identify the rows to be updated or
112+
*deleted. Inthe inheritance case, we do nothing now, leaving this to
113+
*be dealt withwhen expand_inherited_rtentry() makes the leaf target
114+
*relations. (Butthere might not be any leaf target relations, in which
115+
*case we must dothis in distribute_row_identity_vars().)
116116
*/
117-
if ((command_type==CMD_UPDATE||command_type==CMD_DELETE)&&
117+
if ((command_type==CMD_UPDATE||command_type==CMD_DELETE||
118+
command_type==CMD_MERGE)&&
118119
!target_rte->inh)
119120
{
120121
/* row-identity logic expects to add stuff to processed_tlist */
@@ -125,23 +126,15 @@ preprocess_targetlist(PlannerInfo *root)
125126
}
126127

127128
/*
128-
* For MERGE we need to handle the target list for the target relation,
129-
* and also target list for each action (only INSERT/UPDATE matter).
129+
* For MERGE we also need to handle the target list for each INSERT and
130+
* UPDATE action separately. In addition, we examine the qual of each
131+
* action and add any Vars there (other than those of the target rel) to
132+
* the subplan targetlist.
130133
*/
131134
if (command_type==CMD_MERGE)
132135
{
133136
ListCell*l;
134137

135-
/*
136-
* For MERGE, add any junk column(s) needed to allow the executor to
137-
* identify the rows to be inserted or updated.
138-
*/
139-
root->processed_tlist=tlist;
140-
add_row_identity_columns(root,result_relation,
141-
target_rte,target_relation);
142-
143-
tlist=root->processed_tlist;
144-
145138
/*
146139
* For MERGE, handle targetlist of each MergeAction separately. Give
147140
* the same treatment to MergeAction->targetList as we would have
@@ -151,13 +144,45 @@ preprocess_targetlist(PlannerInfo *root)
151144
foreach(l,parse->mergeActionList)
152145
{
153146
MergeAction*action= (MergeAction*)lfirst(l);
147+
List*vars;
148+
ListCell*l2;
154149

155150
if (action->commandType==CMD_INSERT)
156151
action->targetList=expand_insert_targetlist(action->targetList,
157152
target_relation);
158153
elseif (action->commandType==CMD_UPDATE)
159154
action->updateColnos=
160155
extract_update_targetlist_colnos(action->targetList);
156+
157+
/*
158+
* Add resjunk entries for any Vars used in each action's
159+
* targetlist and WHEN condition that belong to relations other
160+
* than target. Note that aggregates, window functions and
161+
* placeholder vars are not possible anywhere in MERGE's WHEN
162+
* clauses. (PHVs may be added later, but they don't concern us
163+
* here.)
164+
*/
165+
vars=pull_var_clause((Node*)
166+
list_concat_copy((List*)action->qual,
167+
action->targetList),
168+
0);
169+
foreach(l2,vars)
170+
{
171+
Var*var= (Var*)lfirst(l2);
172+
TargetEntry*tle;
173+
174+
if (IsA(var,Var)&&var->varno==result_relation)
175+
continue;/* don't need it */
176+
177+
if (tlist_member((Expr*)var,tlist))
178+
continue;/* already got it */
179+
180+
tle=makeTargetEntry((Expr*)var,
181+
list_length(tlist)+1,
182+
NULL, true);
183+
tlist=lappend(tlist,tle);
184+
}
185+
list_free(vars);
161186
}
162187
}
163188

‎src/backend/parser/parse_merge.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,6 @@
1818
#include"access/sysattr.h"
1919
#include"miscadmin.h"
2020
#include"nodes/makefuncs.h"
21-
#include"nodes/nodeFuncs.h"
2221
#include"parser/analyze.h"
2322
#include"parser/parse_collate.h"
2423
#include"parser/parsetree.h"
@@ -205,9 +204,11 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt)
205204
pstate->p_target_nsitem->p_names->aliasname),
206205
errdetail("The name is used both as MERGE target table and data source."));
207206

208-
qry->targetList=expandNSItemAttrs(pstate,nsitem,0, false,
209-
exprLocation(stmt->sourceRelation));
210-
207+
/*
208+
* There's no need for a targetlist here; it'll be set up by
209+
* preprocess_targetlist later.
210+
*/
211+
qry->targetList=NIL;
211212
qry->rtable=pstate->p_rtable;
212213

213214
/*

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -791,6 +791,19 @@ SELECT * FROM wq_target;
791791
1 | 299
792792
(1 row)
793793

794+
-- check source-side whole-row references
795+
BEGIN;
796+
MERGE INTO wq_target t
797+
USING wq_source s ON (t.tid = s.sid)
798+
WHEN matched and t = s or t.tid = s.sid THEN
799+
UPDATE SET balance = t.balance + s.balance;
800+
SELECT * FROM wq_target;
801+
tid | balance
802+
-----+---------
803+
1 | 399
804+
(1 row)
805+
806+
ROLLBACK;
794807
-- check if subqueries work in the conditions?
795808
MERGE INTO wq_target t
796809
USING wq_source s ON t.tid = s.sid

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2800,7 +2800,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
28002800
-> Result
28012801
Output: 1, 'cte_basic val'::text
28022802
-> Hash Right Join
2803-
Output: (0), ('merge source SubPlan'::text), m.ctid
2803+
Output:m.ctid,(0), ('merge source SubPlan'::text)
28042804
Hash Cond: (m.k = (0))
28052805
-> Seq Scan on public.m
28062806
Output: m.ctid, m.k
@@ -2847,7 +2847,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
28472847
Output: (cte_init.b || ' merge update'::text)
28482848
Filter: (cte_init.a = 1)
28492849
-> Hash Right Join
2850-
Output: (1), ('merge source InitPlan'::text), m.ctid
2850+
Output:m.ctid,(1), ('merge source InitPlan'::text)
28512851
Hash Cond: (m.k = (1))
28522852
-> Seq Scan on public.m
28532853
Output: m.ctid, m.k
@@ -2889,7 +2889,7 @@ WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text
28892889
-> CTE Scan on merge_source_cte merge_source_cte_2
28902890
Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
28912891
-> Hash Right Join
2892-
Output:merge_source_cte.a, merge_source_cte.b, m.ctid
2892+
Output:m.ctid, merge_source_cte.a, merge_source_cte.b
28932893
Hash Cond: (m.k = merge_source_cte.a)
28942894
-> Seq Scan on public.m
28952895
Output: m.ctid, m.k

‎src/test/regress/sql/merge.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -527,6 +527,15 @@ WHEN MATCHED AND t.balance = 199 OR s.balance > 100 THEN
527527
UPDATESET balance=t.balance+s.balance;
528528
SELECT*FROM wq_target;
529529

530+
-- check source-side whole-row references
531+
BEGIN;
532+
MERGE INTO wq_target t
533+
USING wq_source sON (t.tid=s.sid)
534+
WHEN matchedand t= sort.tid=s.sid THEN
535+
UPDATESET balance=t.balance+s.balance;
536+
SELECT*FROM wq_target;
537+
ROLLBACK;
538+
530539
-- check if subqueries work in the conditions?
531540
MERGE INTO wq_target t
532541
USING wq_source sONt.tid=s.sid

0 commit comments

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