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

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru>
Cc: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: foreign join error "variable not found in subplan target list"
Date: 2022-08-10 02:15:52
Message-ID: CAMbWs4_xn7drbNBDSLKGD7nDLxM3aMOSTXz6CbWugPJ-7JzZbg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Treat 2022-08-10 02:51:08 Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever
Previous Message Maxim Boguk 2022-08-09 23:27:49 Re: BUG #17574: Attaching an invalid index to partition head make head index invalid forever