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

Commite3ac850

Browse files
committed
Support PlaceHolderVars in MERGE actions.
preprocess_targetlist thought PHVs couldn't appear here.It was mistaken, as per report from Önder Kalacı.Surveying other pull_var_clause calls, I noted no similar errors,but I did notice that qual_is_pushdown_safe's assertion about!contain_window_function was pointless, because the followingpull_var_clause call would complain about them anyway. In HEADonly, remove the redundant Assert and improve the commentary.Discussion:https://postgr.es/m/CACawEhUuum-gC_2S3sXLTcsk7bUSPSHOD+g1ZpfKaDK-KKPPWA@mail.gmail.com
1 parenta013738 commite3ac850

File tree

4 files changed

+41
-14
lines changed

4 files changed

+41
-14
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3839,18 +3839,16 @@ qual_is_pushdown_safe(Query *subquery, Index rti, RestrictInfo *rinfo,
38393839
contain_leaked_vars(qual))
38403840
return false;
38413841

3842-
/*
3843-
* It would be unsafe to push down window function calls, but at least for
3844-
* the moment we could never see any in a qual anyhow. (The same applies
3845-
* to aggregates, which we check for in pull_var_clause below.)
3846-
*/
3847-
Assert(!contain_window_function(qual));
3848-
38493842
/*
38503843
* Examine all Vars used in clause. Since it's a restriction clause, all
38513844
* such Vars must refer to subselect output columns ... unless this is
38523845
* part of a LATERAL subquery, in which case there could be lateral
38533846
* references.
3847+
*
3848+
* By omitting the relevant flags, this also gives us a cheap sanity check
3849+
* that no aggregates or window functions appear in the qual. Those would
3850+
* be unsafe to push down, but at least for the moment we could never see
3851+
* any in a qual anyhow.
38543852
*/
38553853
vars=pull_var_clause(qual,PVC_INCLUDE_PLACEHOLDERS);
38563854
foreach(vl,vars)

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

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -155,17 +155,15 @@ preprocess_targetlist(PlannerInfo *root)
155155
extract_update_targetlist_colnos(action->targetList);
156156

157157
/*
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.)
158+
* Add resjunk entries for any Vars and PlaceHolderVars used in
159+
* each action's targetlist and WHEN condition that belong to
160+
* relations other than the target. We don't expect to see any
161+
* aggregates or window functions here.
164162
*/
165163
vars=pull_var_clause((Node*)
166164
list_concat_copy((List*)action->qual,
167165
action->targetList),
168-
0);
166+
PVC_INCLUDE_PLACEHOLDERS);
169167
foreach(l2,vars)
170168
{
171169
Var*var= (Var*)lfirst(l2);

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1905,6 +1905,27 @@ SELECT * FROM cj_target;
19051905
2 | 320 | initial source2 300
19061906
(4 rows)
19071907

1908+
-- try it with an outer join and PlaceHolderVar
1909+
MERGE INTO cj_target t
1910+
USING (SELECT *, 'join input'::text AS phv FROM cj_source1) fj
1911+
FULL JOIN cj_source2 fj2 ON fj.scat = fj2.sid2 * 10
1912+
ON t.tid = fj.scat
1913+
WHEN NOT MATCHED THEN
1914+
INSERT (tid, balance, val) VALUES (fj.scat, fj.delta, fj.phv);
1915+
SELECT * FROM cj_target;
1916+
tid | balance | val
1917+
-----+---------+----------------------------------
1918+
3 | 400 | initial source2 updated by merge
1919+
1 | 220 | initial source2 200
1920+
1 | 110 | initial source2 200
1921+
2 | 320 | initial source2 300
1922+
10 | 100 | join input
1923+
10 | 400 | join input
1924+
20 | 200 | join input
1925+
20 | 300 | join input
1926+
| |
1927+
(9 rows)
1928+
19081929
ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
19091930
ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
19101931
TRUNCATE cj_target;

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1225,6 +1225,16 @@ WHEN MATCHED THEN
12251225

12261226
SELECT*FROM cj_target;
12271227

1228+
-- try it with an outer join and PlaceHolderVar
1229+
MERGE INTO cj_target t
1230+
USING (SELECT*,'join input'::textAS phvFROM cj_source1) fj
1231+
FULLJOIN cj_source2 fj2ONfj.scat=fj2.sid2*10
1232+
ONt.tid=fj.scat
1233+
WHEN NOT MATCHED THEN
1234+
INSERT (tid, balance, val)VALUES (fj.scat,fj.delta,fj.phv);
1235+
1236+
SELECT*FROM cj_target;
1237+
12281238
ALTERTABLE cj_source1 RENAME COLUMN sid1 TO sid;
12291239
ALTERTABLE cj_source2 RENAME COLUMN sid2 TO sid;
12301240

0 commit comments

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