Re: Join push-down support for foreign tables

Thanks for reviewing my patch.

2015-03-02 22:50 GMT+09:00 Thom Brown <thom(at)linux(dot)com>:
> 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.

Your concern would not be limited to the feature I'm proposing,
because fdw_options about object name also introduce such mismatch
between the query user constructed and another one which postgres_fdw
constructed for remote execution. Currently we put CONTEXT line for
such purpose, but it might hard to understand soon only from error
messages. So I'd like to add section about query debugging for
postgres_fdw.

> 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

Good catch. In my quick trial, removing LIMIT3 avoids this error.
I'll check it right now.

--
Shigeru HANADA