On 2016/06/07 19:13, Ashutosh Bapat wrote:
> I thought, columns of inner relation will be set to null during projection
> from ForeignScan for joins. But I was wrong. If we want to push-down joins
> in this case, we have two solutions
> 1. Build queries with subqueries at the time of deparsing. Thus a base
> relation or join has to include placeholders while being deparsed as a
> subquery. This means that the deparser should deparse expression
> represented by the placeholder. This may not be possible always.
> 2. At the time of projection from ForeignScan recognize the nullable
> placeholders and nullify them if the corresponding relation is nullified.
> That looks like a major surgery.
> So, your patch looks to be the correct approach (even after we support
> deparsing subqueries). Can you please include a test in regression?
I added a slightly modified version of your test to the originally posted
patch.
Thanks,
Amit.
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 1de0bc4..2dcce36 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2053,6 +2053,28 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
1
(10 rows)
+-- query which introduces placeholders in the targetlist
+EXPLAIN (COSTS false, VERBOSE)
+SELECT q.a FROM (SELECT 3 as a FROM ft1 WHERE 10 = ft1.c1) q RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 = 10;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: (3)
+ Join Filter: (3 = ft2.c1)
+ -> Foreign Scan on public.ft2
+ Output: ft2.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE (("C 1" = 10)) ORDER BY "C 1" ASC NULLS LAST
+ -> Foreign Scan on public.ft1
+ Output: 3
+ Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((10 = "C 1"))
+(9 rows)
+
+SELECT q.a FROM (SELECT 3 as a FROM ft1 WHERE 10 = ft1.c1) q RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 = 10;
+ a
+---
+
+(1 row)
+
-- create another user for permission, user mapping, effective user tests
CREATE USER view_owner;
-- grant privileges on ft4 and ft5 to view_owner
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 4d17272..ec86b9a 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -4036,6 +4036,20 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
return false;
}
+ /*
+ * Cannot push down if any PlaceHolderVars in its result are needed above
+ * the join.
+ */
+ foreach(lc, root->placeholder_list)
+ {
+ PlaceHolderInfo *phinfo = lfirst(lc);
+ Relids relids = joinrel->relids;
+
+ if (bms_nonempty_difference(phinfo->ph_needed, relids) &&
+ bms_is_subset(phinfo->ph_eval_at, relids))
+ return false;
+ }
+
/* Save the join clauses, for later use. */
fpinfo->joinclauses = joinclauses;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 6c2b08c..8970fd6 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -484,6 +484,10 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
EXPLAIN (COSTS false, VERBOSE)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
+-- query which introduces placeholders in the targetlist
+EXPLAIN (COSTS false, VERBOSE)
+SELECT q.a FROM (SELECT 3 as a FROM ft1 WHERE 10 = ft1.c1) q RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 = 10;
+SELECT q.a FROM (SELECT 3 as a FROM ft1 WHERE 10 = ft1.c1) q RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 = 10;
-- create another user for permission, user mapping, effective user tests
CREATE USER view_owner;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
