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(at)ak(dot)jp(dot)nec(dot)com>

> -----Original Message-----
> From: thombrown(at)gmail(dot)com [mailto:thombrown(at)gmail(dot)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(dot)hanada(at)gmail(dot)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