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

Commit6f80a8d

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 parent38df84c commit6f80a8d

File tree

5 files changed

+78
-2
lines changed

5 files changed

+78
-2
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

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

2319+
-- join with pseudoconstant quals, not pushed down.
2320+
EXPLAIN (VERBOSE, COSTS OFF)
2321+
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;
2322+
QUERY PLAN
2323+
-------------------------------------------------------------------------------
2324+
Limit
2325+
Output: t1.c1, t2.c1, t1.c3
2326+
-> Sort
2327+
Output: t1.c1, t2.c1, t1.c3
2328+
Sort Key: t1.c3, t1.c1
2329+
-> Result
2330+
Output: t1.c1, t2.c1, t1.c3
2331+
One-Time Filter: (CURRENT_USER = SESSION_USER)
2332+
-> Hash Join
2333+
Output: t1.c1, t1.c3, t2.c1
2334+
Hash Cond: (t2.c1 = t1.c1)
2335+
-> Foreign Scan on public.ft2 t2
2336+
Output: t2.c1
2337+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
2338+
-> Hash
2339+
Output: t1.c1, t1.c3
2340+
-> Foreign Scan on public.ft1 t1
2341+
Output: t1.c1, t1.c3
2342+
Remote SQL: SELECT "C 1", c3 FROM "S 1"."T 1"
2343+
(19 rows)
2344+
23192345
-- non-Var items in targetlist of the nullable rel of a join preventing
23202346
-- push-down in some cases
23212347
-- 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
@@ -640,6 +640,9 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
640640
EXPLAIN (VERBOSE, COSTS OFF)
641641
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;
642642
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;
643+
-- join with pseudoconstant quals, not pushed down.
644+
EXPLAIN (VERBOSE, COSTS OFF)
645+
SELECTt1.c1,t2.c1FROM ft1 t1JOIN ft2 t2ON (t1.c1=t2.c1ANDCURRENT_USER=SESSION_USER)ORDER BYt1.c3,t1.c1 OFFSET100LIMIT10;
643646

644647
-- non-Var items in targetlist of the nullable rel of a join preventing
645648
-- 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

@@ -321,21 +323,34 @@ add_paths_to_joinrel(PlannerInfo *root,
321323
hash_inner_and_outer(root,joinrel,outerrel,innerrel,
322324
jointype,&extra);
323325

326+
/*
327+
* createplan.c does not currently support handling of pseudoconstant
328+
* clauses assigned to joins pushed down by extensions; check if the
329+
* restrictlist has such clauses, and if so, disallow pushing down joins.
330+
*/
331+
if ((joinrel->fdwroutine&&
332+
joinrel->fdwroutine->GetForeignJoinPaths)||
333+
set_join_pathlist_hook)
334+
consider_join_pushdown= !has_pseudoconstant_clauses(root,
335+
restrictlist);
336+
324337
/*
325338
* 5. If inner and outer relations are foreign tables (or joins) belonging
326339
* to the same server and assigned to the same user to check access
327340
* permissions as, give the FDW a chance to push down joins.
328341
*/
329342
if (joinrel->fdwroutine&&
330-
joinrel->fdwroutine->GetForeignJoinPaths)
343+
joinrel->fdwroutine->GetForeignJoinPaths&&
344+
consider_join_pushdown)
331345
joinrel->fdwroutine->GetForeignJoinPaths(root,joinrel,
332346
outerrel,innerrel,
333347
jointype,&extra);
334348

335349
/*
336350
* 6. Finally, give extensions a chance to manipulate the path list.
337351
*/
338-
if (set_join_pathlist_hook)
352+
if (set_join_pathlist_hook&&
353+
consider_join_pushdown)
339354
set_join_pathlist_hook(root,joinrel,outerrel,innerrel,
340355
jointype,&extra);
341356
}

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -549,6 +549,36 @@ extract_actual_join_clauses(List *restrictinfo_list,
549549
}
550550
}
551551

552+
/*
553+
* has_pseudoconstant_clauses
554+
*
555+
* Returns true if 'restrictinfo_list' includes pseudoconstant clauses.
556+
*
557+
* This is used when we determine whether to allow extensions to consider
558+
* pushing down joins in add_paths_to_joinrel().
559+
*/
560+
bool
561+
has_pseudoconstant_clauses(PlannerInfo*root,
562+
List*restrictinfo_list)
563+
{
564+
ListCell*l;
565+
566+
/* No need to look if we know there are no pseudoconstants */
567+
if (!root->hasPseudoConstantQuals)
568+
return false;
569+
570+
/* See if there are pseudoconstants in the RestrictInfo list */
571+
foreach(l,restrictinfo_list)
572+
{
573+
RestrictInfo*rinfo=lfirst_node(RestrictInfo,l);
574+
575+
if (rinfo->pseudoconstant&&
576+
!rinfo_is_constant_true(rinfo))
577+
return true;
578+
}
579+
return false;
580+
}
581+
552582
/*
553583
* join_clause_is_movable_to
554584
*Test whether a join clause is a safe candidate for parameterization

‎src/include/optimizer/restrictinfo.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,8 @@ extern void extract_actual_join_clauses(List *restrictinfo_list,
4343
Relidsjoinrelids,
4444
List**joinquals,
4545
List**otherquals);
46+
externboolhas_pseudoconstant_clauses(PlannerInfo*root,
47+
List*restrictinfo_list);
4648
externbooljoin_clause_is_movable_to(RestrictInfo*rinfo,RelOptInfo*baserel);
4749
externbooljoin_clause_is_movable_into(RestrictInfo*rinfo,
4850
Relidscurrentrelids,

0 commit comments

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