Re: Postgres_fdw join pushdown - wrong results with whole-row reference

On 2016/06/22 18:16, Ashutosh Bapat wrote:
> On Wed, Jun 22, 2016 at 2:26 PM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp <mailto:fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>> wrote:

> I think we could address this in another way once we support
> deparsing subqueries; rewrite the remote query into something that
> wouldn't need the CASE WHEN conversion. For example, we currently have:
>
> postgres=# explain verbose select ft2 from ft1 left join ft2 on
> ft1.a = ft2.a;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------
> Foreign Scan (cost=100.00..110.04 rows=1 width=32)
> Output: ft2.*
> Relations: (public.ft1) LEFT JOIN (public.ft2)
> Remote SQL: SELECT CASE WHEN r2.* IS NOT NULL THEN ROW(r2.a,
> r2.b) END FROM (public.t1 r1 LEFT JOIN public.t2 r2 ON (((r1.a =
> r2.a))))
> (4 rows)
>
> However, if we support deparsing subqueries, the remote query in the
> above example could be rewritten into something like this:
>
> SELECT ss.c2 FROM t1 LEFT JOIN (SELECT t2.a, ROW(a, b) FROM t2)
> ss(c1, c2) ON (t1.a = ss.c1);
>
> So we would no longer need "CASE WHEN r2.* IS NOT NULL THEN
> ROW(r2.a, r2.b) END" in the target list in the remote query.

> Right. Although, it means that the query processor at the other end has
> to do extra work for pulling up the subqueries.

Yeah, that's right. But this approach seems not so ugly...

> For the CASE WHEN conversion for a system column other than ctid, we
> could also address this by replacing the whole-row reference in the
> IS NOT NULL condition in that conversion with the system column
> reference.

> That would not work again as the system column reference would make
> sense locally but may not be available at the foreign server e.g.
> foreign table targeting a view a tableoid is requested.

Maybe I'm confused, but I think that in the system-column case it's the
user's responsibility to specify system columns for foreign tables in a
local query only when that makes sense on the remote end, as shown in
the below counter example:

postgres=# create foreign table fv1 (a int, b int) server myserver
options (table_name 'v1');
CREATE FOREIGN TABLE
postgres=# select ctid, * from fv1;
ERROR: column "ctid" does not exist
CONTEXT: Remote SQL command: SELECT a, b, ctid FROM public.v1

where v1 is a view created on the remote server "myserver".

Best regards,
Etsuro Fujita