On Tue, Jun 7, 2016 at 11:36 AM, Amit Langote <langote_amit...@lab.ntt.co.jp
> wrote:
> On 2016/06/05 23:01, Andreas Seltenreich wrote:
> > Creating some foreign tables via postgres_fdw in the regression db of
> > master as of de33af8, sqlsmith triggers the following assertion:
> >
> > TRAP: FailedAssertion("!(((((const Node*)(var))->type) == T_Var))",
> File: "deparse.c", Line: 1116)
> >
> > gdb says var is holding a T_PlaceHolderVar instead. In a build without
> > assertions, it leads to an error later:
> >
> > ERROR: cache lookup failed for type 0
> >
> > Recipe:
> >
> > --8<---------------cut here---------------start------------->8---
> > create extension postgres_fdw;
> > create server myself foreign data wrapper postgres_fdw;
> > create schema fdw_postgres;
> > create user mapping for public server myself options (user :'USER');
> > import foreign schema public from server myself into fdw_postgres;
> > select subq_0.c0 as c0 from
> > (select 31 as c0 from fdw_postgres.a as ref_0
> > where 93 >= ref_0.aa) as subq_0
> > right join fdw_postgres.rtest_vview5 as ref_1
> > on (subq_0.c0 = ref_1.a )
> > where 92 = subq_0.c0;
> > --8<---------------cut here---------------end--------------->8---
>
The repro assumes existence of certain tables/views e.g. rtest_vview5, a in
public schema. Their definition is not included here. Although I could
reproduce the issue by adding a similar query in the postgres_fdw
regression tests (see attached patch).
>
> Thanks for the example. It seems that postgres_fdw join-pushdown logic
> (within foreign_join_ok()?) should reject a join if any PlaceHolderVars in
> its targetlist are required above it. Tried to do that with the attached
> patch which trivially fixes the reported assertion failure.
>
Although the patch fixes the issue, it's restrictive. The placeholder Vars
can be evaluated locally after the required columns are fetched from the
foreign server. The right fix, therefore, is to build targetlist containing
only the Vars that belong to the foreign tables, which in this case would
contain "nothing". Attached patch does this and fixes the issue, while
pushing down the join. Although, I haven't tried the exact query given in
the report. Please let me know if the patch fixes issue with that query as
well.
The query generated by sqlsmith doesn't seem to return any result since it
assigns 31 to subq_0.c0 and the WHERE clause checks 92 =subq_0.c0. Is that
expected?
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 35c27e7..f72cff6 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -724,21 +724,23 @@ deparse_type_name(Oid type_oid, int32 typemod)
List *
build_tlist_to_deparse(RelOptInfo *foreignrel)
{
List *tlist = NIL;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
/*
* We require columns specified in foreignrel->reltarget->exprs and those
* required for evaluating the local conditions.
*/
- tlist = add_to_flat_tlist(tlist, foreignrel->reltarget->exprs);
+ tlist = add_to_flat_tlist(tlist,
+ pull_var_clause((Node *) foreignrel->reltarget->exprs,
+ PVC_RECURSE_PLACEHOLDERS));
tlist = add_to_flat_tlist(tlist,
pull_var_clause((Node *) fpinfo->local_conds,
PVC_RECURSE_PLACEHOLDERS));
return tlist;
}
/*
* Deparse SELECT statement for given relation into buf.
*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 1de0bc4..50fb5c3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2046,20 +2046,37 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
1
1
1
1
1
1
1
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 9 = q.a;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: 3
+ Filter: (9 = 3)
+ Relations: (public.ft2) LEFT JOIN (public.ft1)
+ Remote SQL: SELECT NULL FROM ("S 1"."T 1" r2 LEFT JOIN "S 1"."T 1" r4 ON (((3 = r2."C 1")) AND ((10 >= r4."C 1"))))
+(5 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 9 = q.a;
+ a
+---
+(0 rows)
+
-- create another user for permission, user mapping, effective user tests
CREATE USER view_owner;
-- grant privileges on ft4 and ft5 to view_owner
GRANT ALL ON ft4 TO view_owner;
GRANT ALL ON ft5 TO view_owner;
-- prepare statement with current session user
PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 6c2b08c..4db2b77 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -477,20 +477,24 @@ EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
-- Aggregate after UNION, for testing setrefs
EXPLAIN (COSTS false, VERBOSE)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
-- join with lateral reference
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 9 = q.a;
+SELECT q.a FROM (SELECT 3 as a FROM ft1 WHERE 10 >= ft1.c1) q RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE 9 = q.a;
-- create another user for permission, user mapping, effective user tests
CREATE USER view_owner;
-- grant privileges on ft4 and ft5 to view_owner
GRANT ALL ON ft4 TO view_owner;
GRANT ALL ON ft5 TO view_owner;
-- prepare statement with current session user
PREPARE join_stmt AS SELECT t1.c1, t2.c1 FROM ft4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
EXPLAIN (COSTS OFF, VERBOSE) EXECUTE join_stmt;
EXECUTE join_stmt;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
