Re: foreign join error "variable not found in subplan target list"

On Tue, Aug 9, 2022 at 7:54 PM Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru>
wrote:

> Richard Guo писал 2022-08-09 06:51:
> > On Tue, Aug 9, 2022 at 9:59 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> > wrote:
> >
> >> On Tue, Aug 9, 2022 at 12:26 AM Alexander Pyhalov
> >> <a(dot)pyhalov(at)postgrespro(dot)ru> wrote:
> >>> Using the following patch I'm able to get
> >>> "ERROR: variable not found in subplan target list"
> >>> on foreign join pushdown for update returning.
> >>
> >> Reproduced here. Will look into this.
> >>
> >> Thanks for the report!
> >
> > A rough look shows to me that the part of plan that causes problem
> > looks
> > like:
> >
> > -> Result
> > Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft2.c2
> > -> Sort
> > Output: ft2.ctid, ft2.*, ft4.*, ft4.c1
> > Sort Key: ft4.c1
> >
> > Note that for node 'Result', one of its target entries, 'ft2.c2', does
> > not appear in the target list of its subplan, i.e. node 'Sort'.
> >
> > I think something goes wrong in postgresGetForeignPlan() when we build
> > the list of columns to be fetched from the foreign server, and fix the
> > subplan's tlist with that, where we would include in columns specified
> > in local conditions. In this case the local condition is 'ft2.c2 ===
> > ft4.c1', and that's how we have entry 'ft2.c2' in Result's target
> > list.
> >
> > Thanks
> > Richard
>
> Hi.
> The issue seems to appear due to the fact that Sort path doesn't provide
> all vars, needed by local_conds.
> What if we check for this condition specifically?

I think this change may make us miss some EPQ path with pathkeys, which
seems not good. Also, Sort path would always have the same pathtarget as
its subpath. So if we really want to do this, we can do it earlier in
the caller of add_paths_with_pathkeys_for_rel.

Currently the outer_plan used in postgresGetForeignPlan() can only be
'Join' or 'Sort + Join'. I'm wondering whether we can take this
knowledge into consideration when we fix the outer_plan's tlist, to also
fix the Join's tlist if it is below the Sort node.

Thanks
Richard