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

Commite6d8639

Browse files
committed
Prevent join removal from removing the query's result relation.
This was not something that required consideration before MERGEwas invented; but MERGE builds a join tree that left-joins to theresult relation, meaning that remove_useless_joins will considerremoving it. That should generally be stopped by the query's useof output variables from the result relation. However, if theresult relation is inherited (e.g. a partitioned table) thenwe don't add any row identity variables to the query untilexpand_inherited_rtentry, which happens after join removal.This was exposed as of commit3c56904, which made it possibleto deduce that a partitioned table could contain at most one rowmatching a join key, enabling removal of the not-yet-expandedresult relation. Ooops.To fix, let's just teach join_is_removable that the query resultrel is never removable. It's a cheap enough test in any case,and it'll save some cycles that we'd otherwise expend in provingthat it's not removable, even in the cases we got right.Back-patch to v15 where MERGE was added. Although I think thecase cannot be reached in v15, this seems like cheap insurance.Per investigation of a report from Alexander Lakhin.Discussion:https://postgr.es/m/36bee393-b351-16ac-93b2-d46d83637e45@gmail.com
1 parentda32a99 commite6d8639

File tree

3 files changed

+60
-0
lines changed

3 files changed

+60
-0
lines changed

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -176,6 +176,14 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
176176
if (!bms_get_singleton_member(sjinfo->min_righthand,&innerrelid))
177177
return false;
178178

179+
/*
180+
* Never try to eliminate a left join to the query result rel. Although
181+
* the case is syntactically impossible in standard SQL, MERGE will build
182+
* a join tree that looks exactly like that.
183+
*/
184+
if (innerrelid==root->parse->resultRelation)
185+
return false;
186+
179187
innerrel=find_base_rel(root,innerrelid);
180188

181189
/*

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

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1771,6 +1771,38 @@ SELECT * FROM pa_target ORDER BY tid;
17711771
(9 rows)
17721772

17731773
ROLLBACK;
1774+
DROP TABLE pa_source;
1775+
DROP TABLE pa_target CASCADE;
1776+
-- Partitioned table with primary key
1777+
CREATE TABLE pa_target (tid integer PRIMARY KEY) PARTITION BY LIST (tid);
1778+
CREATE TABLE pa_targetp PARTITION OF pa_target DEFAULT;
1779+
CREATE TABLE pa_source (sid integer);
1780+
INSERT INTO pa_source VALUES (1), (2);
1781+
EXPLAIN (VERBOSE, COSTS OFF)
1782+
MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1783+
WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1784+
QUERY PLAN
1785+
-----------------------------------------------------------------------
1786+
Merge on public.pa_target t
1787+
Merge on public.pa_targetp t_1
1788+
-> Nested Loop Left Join
1789+
Output: s.sid, t_1.tableoid, t_1.ctid
1790+
-> Seq Scan on public.pa_source s
1791+
Output: s.sid
1792+
-> Index Scan using pa_targetp_pkey on public.pa_targetp t_1
1793+
Output: t_1.tid, t_1.tableoid, t_1.ctid
1794+
Index Cond: (t_1.tid = s.sid)
1795+
(9 rows)
1796+
1797+
MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1798+
WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1799+
TABLE pa_target;
1800+
tid
1801+
-----
1802+
1
1803+
2
1804+
(2 rows)
1805+
17741806
DROP TABLE pa_source;
17751807
DROP TABLE pa_target CASCADE;
17761808
-- some complex joins on the source side

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1116,6 +1116,26 @@ ROLLBACK;
11161116
DROPTABLE pa_source;
11171117
DROPTABLE pa_target CASCADE;
11181118

1119+
-- Partitioned table with primary key
1120+
1121+
CREATETABLEpa_target (tidintegerPRIMARY KEY) PARTITION BY LIST (tid);
1122+
CREATETABLEpa_targetp PARTITION OF pa_target DEFAULT;
1123+
CREATETABLEpa_source (sidinteger);
1124+
1125+
INSERT INTO pa_sourceVALUES (1), (2);
1126+
1127+
EXPLAIN (VERBOSE, COSTS OFF)
1128+
MERGE INTO pa_target t USING pa_source sONt.tid=s.sid
1129+
WHEN NOT MATCHED THEN INSERTVALUES (s.sid);
1130+
1131+
MERGE INTO pa_target t USING pa_source sONt.tid=s.sid
1132+
WHEN NOT MATCHED THEN INSERTVALUES (s.sid);
1133+
1134+
TABLE pa_target;
1135+
1136+
DROPTABLE pa_source;
1137+
DROPTABLE pa_target CASCADE;
1138+
11191139
-- some complex joins on the source side
11201140

11211141
CREATETABLEcj_target (tidinteger, balance float, valtext)

0 commit comments

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