Thread

Re: Function scan FDW pushdown

Alexander Pyhalov <a.pyhalov@postgrespro.ru> — 2026-05-20T10:17:04Z

Alexander Korotkov писал(а) 2026-05-19 21:21:
> Good evening!
> 
> On Tue, May 19, 2026 at 6:25 PM Alexander Pyhalov
> <a.pyhalov@postgrespro.ru> wrote:
>> 
>> Found one more issue in whole row var deparsing. It can appear on a
>> nullable outer side, and we should use the same logic as when 
>> deparsing
>> table column reference. Otherwise we get records from nulls instead of
>> nulls (for example, "(NULL, NULL)" instead of NULL).
> 
> 
> Good catch, accepted.
> 

Hi.

I've found another issue. The fact that in the new versions of the patch 
RTE RelOptInfo misses fdw_private seems to be unfortunate. For example, 
in the last version we haven't thought about classifying 
baserestrictinfo. And if we do, we should pass fdw_private down to 
foreign_expr_walker. Perhaps, we could attach it to RTE_FUNCTION rel 
prior to calling classifyConditions(), but should we later set it back 
to NULL? Another problem comes if we try to handle joins,  which can 
crearte subqueries (like INNER/OUTER UNIQUE). In this case we should 
somehow cook fpinfo for get_relation_column_alias_ids(). Attaching patch 
which tries to handle baserestrictinfos by passing fpinfo down to 
foreign_expr_walker().

One more interesting example (included in the patch) is

EXPLAIN (VERBOSE, COSTS OFF)
WITH s AS MATERIALIZED (SELECT r1.* FROM remote_tbl r1
JOIN LATERAL
(SELECT r2.a FROM remote_tbl r2, f(r1.a) LIMIT 1) s
ON true)
SELECT * FROM s ORDER BY 1;

We get the following plan:

  Sort
    Output: s.a, s.b
    Sort Key: s.a
    CTE s
      ->  Nested Loop
            Output: r1.a, r1.b
            ->  Foreign Scan on public.remote_tbl r1
                  Output: r1.a, r1.b
                  Remote SQL: SELECT a, b FROM public.base_tbl_fn
            ->  Foreign Scan
                  Output: NULL::integer
                  Relations: (public.remote_tbl r2) INNER JOIN (Function 
f)
                  Remote SQL: SELECT NULL FROM (public.base_tbl_fn r1 
INNER JOIN public.f($1::integer) f2(c1) ON (TRUE)) LIMIT 1::bigint
    ->  CTE Scan on s
          Output: s.a, s.b

Here you can see that we use parameter in function argument. Don't know 
if it's a real problem, but at least looks suspicious. In v3 patch used 
contain_param_walker() in is_nonrel_relinfo_ok() (which mutated to 
function_rte_pushdown_ok()) to avoid such plans.

One minor issue I've noticed is in function_rte_pushdown_ok():
+       if (rel->rtekind != RTE_FUNCTION)
+               return false;
+       rte = planner_rt_fetch(rel->relid, root);
+       if (rte->rtekind != RTE_FUNCTION)
+               return false;

Is the second rtekind check necessary?
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional