Re: Foreign join pushdown vs EvalPlanQual

On Thu, Oct 8, 2015 at 11:00 PM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> The best plan is presumably something like this as you said before:
>>
>> LockRows
>> -> Nested Loop
>> -> Seq Scan on verysmall v
>> -> Foreign Scan on bigft1 and bigft2
>> Remote SQL: SELECT * FROM bigft1 JOIN bigft2 ON bigft1.x =
>> bigft2.x AND bigft1.q = $1 AND bigft2.r = $2
>>
>> Consider the EvalPlanQual testing to see if the updated version of a
>> tuple in v satisfies the query. If we use the column in the testing, we
>> would get the wrong results in some cases.
>
> More precisely, we would get the wrong result when the value of v.q or v.r
> in the updated version has changed.

Interesting test case. It's worth considering why this works if you
were to replace the Foreign Scan with an Index Scan; suppose the query
is SELECT * FROM verysmall v LEFT JOIN realbiglocaltable t ON v.x =
t.x FOR UPDATE OF v, so that you get:

LockRows
-> Nested Loop
-> Seq Scan on verysmall v
-> Foreign Scan on realbiglocaltable t
Index Cond: v.x = t.x

In your example, the remote SQL pushes down certain quals to the
remote server, and so if we just return the same tuple they might no
longer be satisfied. In this example, the index qual is essentially a
filter condition that has been "pushed down" into the index AM. The
EvalPlanQual machinery prevents this from generating wrong answers by
rechecking the index cond - see IndexRecheck. Even though it's
normally the AM's job to enforce the index cond, and the executor does
not need to recheck, in the EvalPlanQual case it does need to recheck.

I think the foreign data wrapper case should be handled the same way.
Any condition that we initially pushed down to the foreign server
needs to be locally rechecked if we're inside EPQ.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company