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

Commit131c7e7

Browse files
committed
postgres_fdw: Check PlaceHolderVars before pushing down a join.
As discovered by Andreas Seltenreich via sqlsmith, it's possible for aremote join to need to generate a target list which contains aPlaceHolderVar which would need to be evaluated on the remote server.This happens when we try to push down a join tree which contains outerjoins and the nullable side of the join contains a subquery whichevauates some expression which can go to NULL above the level of thejoin. Since the deparsing logic can't build a remote query thatinvolves subqueries, it fails while trying to produce an SQL querythat can be sent to the remote side. Detect such cases and don't tryto push down the join at all.It's actually fine to push down the join if the PlaceHolderVar needsto be evaluated at the current join level. This patch makes a smallchange to build_tlist_to_deparse so that this case will work.Amit Langote, Ashutosh Bapat, and me.
1 parent5484c0a commit131c7e7

File tree

4 files changed

+96
-12
lines changed

4 files changed

+96
-12
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -731,7 +731,9 @@ build_tlist_to_deparse(RelOptInfo *foreignrel)
731731
* We require columns specified in foreignrel->reltarget->exprs and those
732732
* required for evaluating the local conditions.
733733
*/
734-
tlist=add_to_flat_tlist(tlist,foreignrel->reltarget->exprs);
734+
tlist=add_to_flat_tlist(tlist,
735+
pull_var_clause((Node*)foreignrel->reltarget->exprs,
736+
PVC_RECURSE_PLACEHOLDERS));
735737
tlist=add_to_flat_tlist(tlist,
736738
pull_var_clause((Node*)fpinfo->local_conds,
737739
PVC_RECURSE_PLACEHOLDERS));

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2202,6 +2202,64 @@ SELECT t1.c1, t2.c1 FROM (ft5 t1 JOIN v_ft5 t2 ON (t1.c1 = t2.c1)) left join (ft
22022202
Remote SQL: SELECT c1 FROM "S 1"."T 4"
22032203
(27 rows)
22042204

2205+
-- non-Var items in targelist of the nullable rel of a join preventing
2206+
-- push-down in some cases
2207+
-- unable to push {ft1, ft2}
2208+
EXPLAIN (COSTS false, VERBOSE)
2209+
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
2210+
QUERY PLAN
2211+
---------------------------------------------------------------------------------------------------------------------------
2212+
Nested Loop Left Join
2213+
Output: (13), ft2.c1
2214+
Join Filter: (13 = ft2.c1)
2215+
-> Foreign Scan on public.ft2
2216+
Output: ft2.c1
2217+
Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE (("C 1" >= 10)) AND (("C 1" <= 15)) ORDER BY "C 1" ASC NULLS LAST
2218+
-> Materialize
2219+
Output: (13)
2220+
-> Foreign Scan on public.ft1
2221+
Output: 13
2222+
Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE (("C 1" = 13))
2223+
(11 rows)
2224+
2225+
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
2226+
a | c1
2227+
----+----
2228+
| 10
2229+
| 11
2230+
| 12
2231+
13 | 13
2232+
| 14
2233+
| 15
2234+
(6 rows)
2235+
2236+
-- ok to push {ft1, ft2} but not {ft1, ft2, ft4}
2237+
EXPLAIN (COSTS false, VERBOSE)
2238+
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
2239+
QUERY PLAN
2240+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2241+
Nested Loop Left Join
2242+
Output: ft4.c1, (13), ft1.c1, ft2.c1
2243+
Join Filter: (ft4.c1 = ft1.c1)
2244+
-> Foreign Scan on public.ft4
2245+
Output: ft4.c1, ft4.c2, ft4.c3
2246+
Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 10)) AND ((c1 <= 15))
2247+
-> Materialize
2248+
Output: ft1.c1, ft2.c1, (13)
2249+
-> Foreign Scan
2250+
Output: ft1.c1, ft2.c1, 13
2251+
Relations: (public.ft1) INNER JOIN (public.ft2)
2252+
Remote SQL: SELECT r4."C 1", r5."C 1" FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (((r5."C 1" = 12)) AND ((r4."C 1" = 12)))) ORDER BY r4."C 1" ASC NULLS LAST
2253+
(12 rows)
2254+
2255+
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
2256+
c1 | a | b | c
2257+
----+----+----+----
2258+
10 | | |
2259+
12 | 13 | 12 | 12
2260+
14 | | |
2261+
(3 rows)
2262+
22052263
-- recreate the dropped user mapping for further tests
22062264
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
22072265
DROP USER MAPPING FOR PUBLIC SERVER loopback;

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 23 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -3952,14 +3952,6 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
39523952
* Assess whether the join between inner and outer relations can be pushed down
39533953
* to the foreign server. As a side effect, save information we obtain in this
39543954
* function to PgFdwRelationInfo passed in.
3955-
*
3956-
* Joins that satisfy conditions below are safe to push down.
3957-
*
3958-
* 1) Join type is INNER or OUTER (one of LEFT/RIGHT/FULL)
3959-
* 2) Both outer and inner portions are safe to push-down
3960-
* 3) All join conditions are safe to push down
3961-
* 4) No relation has local filter (this can be relaxed for INNER JOIN, if we
3962-
* can move unpushable clauses upwards in the join tree).
39633955
*/
39643956
staticbool
39653957
foreign_join_ok(PlannerInfo*root,RelOptInfo*joinrel,JoinTypejointype,
@@ -4036,6 +4028,26 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
40364028
return false;
40374029
}
40384030

