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
