Re: Foreign join pushdown vs EvalPlanQual

Hello,

At Thu, 1 Oct 2015 17:50:25 +0900, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <560CF3D1(dot)9060305(at)lab(dot)ntt(dot)co(dot)jp>
> On 2015/10/01 11:15, Kouhei Kaigai wrote:
> >> From: pgsql-hackers-owner(at)postgresql(dot)org
> >> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Robert Haas
> >> On Mon, Sep 28, 2015 at 11:15 PM, Etsuro Fujita
> >> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> >> So, if we wanted to fix this in a way that preserves the spirit of
> >> what's there now, it seems to me that we'd want the FDW to return
> >> something that's like a whole row reference, but represents the output
> >> of the foreign join rather than some underlying base table. And then
> >> get the EPQ machinery to have the evaluation of the ForeignScan for
> >> the join, when it happens in an EPQ context, to return that tuple.
> >> But I don't really have a good idea how to do that.
>
> > Alternative built-in join execution?
> > Once it is executed under the EPQ context, built-in join node fetches
> > a tuple from both of inner and outer side for each. It is eventually
> > fetched from the EPQ slot, then the alternative join produce a result
> > tuple.
> > In case when FDW is not designed to handle join by itself, it is
> > a reasonable fallback I think.
> >
> > I expect FDW driver needs to handle EPQ recheck in the case below:
> > * ForeignScan on base relation and it uses late row locking.
> > * ForeignScan on join relation, even if early locking.
>
> I also think the approach would be one choice. But one thing I'm
> concerned about is plan creation for that by the FDW author; that
> would make life hard for the FDW author. (That was proposed by me
> ...)
>
> So, I'd like to investigate another approach that preserves the
> applicability of late row locking to the join pushdown case as well as
> the spirit of what's there now. The basic idea is (1) add a new
> callback routine RefetchForeignJoinRow that refetches one foreign-join
> tuple from the foreign server, after locking remote tuples for the
> component foreign tables if required,

It would be the case that at least one of the component relations
of a foreign join is other than ROW_MARK_COPY, which is not
possible so far on postgres_fdw. For the case that some of the
component relations are other than ROW_MARK_COPY, we might should
call RefetchForeignRow for such relations and construct joined
row involving ROW_MARK_COPY relations.

Indeed we could consider some logic for the case, it is obvious
that the case now we should focus on is a "foreign join" scan
with all underlying foreign scans are ROW_MARK_COPY, I
think. "foreign join" scan with ROW_MARK_COPY looks to be
promising (for me) and in future it would be able to coexist with
refetch mechanism maybe in your mind from this point of
view... Maybe:p

> and (2) call that routine in
> ExecScanFetch if the target scan is for a foreign join and the
> component foreign tables require to be locked lately, else just return
> the foreign-join tuple stored in the parent's state tree, which is the
> tuple mentioned by Robert, for preserving the spirit of what's there
> now. I think that ExecLockRows and EvalPlanQualFetchRowMarks should
> probably be modified so as to skip foreign tables involved in a
> foreign join.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center