4031+
/*
4032+
* deparseExplicitTargetList() isn't smart enough to handle anything other
4033+
* than a Var. In particular, if there's some PlaceHolderVar that would
4034+
* need to be evaluated within this join tree (because there's an upper
4035+
* reference to a quantity that may go to NULL as a result of an outer
4036+
* join), then we can't try to push the join down because we'll fail when
4037+
* we get to deparseExplicitTargetList(). However, a PlaceHolderVar that
4038+
* needs to be evaluated *at the top* of this join tree is OK, because we
4039+
* can do that locally after fetching the results from the remote side.
4040+
*/
4041+
foreach(lc,root->placeholder_list)
4042+
{
4043+
PlaceHolderInfo*phinfo=lfirst(lc);
4044+
Relidsrelids=joinrel->relids;
4045+
4046+
if (bms_is_subset(phinfo->ph_eval_at,relids)&&
4047+
bms_nonempty_difference(relids,phinfo->ph_eval_at))
4048+
return false;
4049+
}
4050+
40394051
/* Save the join clauses, for later use. */
40404052
fpinfo->joinclauses=joinclauses;
40414053

@@ -4116,9 +4128,9 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
41164128
}
41174129

41184130
/*
4119-
* For an inner join,as explained aboveall restrictions can be treated
4120-
*alike. Treating thepushed down conditions as join conditions allows a
4121-
*top level full outerjoin to be deparsed without requiring subqueries.
4131+
* For an inner join, all restrictions can be treated alike. Treating the
4132+
* pushed down conditions as join conditions allows a top level full outer
4133+
* join to be deparsed without requiring subqueries.
41224134
*/
41234135
if (jointype==JOIN_INNER)
41244136
{

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -527,6 +527,18 @@ EXECUTE join_stmt;
527527
EXPLAIN (COSTS false, VERBOSE)
528528
SELECTt1.c1,t2.c1FROM (ft5 t1JOIN v_ft5 t2ON (t1.c1=t2.c1))left join (ft5 t3JOIN v_ft5 t4ON (t3.c1=t4.c1))ON (t1.c1=t3.c1);
529529

530+
-- non-Var items in targelist of the nullable rel of a join preventing
531+
-- push-down in some cases
532+
-- unable to push {ft1, ft2}
533+
EXPLAIN (COSTS false, VERBOSE)
534+
SELECTq.a,ft2.c1FROM (SELECT13FROM ft1WHERE c1=13) q(a)RIGHT JOIN ft2ON (q.a=ft2.c1)WHEREft2.c1 BETWEEN10AND15;
535+
SELECTq.a,ft2.c1FROM (SELECT13FROM ft1WHERE c1=13) q(a)RIGHT JOIN ft2ON (q.a=ft2.c1)WHEREft2.c1 BETWEEN10AND15;
536+
537+
-- ok to push {ft1, ft2} but not {ft1, ft2, ft4}
538+
EXPLAIN (COSTS false, VERBOSE)
539+
SELECTft4.c1, q.*FROM ft4LEFT JOIN (SELECT13,ft1.c1,ft2.c1FROM ft1RIGHT JOIN ft2ON (ft1.c1=ft2.c1)WHEREft1.c1=12) q(a, b, c)ON (ft4.c1=q.b)WHEREft4.c1 BETWEEN10AND15;
540+
SELECTft4.c1, q.*FROM ft4LEFT JOIN (SELECT13,ft1.c1,ft2.c1FROM ft1RIGHT JOIN ft2ON (ft1.c1=ft2.c1)WHEREft1.c1=12) q(a, b, c)ON (ft4.c1=q.b)WHEREft4.c1 BETWEEN10AND15;
541+
530542
-- recreate the dropped user mapping for further tests
531543
CREATEUSERMAPPING FORCURRENT_USER SERVER loopback;
532544
DROPUSER MAPPING FOR PUBLIC SERVER loopback;

0 commit comments

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