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

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?

--
Best regards,
Alexander Pyhalov,
Postgres Professional