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

Richard Guo писал 2022-08-10 11:36:
> On Wed, Aug 10, 2022 at 3:06 PM Alexander Pyhalov
> <a(dot)pyhalov(at)postgrespro(dot)ru> wrote:
>
>> Richard Guo писал 2022-08-10 08:28:
>>> On Wed, Aug 10, 2022 at 10:15 AM Richard Guo
>> <guofenglinux(at)gmail(dot)com>
>>> wrote:
>>>
>>>> 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.
>>>
>>> Alternatively, how about we include in the EPQ path's pathtarget
>>> thecolumns required for evaluating the local conditions when we
>>> consider
>>> EPQ paths with pathkeys? Something like attached.
>>>
>>> Thanks
>>> Richard
>>
>> Hi.
>> Why are we sure that epq_path can provide all vars from
>> restrictinfo?
>
> The local conditions come from the joinrel's restrictlist, which
> contains all the clauses that syntactically belong at the join level.
> So
> I think the join path for EPQ checks should be able to provide all the
> exprs needed by local_conds.
>
> Thanks
> Richard

OK. It looks good to me. The only thing which surprised me that in test
case we see unnecessary sort in remote query. However, it's explained by
selected costs and STD_FUZZ_FACTOR, so that sorted path has essentially
the same cost as non-sorted one according to
compare_path_costs_fuzzily().

EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*;


QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7,
ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING
"C 1", c2, c3, c4, c5, c6, c7, c8
-> Hash Join
Output: 'baz'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1,
ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Hash Cond: (ft5.c1 = ft4.c1)
-> Foreign Scan on public.ft5
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
-> Hash
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft4.c2, ft4.c3,
ft2.c2
-> Foreign Scan
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft4.c2,
ft4.c3, ft2.c2
Filter: (ft2.c2 === ft4.c1)
Relations: (public.ft2) INNER JOIN (public.ft4)
Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text
IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7,
r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2,
r2.c3) END, r2.c1, r2.c2, r2.c3, r1.c2 FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 3" r2 ON (((r1."C 1" > 2000)))) ORDER BY r2.c1 ASC NULLS LAST
FOR UPDATE OF r1
-> Sort
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1,
ft4.c2, ft4.c3, ft2.c2
Sort Key: ft4.c1
-> Nested Loop
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1,
ft4.c2, ft4.c3, ft2.c2
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.*, ft2.c2
Remote SQL: SELECT "C 1", c2, c3,
c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR
UPDATE
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1, ft4.c2,
ft4.c3
Remote SQL: SELECT c1, c2, c3
FROM "S 1"."T 3"

--
Best regards,
Alexander Pyhalov,
Postgres Professional