Re: FDW RTE join pushdown fails to create plan with aggregates

Kirill Reshke писал(а) 2026-09-25 07:51:
> On head, planner fails to build a plan for foreign relation joined
> with RTE in cases where Eager Aggregation optimization is applicable.
>
> This issue exists starting at 0ee83dd4a99.
>
> repro:
>
> CREATE SCHEMA rmt;
> CREATE SERVER srv FOREIGN DATA WRAPPER postgres_fdw
> OPTIONS (dbname 'postgres', host 'localhost', port '5432');
> CREATE USER MAPPING FOR CURRENT_USER SERVER srv;
> CREATE FOREIGN TABLE rmt.a (id integer, k integer, v text)
> SERVER srv OPTIONS (schema_name 'r', table_name 'a');
>
>
> EXPLAIN
> SELECT count(1) FROM rmt.a, generate_series(1,1) GROUP BY id;
> ERROR: Aggref found where not expected
>
>
> bt:
> ```
> #0 errstart_cold (elevel=elevel(at)entry=21, domain=domain(at)entry=0x0) at
> elog.c:340
> #1 0x00005c3e6badfa57 in pull_var_clause_walker (node=<optimized
> out>, context=0x7ffce16693c0) at var.c:699
> #2 0x00005c3e6bda543b in expression_tree_walker_impl (node=<optimized
> out>, walker=0x5c3e6be5f3a0 <pull_var_clause_walker>,
> context=0x7ffce16693c0) at nodeFuncs.c:2544
> #3 0x00005c3e6be605d2 in pull_var_clause (node=<optimized out>,
> flags=flags(at)entry=32) at var.c:668
> #4 0x00007344a35d4f10 in build_tlist_to_deparse
> (foreignrel=foreignrel(at)entry=0x5c3ea98f9918) at deparse.c:1250
> #5 0x00007344a35e1922 in postgresGetForeignPlan (root=0x5c3ea98f3e88,
> foreignrel=0x5c3ea98f9918, foreigntableid=<optimized out>,
> best_path=0x5c3ea98fa558, tlist=0x0, scan_clauses=0x0, outer_plan=0x0)
> at postgres_fdw.c:1471
> #6 0x00005c3e6be1f268 in create_foreignscan_plan
> (scan_clauses=<optimized out>, tlist=0x0, best_path=0x5c3ea98fa558,
> root=0x5c3ea98f3e88) at createplan.c:4011
> #7 create_scan_plan (root=0x5c3ea98f3e88, best_path=0x5c3ea98fa558,
> flags=<optimized out>) at createplan.c:785
> #8 0x00005c3e6be1ad70 in create_projection_plan (root=0x5c3ea98f3e88,
> best_path=0x5c3ea98fbfa0, flags=6) at createplan.c:1907
> #9 0x00005c3e6be1bab6 in create_sort_plan (flags=4,
> best_path=0x5c3ea98fc6b0, root=0x5c3ea98f3e88) at createplan.c:2037
> #10 create_plan_recurse (root=0x5c3ea98f3e88,
> best_path=0x5c3ea98fc6b0, flags=4) at createplan.c:487
> ```
>
> So, eager aggregation optimization tries to pushdown relations with
> partial agg tle, which postgresGetForeignJoinPaths couldn't deparse,
> so there is an error.
>
> PFA simple patch adding guard for this exact case.
>
> In principle, we can make deparse more smarter and do actually
> pushdown partial agg, but looks like this is less likely to land in a
> short time.
>
>
> +CC Alexander Korotkov as committer of 0ee83dd4a99
> +CC Author Alexander Pyhalov as author

Hi.

Yes, there's an issue, but it seems to be not specific to function
pushdown.
For example, the following test case

EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(1) FROM remote_tbl r1, remote_tbl r2 GROUP BY r1.a;
ERROR: Aggref found where not expected

if we emit foreign path with low cost for grouped_rel (to test this I've
just set low Foreign Path cost in
make_grouped_join_rel() after populate_joinrel_with_paths()).

And yes, we can't deparse partial aggregates now. The last discussion of
the issue was here[1], but had been before
eager aggregation was introduced.

I'm not sure, does issue affect only joinrels? Can't we encounter other
grouped rels while building foreign paths?

[1]
https://www.postgresql.org/message-id/flat/TYRPR01MB13941CEA16574771B1BFD130595A02(at)TYRPR01MB13941(dot)jpnprd01(dot)prod(dot)outlook(dot)com#54ab5e31f1c46ec6f109667f09b0407f

--
Best regards,
Alexander Pyhalov,
Postgres Professional