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

Commitd1ef563

Browse files
author
Etsuro Fujita
committed
Disallow replacing joins with scans in problematic cases.
Commite7cb7ee, which introduced the infrastructure for FDWs andcustom scan providers to replace joins with scans, failed to add supporthandling of pseudoconstant quals assigned to replaced joins increateplan.c, leading to an incorrect plan without a gating Result nodewhen postgres_fdw replaced a join with such a qual.To fix, we could add the support by 1) modifying the ForeignPath andCustomPath structs to store the list of RestrictInfo nodes to apply tothe join, as in JoinPaths, if they represent foreign and custom scansreplacing a join with a scan, and by 2) modifying create_scan_plan() increateplan.c to use that list in that case, instead of thebaserestrictinfo list, to get pseudoconstant quals assigned to the join;but#1 would cause an ABI break. So fix by modifying the infrastructureto just disallow replacing joins with such quals.Back-patch to all supported branches.Reported by Nishant Sharma. Patch by me, reviewed by Nishant Sharma andRichard Guo.Discussion:https://postgr.es/m/CADrsxdbcN1vejBaf8a%2BQhrZY5PXL-04mCd4GDu6qm6FigDZd6Q%40mail.gmail.com
1 parent313ceda commitd1ef563

File tree

5 files changed

+77
-2
lines changed

5 files changed

+77
-2
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2293,6 +2293,32 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
22932293
1
22942294
(10 rows)
22952295

2296+
-- join with pseudoconstant quals, not pushed down.
2297+
EXPLAIN (VERBOSE, COSTS OFF)
2298+
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1 AND CURRENT_USER = SESSION_USER) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
2299+
QUERY PLAN
2300+
-------------------------------------------------------------------------------
2301+
Limit
2302+
Output: t1.c1, t2.c1, t1.c3
2303+
-> Sort
2304+
Output: t1.c1, t2.c1, t1.c3
2305+
Sort Key: t1.c3, t1.c1
2306+
-> Result
2307+
Output: t1.c1, t2.c1, t1.c3
2308+
One-Time Filter: (CURRENT_USER = SESSION_USER)
2309+
-> Hash Join
2310+
Output: t1.c1, t1.c3, t2.c1
2311+
Hash Cond: (t2.c1 = t1.c1)
2312+
-> Foreign Scan on public.ft2 t2
2313+
Output: t2.c1
2314+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
2315+
-> Hash
2316+
Output: t1.c1, t1.c3
2317+
-> Foreign Scan on public.ft1 t1
2318+
Output: t1.c1, t1.c3
2319+
Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1"
2320+
(19 rows)
2321+
22962322
-- non-Var items in targetlist of the nullable rel of a join preventing
22972323
-- push-down in some cases
22982324
-- unable to push {ft1, ft2}

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -629,6 +629,9 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
629629
EXPLAIN (VERBOSE, COSTS OFF)
630630
SELECT t1."C 1"FROM"S 1"."T 1" t1, LATERAL (SELECT DISTINCTt2.c1,t3.c1FROM ft1 t2, ft2 t3WHEREt2.c1=t3.c1ANDt2.c2=t1.c2) qORDER BY t1."C 1" OFFSET10LIMIT10;
631631
SELECT t1."C 1"FROM"S 1"."T 1" t1, LATERAL (SELECT DISTINCTt2.c1,t3.c1FROM ft1 t2, ft2 t3WHEREt2.c1=t3.c1ANDt2.c2=t1.c2) qORDER BY t1."C 1" OFFSET10LIMIT10;
632+
-- join with pseudoconstant quals, not pushed down.
633+
EXPLAIN (VERBOSE, COSTS OFF)
634+
SELECTt1.c1,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1ANDCURRENT_USER=SESSION_USER)ORDER BYt1.c3,t1.c1 OFFSET100LIMIT10;
632635

633636
-- non-Var items in targetlist of the nullable rel of a join preventing
634637
-- push-down in some cases

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

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include"optimizer/pathnode.h"
2525
#include"optimizer/paths.h"
2626
#include"optimizer/planmain.h"
27+
#include"optimizer/restrictinfo.h"
2728
#include"utils/typcache.h"
2829

