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

Commit9edf72a

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 parent0660f74 commit9edf72a

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
@@ -2176,6 +2176,32 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
21762176
1
21772177
(10 rows)
21782178

2179+
-- join with pseudoconstant quals, not pushed down.
2180+
EXPLAIN (VERBOSE, COSTS OFF)
2181+
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;
2182+
QUERY PLAN
2183+
-------------------------------------------------------------------------------
2184+
Limit
2185+
Output: t1.c1, t2.c1, t1.c3
2186+
-> Sort
2187+
Output: t1.c1, t2.c1, t1.c3
2188+
Sort Key: t1.c3, t1.c1
2189+
-> Result
2190+
Output: t1.c1, t2.c1, t1.c3
2191+
One-Time Filter: (CURRENT_USER = SESSION_USER)
2192+
-> Hash Join
2193+
Output: t1.c1, t1.c3, t2.c1
2194+
Hash Cond: (t2.c1 = t1.c1)
2195+
-> Foreign Scan on public.ft2 t2
2196+
Output: t2.c1
2197+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
2198+
-> Hash
2199+
Output: t1.c1, t1.c3
2200+
-> Foreign Scan on public.ft1 t1
2201+
Output: t1.c1, t1.c3
2202+
Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1"
2203+
(19 rows)
2204+
21792205
-- non-Var items in targetlist of the nullable rel of a join preventing
21802206
-- push-down in some cases
21812207
-- 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
@@ -570,6 +570,9 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
570570
EXPLAIN (VERBOSE, COSTS OFF)
571571
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;
572572
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;
573+
-- join with pseudoconstant quals, not pushed down.
574+
EXPLAIN (VERBOSE, COSTS OFF)
575+
SELECTt1.c1,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1ANDCURRENT_USER=SESSION_USER)ORDER BYt1.c3,t1.c1 OFFSET100LIMIT10;
573576

574577
-- non-Var items in targetlist of the nullable rel of a join preventing
575578
-- 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
@@ -22,6 +22,7 @@
2222
#include"optimizer/pathnode.h"
2323
#include"optimizer/paths.h"
2424
#include"optimizer/planmain.h"
25+
#include"optimizer/restrictinfo.h"
2526

2627
/* Hook for plugins to get control in add_paths_to_joinrel() */
2728
set_join_pathlist_hook_typeset_join_pathlist_hook=NULL;
@@ -124,6 +125,7 @@ add_paths_to_joinrel(PlannerInfo *root,
124125
{
125126
JoinPathExtraDataextra;
126127
boolmergejoin_allowed= true;
128+
boolconsider_join_pushdown= false;
127129
ListCell*lc;
128130
Relidsjoinrelids;
129131

@@ -307,21 +309,34 @@ add_paths_to_joinrel(PlannerInfo *root,
307309
hash_inner_and_outer(root,joinrel,outerrel,innerrel,
308310
jointype,&extra);
309311

312+
/*
313+
* createplan.c does not currently support handling of pseudoconstant
314+
* clauses assigned to joins pushed down by extensions; check if the
315+
* restrictlist has such clauses, and if so, disallow pushing down joins.
316+
*/
317+
if ((joinrel->fdwroutine&&
318+
joinrel->fdwroutine->GetForeignJoinPaths)||
319+
set_join_pathlist_hook)
320+
consider_join_pushdown= !has_pseudoconstant_clauses(root,
321+
restrictlist);
322+
310323
/*
311324
* 5. If inner and outer relations are foreign tables (or joins) belonging
312325
* to the same server and assigned to the same user to check access
313326
* permissions as, give the FDW a chance to push down joins.
314327
*/
315328
if (joinrel->fdwroutine&&
316-
joinrel->fdwroutine->GetForeignJoinPaths)
329+
joinrel->fdwroutine->GetForeignJoinPaths&&
330+
consider_join_pushdown)
317331
joinrel->fdwroutine->GetForeignJoinPaths(root,joinrel,
318332
outerrel,innerrel,
319333
jointype,&extra);
320334

321335
/*
322336
* 6. Finally, give extensions a chance to manipulate the path list.
323337
*/
324-
if (set_join_pathlist_hook)
338+
if (set_join_pathlist_hook&&
339+
consider_join_pushdown)
325340
set_join_pathlist_hook(root,joinrel,outerrel,innerrel,
326341
jointype,&extra);
327342
}

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -509,6 +509,35 @@ extract_actual_join_clauses(List *restrictinfo_list,
509509
}
510510
}
511511

512+
/*
513+
* has_pseudoconstant_clauses
514+
*
515+
* Returns true if 'restrictinfo_list' includes pseudoconstant clauses.
516+
*
517+
* This is used when we determine whether to allow extensions to consider
518+
* pushing down joins in add_paths_to_joinrel().
519+
*/
520+
bool
521+
has_pseudoconstant_clauses(PlannerInfo*root,
522+
List*restrictinfo_list)
523+
{
524+
ListCell*l;
525+
526+
/* No need to look if we know there are no pseudoconstants */
527+
if (!root->hasPseudoConstantQuals)
528+
return false;
529+
530+
/* See if there are pseudoconstants in the RestrictInfo list */
531+
foreach(l,restrictinfo_list)
532+
{
533+
RestrictInfo*rinfo=lfirst_node(RestrictInfo,l);
534+
535+
if (rinfo->pseudoconstant)
536+
return true;
537+
}
538+
return false;
539+
}
540+
512541

513542
/*
514543
* join_clause_is_movable_to

‎src/include/optimizer/restrictinfo.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,8 @@ extern void extract_actual_join_clauses(List *restrictinfo_list,
4949
Relidsjoinrelids,
5050
List**joinquals,
5151
List**otherquals);
52+
externboolhas_pseudoconstant_clauses(PlannerInfo*root,
53+
List*restrictinfo_list);
5254
externbooljoin_clause_is_movable_to(RestrictInfo*rinfo,RelOptInfo*baserel);
5355
externbooljoin_clause_is_movable_into(RestrictInfo*rinfo,
5456
Relidscurrentrelids,

0 commit comments

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