Re: Postgres_fdw join pushdown - wrong results with whole-row reference - Mailing list pgsql-hackers

On 2016/06/22 18:16, Ashutosh Bapat wrote:

On Wed, Jun 22, 2016 at 2:26 PM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@lab.ntt.co.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