Horton

Re: postgres_fdw bug in 9.6

Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>

View thread View on postgr.es
From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Jeff Janes <jeff.janes@gmail.com>, Robert Haas <robertmhaas@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2016-12-21T12:44:01Z
Lists: pgsql-hackers

Attachments

On 2016/12/20 0:37, Tom Lane wrote:
> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
>> On 2016/12/17 1:13, Tom Lane wrote:
>>> So I think the rule could be

>>> "When first asked to produce a path for a given foreign joinrel, collect
>>> the cheapest paths for its left and right inputs, and make a nestloop path
>>> (or hashjoin path, if full join) from those, using the join quals needed
>>> for the current input relation pair.

>> Seems reasonable.

>>> Use this as the fdw_outerpath for
>>> all foreign paths made for the joinrel."

>> I'm not sure that would work well for foreign joins with sort orders.
>> Consider a merge join, whose left input is a 2-way foreign join with a
>> sort order that implements a full join and whose right input is a sorted
>> local table scan.  If the EPQ subplan for the foreign join wouldn't
>> produce the right sort order, the merge join might break during EPQ
>> rechecks (note that in this case the EPQ subplan for the foreign join
>> might produce more than a single row during an EPQ recheck).

> How so?  We only recheck one row at a time, therefore it can be claimed to
> have any sort order you care about.

I'll have second thoughts about that.  I agree with you except for that, 
so I've created a patch; I removed GetExistingLocalJoinPath and added a 
helper function, CreateLocalJoinPath, that generates a local join path 
for a given foreign join, as described above.  Please find attached a patch.

Best regards,
Etsuro Fujita

Commits

  1. Fix test case for 'outer pathkeys do not match mergeclauses' fix.

  2. postgres_fdw: Avoid 'outer pathkeys do not match mergeclauses' error.

  3. postgres_fdw: Consider foreign joining and foreign sorting together.

  4. Allow foreign and custom joins to handle EvalPlanQual rechecks.

  5. Allow FDWs to push down quals without breaking EvalPlanQual rechecks.