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

Commit86e640a

Browse files
committed
postgres_fdw: Fix join push down with extensions
Objects in an extension are shippable to a foreign server if theextension is part of the foreign server definition's shippableextensions list. But this was not properly considered in some caseswhen checking whether a join condition can be pushed to a foreign serverand the join condition uses an object from a shippable extension. Sothe join would never be pushed down in those cases.So, the list of extensions needs to be made available in fpinfo of therelation being considered to be pushed down before any expressions areassessed for being shippable. Fix foreign_join_ok() to do that for ajoin relation.David Rowley and Ashutosh Bapat, per report from David Rowleyreduced version of patch332bec1 forbackpatch to 9.6, first release with join push down
1 parente615605 commit86e640a

File tree

3 files changed

+46
-3
lines changed

3 files changed

+46
-3
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1501,6 +1501,35 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1
15011501
| 21
15021502
(10 rows)
15031503

1504+
-- full outer join + WHERE clause with shippable extensions set
1505+
EXPLAIN (VERBOSE, COSTS OFF)
1506+
SELECT t1.c1, t2.c2, t1.c3 FROM ft1 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE postgres_fdw_abs(t1.c1) > 0 OFFSET 10 LIMIT 10;
1507+
QUERY PLAN
1508+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1509+
Limit
1510+
Output: t1.c1, t2.c2, t1.c3
1511+
-> Foreign Scan
1512+
Output: t1.c1, t2.c2, t1.c3
1513+
Relations: (public.ft1 t1) FULL JOIN (public.ft2 t2)
1514+
Remote SQL: SELECT r1."C 1", r1.c3, r2.c2 FROM ("S 1"."T 1" r1 FULL JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) WHERE ((public.postgres_fdw_abs(r1."C 1") > 0))
1515+
(6 rows)
1516+
1517+
ALTER SERVER loopback OPTIONS (DROP extensions);
1518+
-- full outer join + WHERE clause with shippable extensions not set
1519+
EXPLAIN (VERBOSE, COSTS OFF)
1520+
SELECT t1.c1, t2.c2, t1.c3 FROM ft1 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE postgres_fdw_abs(t1.c1) > 0 OFFSET 10 LIMIT 10;
1521+
QUERY PLAN
1522+
-------------------------------------------------------------------------------------------------------------------------------
1523+
Limit
1524+
Output: t1.c1, t2.c2, t1.c3
1525+
-> Foreign Scan
1526+
Output: t1.c1, t2.c2, t1.c3
1527+
Filter: (postgres_fdw_abs(t1.c1) > 0)
1528+
Relations: (public.ft1 t1) FULL JOIN (public.ft2 t2)
1529+
Remote SQL: SELECT r1."C 1", r1.c3, r2.c2 FROM ("S 1"."T 1" r1 FULL JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
1530+
(7 rows)
1531+
1532+
ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
15041533
-- join two tables with FOR UPDATE clause
15051534
-- tests whole-row reference for row marks
15061535
EXPLAIN (VERBOSE, COSTS OFF)

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3990,6 +3990,15 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
39903990
joinclauses=NIL;
39913991
}
39923992

3993+
/* Get foreign server */
3994+
fpinfo->server=fpinfo_o->server;
3995+
3996+
/*
3997+
* Copy shippable_extensions before checking whether the foreign join is
3998+
* OK, so that we know which quals can be evaluated on the foreign server.
3999+
*/
4000+
fpinfo->shippable_extensions=fpinfo_o->shippable_extensions;
4001+
39934002
/* Join quals must be safe to push down. */
39944003
foreach(lc,joinclauses)
39954004
{
@@ -4133,9 +4142,6 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
41334142
else
41344143
fpinfo->user=NULL;
41354144

4136-
/* Get foreign server */
4137-
fpinfo->server=fpinfo_o->server;
4138-
41394145
/*
41404146
* Since both the joining relations come from the same server, the server
41414147
* level options should have same value for both the relations. Pick from

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -422,6 +422,14 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT
422422
EXPLAIN (VERBOSE, COSTS OFF)
423423
SELECTt1.c1,t2.c1FROM ft4 t1 FULLJOIN ft5 t2ON (t1.c1=t2.c1)WHERE (t1.c1=t2.c1ORt1.c1 ISNULL)ORDER BYt1.c1,t2.c1 OFFSET10LIMIT10;
424424
SELECTt1.c1,t2.c1FROM ft4 t1 FULLJOIN ft5 t2ON (t1.c1=t2.c1)WHERE (t1.c1=t2.c1ORt1.c1 ISNULL)ORDER BYt1.c1,t2.c1 OFFSET10LIMIT10;
425+
-- full outer join + WHERE clause with shippable extensions set
426+
EXPLAIN (VERBOSE, COSTS OFF)
427+
SELECTt1.c1,t2.c2,t1.c3FROM ft1 t1 FULLJOIN ft2 t2ON (t1.c1=t2.c1)WHERE postgres_fdw_abs(t1.c1)>0 OFFSET10LIMIT10;
428+
ALTER SERVER loopback OPTIONS (DROP extensions);
429+
-- full outer join + WHERE clause with shippable extensions not set
430+
EXPLAIN (VERBOSE, COSTS OFF)
431+
SELECTt1.c1,t2.c2,t1.c3FROM ft1 t1 FULLJOIN ft2 t2ON (t1.c1=t2.c1)WHERE postgres_fdw_abs(t1.c1)>0 OFFSET10LIMIT10;
432+
ALTER SERVER loopback OPTIONS (ADD extensions'postgres_fdw');
425433
-- join two tables with FOR UPDATE clause
426434
-- tests whole-row reference for row marks
427435
EXPLAIN (VERBOSE, COSTS OFF)

0 commit comments

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