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

Commitb0e390e

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 parent3419962 commitb0e390e

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
@@ -2197,6 +2197,32 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
21972197
1
21982198
(10 rows)
21992199

2200+
-- join with pseudoconstant quals, not pushed down.
2201+
EXPLAIN (VERBOSE, COSTS OFF)
2202+
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;
2203+
QUERY PLAN
2204+
-------------------------------------------------------------------------------
2205+
Limit
2206+
Output: t1.c1, t2.c1, t1.c3
2207+
-> Sort
2208+
Output: t1.c1, t2.c1, t1.c3
2209+
Sort Key: t1.c3, t1.c1
2210+
-> Result
2211+
Output: t1.c1, t2.c1, t1.c3
2212+
One-Time Filter: (CURRENT_USER = SESSION_USER)
2213+
-> Hash Join
2214+
Output: t1.c1, t1.c3, t2.c1
2215+
Hash Cond: (t2.c1 = t1.c1)
2216+
-> Foreign Scan on public.ft2 t2
2217+
Output: t2.c1
2218+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
2219+
-> Hash
2220+
Output: t1.c1, t1.c3
2221+
-> Foreign Scan on public.ft1 t1
2222+
Output: t1.c1, t1.c3
2223+
Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1"
2224+
(19 rows)
2225+
22002226
-- non-Var items in targetlist of the nullable rel of a join preventing
22012227
-- push-down in some cases
22022228
-- 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
@@ -595,6 +595,9 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
595595
EXPLAIN (VERBOSE, COSTS OFF)
596596
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;
597597
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;
598+
-- join with pseudoconstant quals, not pushed down.
599+
EXPLAIN (VERBOSE, COSTS OFF)
600+
SELECTt1.c1,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1ANDCURRENT_USER=SESSION_USER)ORDER BYt1.c3,t1.c1 OFFSET100LIMIT10;
598601

599602
-- non-Var items in targetlist of the nullable rel of a join preventing
600603
-- 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
@@ -495,6 +495,35 @@ extract_actual_join_clauses(List *restrictinfo_list,
495495
}
496496
}
497497

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

499528
/*
500529
* 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