2930
/* Hook for plugins to get control in add_paths_to_joinrel() */
@@ -130,6 +131,7 @@ add_paths_to_joinrel(PlannerInfo *root,
130131
{
131132
JoinPathExtraDataextra;
132133
boolmergejoin_allowed= true;
134+
boolconsider_join_pushdown= false;
133135
ListCell*lc;
134136
Relidsjoinrelids;
135137

@@ -318,21 +320,34 @@ add_paths_to_joinrel(PlannerInfo *root,
318320
hash_inner_and_outer(root,joinrel,outerrel,innerrel,
319321
jointype,&extra);
320322

323+
/*
324+
* createplan.c does not currently support handling of pseudoconstant
325+
* clauses assigned to joins pushed down by extensions; check if the
326+
* restrictlist has such clauses, and if so, disallow pushing down joins.
327+
*/
328+
if ((joinrel->fdwroutine&&
329+
joinrel->fdwroutine->GetForeignJoinPaths)||
330+
set_join_pathlist_hook)
331+
consider_join_pushdown= !has_pseudoconstant_clauses(root,
332+
restrictlist);
333+
321334
/*
322335
* 5. If inner and outer relations are foreign tables (or joins) belonging
323336
* to the same server and assigned to the same user to check access
324337
* permissions as, give the FDW a chance to push down joins.
325338
*/
326339
if (joinrel->fdwroutine&&
327-
joinrel->fdwroutine->GetForeignJoinPaths)
340+
joinrel->fdwroutine->GetForeignJoinPaths&&
341+
consider_join_pushdown)
328342
joinrel->fdwroutine->GetForeignJoinPaths(root,joinrel,
329343
outerrel,innerrel,
330344
jointype,&extra);
331345

332346
/*
333347
* 6. Finally, give extensions a chance to manipulate the path list.
334348
*/
335-
if (set_join_pathlist_hook)
349+
if (set_join_pathlist_hook&&
350+
consider_join_pushdown)
336351
set_join_pathlist_hook(root,joinrel,outerrel,innerrel,
337352
jointype,&extra);
338353
}

‎src/backend/optimizer/util/restrictinfo.c

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -497,6 +497,35 @@ extract_actual_join_clauses(List *restrictinfo_list,
497497
}
498498
}
499499

500+
/*
501+
* has_pseudoconstant_clauses
502+
*
503+
* Returns true if 'restrictinfo_list' includes pseudoconstant clauses.
504+
*
505+
* This is used when we determine whether to allow extensions to consider
506+
* pushing down joins in add_paths_to_joinrel().
507+
*/
508+
bool
509+
has_pseudoconstant_clauses(PlannerInfo*root,
510+
List*restrictinfo_list)
511+
{
512+
ListCell*l;
513+
514+
/* No need to look if we know there are no pseudoconstants */
515+
if (!root->hasPseudoConstantQuals)
516+
return false;
517+
518+
/* See if there are pseudoconstants in the RestrictInfo list */
519+
foreach(l,restrictinfo_list)
520+
{
521+
RestrictInfo*rinfo=lfirst_node(RestrictInfo,l);
522+
523+
if (rinfo->pseudoconstant)
524+
return true;
525+
}
526+
return false;
527+
}
528+
500529

501530
/*
502531
* join_clause_is_movable_to

‎src/include/optimizer/restrictinfo.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,8 @@ extern void extract_actual_join_clauses(List *restrictinfo_list,
4141
Relidsjoinrelids,
4242
List**joinquals,
4343
List**otherquals);
44+
externboolhas_pseudoconstant_clauses(PlannerInfo*root,
45+
List*restrictinfo_list);
4446
externbooljoin_clause_is_movable_to(RestrictInfo*rinfo,RelOptInfo*baserel);
4547
externbooljoin_clause_is_movable_into(RestrictInfo*rinfo,
4648
Relidscurrentrelids,

0 commit comments

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