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

Commitc198048

Browse files
tglsfdcpull[bot]
authored andcommitted
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 parent658cbde commitc198048

File tree

3 files changed

+63
-0
lines changed

3 files changed

+63
-0
lines changed

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

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

186+
/*
187+
* Never try to eliminate a left join to the query result rel. Although
188+
* the case is syntactically impossible in standard SQL, MERGE will build
189+
* a join tree that looks exactly like that.
190+
*/
191+
if (innerrelid==root->parse->resultRelation)
192+
return false;
193+
186194
innerrel=find_base_rel(root,innerrelid);
187195

188196
/*

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

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1771,6 +1771,41 @@ 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+
-> Hash Left Join
1789+
Output: s.sid, t_1.tableoid, t_1.ctid
1790+
Inner Unique: true
1791+
Hash Cond: (s.sid = t_1.tid)
1792+
-> Seq Scan on public.pa_source s
1793+
Output: s.sid
1794+
-> Hash
1795+
Output: t_1.tid, t_1.tableoid, t_1.ctid
1796+
-> Seq Scan on public.pa_targetp t_1
1797+
Output: t_1.tid, t_1.tableoid, t_1.ctid
1798+
(12 rows)
1799+
1800+
MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1801+
WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1802+
TABLE pa_target;
1803+
tid
1804+
-----
1805+
1
1806+
2
1807+
(2 rows)
1808+
17741809
DROP TABLE pa_source;
17751810
DROP TABLE pa_target CASCADE;
17761811
-- 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