Re: Join push-down support for foreign tables

> I seem to be getting a problem with whole-row references:
> 
> # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on
> e.person_id = p.id inner join countries c on p.country_id = c.id;
> ERROR:  table "r" has 3 columns available but 4 columns specified
> CONTEXT:  Remote SQL command: SELECT r.a_0, r.a_1, r.a_2, l.a_1 FROM (SELECT id,
> country FROM public.countries) l (a_0, a_1) INNER JOIN (SELECT id, name,
> country_id FROM public.people) r (a_0, a_1, a_2, a_3)  ON ((r.a_3 = l.a_0))
>
In this case, the 4th target-entry should be "l", not l.a_1.

> And the error message could be somewhat confusing.  This mentions table "r", but
> there's no such table or alias in my actual query.
>
However, do we have a mechanical/simple way to distinguish the cases when
we need relation alias from the case when we don't need it?
Like a self-join cases, we has to construct a remote query even if same
table is referenced multiple times in a query. Do you have a good idea?

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>


> -----Original Message-----
> From: thombrown@gmail.com [mailto:thombrown@gmail.com] On Behalf Of Thom Brown
> Sent: Monday, March 02, 2015 10:51 PM
> To: Shigeru Hanada
> Cc: Kaigai Kouhei(海外 浩平); Robert Haas; PostgreSQL-development
> Subject: ##freemail## Re: [HACKERS] Join push-down support for foreign tables
> 
> On 2 March 2015 at 12:48, Shigeru Hanada <shigeru.hanada@gmail.com> wrote:
> 
> 
>     Attached is the revised/rebased version of the $SUBJECT.
> 
>     This patch is based on Kaigai-san's custom/foreign join patch, so
>     please apply it before this patch.  In this version I changed some
>     points from original postgres_fdw.
> 
>     1) Disabled SELECT clause optimization
>     ~9.4 postgres_fdw lists only columns actually used in SELECT clause,
>     but AFAIS it makes SQL generation complex.  So I disabled such
>     optimization and put "NULL" for unnecessary columns in SELECT clause
>     of remote query.
> 
>     2) Extended deparse context
>     To allow deparsing based on multiple source relations, I added some
>     members to context structure.  They are unnecessary for simple query
>     with single foreign table, but IMO it should be integrated.
> 
>     With Kaigai-san's advise, changes for supporting foreign join on
>     postgres_fdw is minimized into postgres_fdw itself.  But I added new
>     FDW API named GetForeignJoinPaths() to keep the policy that all
>     interface between core and FDW should be in FdwRoutine, instead of
>     using hook function.  Now I'm writing document about it, and will post
>     it in a day.
> 
> 
> 
> I seem to be getting a problem with whole-row references:
> 
> # SELECT p.name, c.country, e.pet_name, p FROM pets e INNER JOIN people p on
> e.person_id = p.id inner join countries c on p.country_id = c.id;
> ERROR:  table "r" has 3 columns available but 4 columns specified
> CONTEXT:  Remote SQL command: SELECT r.a_0, r.a_1, r.a_2, l.a_1 FROM (SELECT id,
> country FROM public.countries) l (a_0, a_1) INNER JOIN (SELECT id, name,
> country_id FROM public.people) r (a_0, a_1, a_2, a_3)  ON ((r.a_3 = l.a_0))
> 
> 
> And the error message could be somewhat confusing.  This mentions table "r", but
> there's no such table or alias in my actual query.
> 
> 
> 
> Another issue:
> 
> # EXPLAIN VERBOSE SELECT NULL FROM (SELECT people.id FROM people INNER JOIN
> countries ON people.country_id = countries.id LIMIT 3) x;
> ERROR:  could not open relation with OID 0
> 
> 
> --
> 
> Thom