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

On Tue, Aug 9, 2022 at 9:59 AM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:

> On Tue, Aug 9, 2022 at 12:26 AM Alexander Pyhalov
> <a.pyhalov@postgrespro.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