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

Commit77d4d88

Browse files
committed
Repair bogus EPQ plans generated for postgres_fdw foreign joins.
postgres_fdw's postgresGetForeignPlan() assumes without checking that theouter_plan it's given for a join relation must have a NestLoop, MergeJoin,or HashJoin node at the top. That's been wrong at least since commit4bbf6ed (which could cause insertion of a Sort node on top) and it seemslike a pretty unsafe thing to Just Assume even without that.Through blind good fortune, this doesn't seem to have any worseconsequences today than strange EXPLAIN output, but it's clearly troublewaiting to happen.To fix, test the node type explicitly before touching Join-specificfields, and avoid jamming the new tlist into a node type that can'tdo projection. Export a new support function from createplan.cto avoid building low-level knowledge about the latter into FDWs.Back-patch to 9.6 where the faulty coding was added. Note that theassociated regression test cases don't show any changes before v11,apparently because the tests back-patched with4bbf6ed don't actuallyexercise the problem case before then (there's no top-level Sortin those plans).Discussion:https://postgr.es/m/8946.1544644803@sss.pgh.pa.us
1 parent0f7ec8d commit77d4d88

File tree

4 files changed

+140
-89
lines changed

4 files changed

+140
-89
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 72 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -1701,25 +1701,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
17011701
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
17021702
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
17031703
Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
1704-
-> Sort
1705-
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1706-
Sort Key: t1.c3 USING <, t1.c1
1707-
-> Merge Join
1704+
-> Result
1705+
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
1706+
-> Sort
17081707
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1709-
Merge Cond: (t1.c1 = t2.c1)
1710-
->Sort
1711-
Output: t1.c1, t1.c3, t1.*
1712-
Sort Key:t1.c1
1713-
->Foreign Scan on public.ft1 t1
1708+
Sort Key:t1.c3, t1.c1
1709+
->Merge Join
1710+
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1711+
Merge Cond: (t1.c1 = t2.c1)
1712+
->Sort
17141713
Output: t1.c1, t1.c3, t1.*
1715-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
1716-
->Sort
1717-
Output:t2.c1,t2.*
1718-
Sort Key: t2.c1
1719-
->Foreign Scan on public.ft2 t2
1714+
Sort Key: t1.c1
1715+
->Foreign Scan on public.ft1 t1
1716+
Output:t1.c1,t1.c3, t1.*
1717+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
1718+
->Sort
17201719
Output: t2.c1, t2.*
1721-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1722-
(26 rows)
1720+
Sort Key: t2.c1
1721+
-> Foreign Scan on public.ft2 t2
1722+
Output: t2.c1, t2.*
1723+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1724+
(28 rows)
17231725

17241726
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
17251727
c1 | c1
@@ -1748,25 +1750,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
17481750
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
17491751
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
17501752
Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
1751-
-> Sort
1752-
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1753-
Sort Key: t1.c3 USING <, t1.c1
1754-
-> Merge Join
1753+
-> Result
1754+
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
1755+
-> Sort
17551756
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1756-
Merge Cond: (t1.c1 = t2.c1)
1757-
->Sort
1758-
Output: t1.c1, t1.c3, t1.*
1759-
Sort Key:t1.c1
1760-
->Foreign Scan on public.ft1 t1
1757+
Sort Key:t1.c3, t1.c1
1758+
->Merge Join
1759+
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1760+
Merge Cond: (t1.c1 = t2.c1)
1761+
->Sort
17611762
Output: t1.c1, t1.c3, t1.*
1762-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
1763-
->Sort
1764-
Output:t2.c1,t2.*
1765-
Sort Key: t2.c1
1766-
->Foreign Scan on public.ft2 t2
1763+
Sort Key: t1.c1
1764+
->Foreign Scan on public.ft1 t1
1765+
Output:t1.c1,t1.c3, t1.*
1766+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
1767+
->Sort
17671768
Output: t2.c1, t2.*
1768-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
1769-
(26 rows)
1769+
Sort Key: t2.c1
1770+
-> Foreign Scan on public.ft2 t2
1771+
Output: t2.c1, t2.*
1772+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
1773+
(28 rows)
17701774

17711775
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
17721776
c1 | c1
@@ -1796,25 +1800,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
17961800
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
17971801
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
17981802
Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1
1799-
-> Sort
1800-
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1801-
Sort Key: t1.c3 USING <, t1.c1
1802-
-> Merge Join
1803+
-> Result
1804+
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
1805+
-> Sort
18031806
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1804-
Merge Cond: (t1.c1 = t2.c1)
1805-
->Sort
1806-
Output: t1.c1, t1.c3, t1.*
1807-
Sort Key:t1.c1
1808-
->Foreign Scan on public.ft1 t1
1807+
Sort Key:t1.c3, t1.c1
1808+
->Merge Join
1809+
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1810+
Merge Cond: (t1.c1 = t2.c1)
1811+
->Sort
18091812
Output: t1.c1, t1.c3, t1.*
1810-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
1811-
->Sort
1812-
Output:t2.c1,t2.*
1813-
Sort Key: t2.c1
1814-
->Foreign Scan on public.ft2 t2
1813+
Sort Key: t1.c1
1814+
->Foreign Scan on public.ft1 t1
1815+
Output:t1.c1,t1.c3, t1.*
1816+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
1817+
->Sort
18151818
Output: t2.c1, t2.*
1816-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1817-
(26 rows)
1819+
Sort Key: t2.c1
1820+
-> Foreign Scan on public.ft2 t2
1821+
Output: t2.c1, t2.*
1822+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
1823+
(28 rows)
18181824

18191825
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
18201826
c1 | c1
@@ -1843,25 +1849,27 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
18431849
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
18441850
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
18451851
Remote SQL: SELECT r1."C 1", r2."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
1846-
-> Sort
1847-
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1848-
Sort Key: t1.c3 USING <, t1.c1
1849-
-> Merge Join
1852+
-> Result
1853+
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
1854+
-> Sort
18501855
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1851-
Merge Cond: (t1.c1 = t2.c1)
1852-
->Sort
1853-
Output: t1.c1, t1.c3, t1.*
1854-
Sort Key:t1.c1
1855-
->Foreign Scan on public.ft1 t1
1856+
Sort Key:t1.c3, t1.c1
1857+
->Merge Join
1858+
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
1859+
Merge Cond: (t1.c1 = t2.c1)
1860+
->Sort
18561861
Output: t1.c1, t1.c3, t1.*
1857-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
1858-
->Sort
1859-
Output:t2.c1,t2.*
1860-
Sort Key: t2.c1
1861-
->Foreign Scan on public.ft2 t2
1862+
Sort Key: t1.c1
1863+
->Foreign Scan on public.ft1 t1
1864+
Output:t1.c1,t1.c3, t1.*
1865+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
1866+
->Sort
18621867
Output: t2.c1, t2.*
1863-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
1864-
(26 rows)
1868+
Sort Key: t2.c1
1869+
-> Foreign Scan on public.ft2 t2
1870+
Output: t2.c1, t2.*
1871+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
1872+
(28 rows)
18651873

18661874
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
18671875
c1 | c1

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 29 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1229,11 +1229,9 @@ postgresGetForeignPlan(PlannerInfo *root,
12291229

12301230
/*
12311231
* Ensure that the outer plan produces a tuple whose descriptor
1232-
* matches our scan tuple slot. This is safe because all scans and
1233-
* joins support projection, so we never need to insert a Result node.
1234-
* Also, remove the local conditions from outer plan's quals, lest
1235-
* they will be evaluated twice, once by the local plan and once by
1236-
* the scan.
1232+
* matches our scan tuple slot. Also, remove the local conditions
1233+
* from outer plan's quals, lest they be evaluated twice, once by the
1234+
* local plan and once by the scan.
12371235
*/
12381236
if (outer_plan)
12391237
{
@@ -1246,23 +1244,42 @@ postgresGetForeignPlan(PlannerInfo *root,
12461244
*/
12471245
Assert(!IS_UPPER_REL(foreignrel));
12481246

1249-
outer_plan->targetlist=fdw_scan_tlist;
1250-
1247+
/*
1248+
* First, update the plan's qual list if possible. In some cases
1249+
* the quals might be enforced below the topmost plan level, in
1250+
* which case we'll fail to remove them; it's not worth working
1251+
* harder than this.
1252+
*/
12511253
foreach(lc,local_exprs)
12521254
{
1253-
Join*join_plan= (Join*)outer_plan;
12541255
Node*qual=lfirst(lc);
12551256

12561257
outer_plan->qual=list_delete(outer_plan->qual,qual);
12571258

12581259
/*
12591260
* For an inner join the local conditions of foreign scan plan
1260-
* can be part of the joinquals as well.
1261+
* can be part of the joinquals as well. (They might also be
1262+
* in the mergequals or hashquals, but we can't touch those
1263+
* without breaking the plan.)
12611264
*/
1262-
if (join_plan->jointype==JOIN_INNER)
1263-
join_plan->joinqual=list_delete(join_plan->joinqual,
1264-
qual);
1265+
if (IsA(outer_plan,NestLoop)||
1266+
IsA(outer_plan,MergeJoin)||
1267+
IsA(outer_plan,HashJoin))
1268+
{
1269+
Join*join_plan= (Join*)outer_plan;
1270+
1271+
if (join_plan->jointype==JOIN_INNER)
1272+
join_plan->joinqual=list_delete(join_plan->joinqual,
1273+
qual);
1274+
}
12651275
}
1276+
1277+
/*
1278+
* Now fix the subplan's tlist --- this might result in inserting
1279+
* a Result node atop the plan tree.
1280+
*/
1281+
outer_plan=change_plan_targetlist(outer_plan,fdw_scan_tlist,
1282+
best_path->path.parallel_safe);
12661283
}
12671284
}
12681285

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

Lines changed: 37 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1407,20 +1407,10 @@ create_unique_plan(PlannerInfo *root, UniquePath *best_path, int flags)
14071407
}
14081408
}
14091409

1410+
/* Use change_plan_targetlist in case we need to insert a Result node */
14101411
if (newitems||best_path->umethod==UNIQUE_PATH_SORT)
1411-
{
1412-
/*
1413-
* If the top plan node can't do projections and its existing target
1414-
* list isn't already what we need, we need to add a Result node to
1415-
* help it along.
1416-
*/
1417-
if (!is_projection_capable_plan(subplan)&&
1418-
!tlist_same_exprs(newtlist,subplan->targetlist))
1419-
subplan=inject_projection_plan(subplan,newtlist,
1420-
best_path->path.parallel_safe);
1421-
else
1422-
subplan->targetlist=newtlist;
1423-
}
1412+
subplan=change_plan_targetlist(subplan,newtlist,
1413+
best_path->path.parallel_safe);
14241414

14251415
/*
14261416
* Build control information showing which subplan output columns are to
@@ -1762,6 +1752,40 @@ inject_projection_plan(Plan *subplan, List *tlist, bool parallel_safe)
17621752
returnplan;
17631753
}
17641754

1755+
/*
1756+
* change_plan_targetlist
1757+
* Externally available wrapper for inject_projection_plan.
1758+
*
1759+
* This is meant for use by FDW plan-generation functions, which might
1760+
* want to adjust the tlist computed by some subplan tree. In general,
1761+
* a Result node is needed to compute the new tlist, but we can optimize
1762+
* some cases.
1763+
*
1764+
* In most cases, tlist_parallel_safe can just be passed as the parallel_safe
1765+
* flag of the FDW's own Path node.
1766+
*/
1767+
Plan*
1768+
change_plan_targetlist(Plan*subplan,List*tlist,booltlist_parallel_safe)
1769+
{
1770+
/*
1771+
* If the top plan node can't do projections and its existing target list
1772+
* isn't already what we need, we need to add a Result node to help it
1773+
* along.
1774+
*/
1775+
if (!is_projection_capable_plan(subplan)&&
1776+
!tlist_same_exprs(tlist,subplan->targetlist))
1777+
subplan=inject_projection_plan(subplan,tlist,
1778+
subplan->parallel_safe&&
1779+
tlist_parallel_safe);
1780+
else
1781+
{
1782+
/* Else we can just replace the plan node's tlist */
1783+
subplan->targetlist=tlist;
1784+
subplan->parallel_safe &=tlist_parallel_safe;
1785+
}
1786+
returnsubplan;
1787+
}
1788+
17651789
/*
17661790
* create_sort_plan
17671791
*

‎src/include/optimizer/planmain.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,8 @@ extern ForeignScan *make_foreignscan(List *qptlist, List *qpqual,
5353
Indexscanrelid,List*fdw_exprs,List*fdw_private,
5454
List*fdw_scan_tlist,List*fdw_recheck_quals,
5555
Plan*outer_plan);
56+
externPlan*change_plan_targetlist(Plan*subplan,List*tlist,
57+
booltlist_parallel_safe);
5658
externPlan*materialize_finished_plan(Plan*subplan);
5759
externboolis_projection_capable_path(Path*path);
5860
externboolis_projection_capable_plan(Plan*plan);

0 commit comments

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