Re: 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