Re: Foreign join pushdown vs EvalPlanQual - Mailing list pgsql-hackers

On 2015/10/20 13:11, Etsuro Fujita wrote:
> On 2015/10/20 5:34, Robert Haas wrote:
>> On Mon, Oct 19, 2015 at 3:45 AM, Etsuro Fujita
>> <fujita.etsuro@lab.ntt.co.jp> wrote:
>>> As Tom mentioned, just recomputing the original join tuple is not good
>>> enough.  We would need to rejoin the test tuples for the baserels
>>> even if
>>> ROW_MARK_COPY is in use.  Consider:
>>>
>>> A=# BEGIN;
>>> A=# UPDATE t SET a = a + 1 WHERE b = 1;
>>> B=# SELECT * from t, ft1, ft2
>>>       WHERE t.a = ft1.a AND t.b = ft2.b AND ft1.c = ft2.c FOR UPDATE;
>>> A=# COMMIT;
>>>
>>> where the plan for the SELECT FOR UPDATE is
>>>
>>> LockRows
>>> -> Nested Loop
>>>     -> Seq Scan on t
>>>     -> Foreign Scan on <ft1, ft2>
>>>          Remote SQL: SELECT * FROM ft1 JOIN ft2 WHERE ft1.c = ft2.c
>>> AND ft1.a
>>> = $1 AND ft2.b = $2
>>>
>>> If an EPQ recheck is invoked by the A's UPDATE, just recomputing the
>>> original join tuple from the whole-row image that you proposed would
>>> output
>>> an incorrect result in the EQP recheck since the value a in the updated
>>> version of a to-be-joined tuple in t would no longer match the value
>>> ft1.a
>>> extracted from the whole-row image if the A's UPDATE has committed
>>> successfully.  So I think we would need to rejoin the tuples
>>> populated from
>>> the whole-row images for the baserels ft1 and ft2, by executing the
>>> secondary plan with the new parameter values for a and b.

>> No.  You just need to populate fdw_recheck_quals correctly, same as
>> for the scan case.

> Yeah, I think we can probably do that for the case where a pushed-down
> join clause is an inner-join one, but I'm not sure that we can do that
> for the case where that clause is an outer-join one.  Maybe I'm missing
> something, though.

As I said yesterday, that opinion of me is completely wrong.  Sorry for 
the incorrectness.  Let me explain a little bit more.  I still think 
that even if ROW_MARK_COPY is in use, we would need to locally rejoin 
the tuples populated from the whole-row images for the foreign tables 
involved in a remote join, using a secondary plan.  Consider eg,

SELECT localtab.*, ft2 from localtab, ft1, ft2 WHERE ft1.x = ft2.x AND ft1.y = localtab.y FOR UPDATE

In this case, since the output of the foreign join would not include any 
ft1 columns, I don't think we could do the same thing as for the scan 
case, even if populating fdw_recheck_quals correctly.  And I think we 
would need to rejoin the tuples, using a local join execution plan, 
which would have the parameterization for the to-be-pushed-down clause 
ft1.y = localtab.y.  I'm still missing something, though.

Best regards,
Etsuro Fujita