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@entry=21, domain=domain@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@entry=32) at var.c:668
#4 0x00007344a35d4f10 in build_tlist_to_deparse
(foreignrel=foreignrel@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]/messages/by-id/TYRPR01MB13941CEA16574771B1BFD130595A02@TYRPR01MB13941.jpnprd01.prod.outlook.com, 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]: /messages/by-id/TYRPR01MB13941CEA16574771B1BFD130595A02@TYRPR01MB13941.jpnprd01.prod.outlook.com
/messages/by-id/TYRPR01MB13941CEA16574771B1BFD130595A02@TYRPR01MB13941.jpnprd01.prod.outlook.com

--
Best regards,
Alexander Pyhalov,
Postgres Professional

On Fri, 25 Sept 2026 at 12:20, Alexander Pyhalov
<a.pyhalov@postgrespro.ru> wrote:

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

Hmm, very interesting, before report I checked on 0ee83dd4a99 and for
me it differs:

reshke=# explain
SELECT count(1) FROM rmt.a a, rmt.a b GROUP BY a.id
;
QUERY PLAN
--------------------------------------------------------------------------------
Finalize GroupAggregate (cost=200.00..13594.87 rows=200 width=12)
Group Key: a.id
-> Nested Loop (cost=200.00..10179.87 rows=682600 width=12)
-> Partial GroupAggregate (cost=100.00..777.98 rows=200 width=12)
Group Key: a.id
-> Foreign Scan on a (cost=100.00..761.35 rows=2925 width=4)
-> Materialize (cost=100.00..877.93 rows=3413 width=0)
-> Foreign Scan on a b (cost=100.00..860.86 rows=3413 width=0)
(8 rows)

reshke=# explain
SELECT count(1) FROM rmt.a, generate_series(1,1) GROUP BY id
;
ERROR: Aggref found in non-Agg plan node

With 0ee83dd4a99~1 (e13851080c) both queries run OK

I'm not sure, does issue affect only joinrels? Can't we encounter other

grouped rels while building foreign paths?

I didn't manage to get any exposure other than join-grouped rels.

--
Best regards,
Kirill Reshke

On Fri, 25 Sept 2026 at 13:22, Kirill Reshke <reshkekirill@gmail.com> wrote:

On Fri, 25 Sept 2026 at 12:20, Alexander Pyhalov
<a.pyhalov@postgrespro.ru> wrote:

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

Hmm, very interesting, before report I checked on 0ee83dd4a99 and for
me it differs:

reshke=# explain
SELECT count(1) FROM rmt.a a, rmt.a b GROUP BY a.id
;
QUERY PLAN
--------------------------------------------------------------------------------
Finalize GroupAggregate (cost=200.00..13594.87 rows=200 width=12)
Group Key: a.id
-> Nested Loop (cost=200.00..10179.87 rows=682600 width=12)
-> Partial GroupAggregate (cost=100.00..777.98 rows=200 width=12)
Group Key: a.id
-> Foreign Scan on a (cost=100.00..761.35 rows=2925 width=4)
-> Materialize (cost=100.00..877.93 rows=3413 width=0)
-> Foreign Scan on a b (cost=100.00..860.86 rows=3413 width=0)
(8 rows)

reshke=# explain
SELECT count(1) FROM rmt.a, generate_series(1,1) GROUP BY id
;
ERROR: Aggref found in non-Agg plan node

With 0ee83dd4a99~1 (e13851080c) both queries run OK

I'm not sure, does issue affect only joinrels? Can't we encounter other

grouped rels while building foreign paths?

I didn't manage to get any exposure other than join-grouped rels.

--
Best regards,
Kirill Reshke

Looks like I wrongly blamed 0ee83dd4a99 as root cause.
On 8e11859102f947e6145acdd809e5cdcdfbe90fa5 (eager aggregation), I got

db1=# CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'db1', host 'localhost', port '5432');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE TABLE e1_small (id int, k int);
CREATE TABLE e1_mid (id int, ref int, val numeric(10,2), c text);
INSERT INTO e1_small SELECT i, i%23 FROM generate_series(1,1000) i;
INSERT INTO e1_mid SELECT i, i%997, (i%1000)::numeric,
chr(97 + i%23) FROM generate_series(1,50000) i;
CREATE FOREIGN TABLE ft_small (id int, k int)
SERVER loopback OPTIONS (table_name 'e1_small');
CREATE FOREIGN TABLE ft_mid (id int, ref int, val numeric(10,2), c text)
SERVER loopback OPTIONS (table_name 'e1_mid');
ANALYZE e1_small; ANALYZE e1_mid;
SELECT count(*) FROM ft_small WHERE k IN (SELECT ref FROM ft_mid
WHERE val>50) GROUP BY k;
CREATE EXTENSION
CREATE SERVER
CREATE USER MAPPING
CREATE TABLE
CREATE TABLE
INSERT 0 1000
INSERT 0 50000
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
ANALYZE
ANALYZE
ERROR: Aggref found where not expected

--
Best regards,
Kirill Reshke

On Sat, Sep 26, 2026 at 1:28 AM Kirill Reshke <reshkekirill@gmail.com> wrote:

Looks like I wrongly blamed 0ee83dd4a99 as root cause.
On 8e11859102f947e6145acdd809e5cdcdfbe90fa5 (eager aggregation), I got

ANALYZE
ERROR: Aggref found where not expected

FWIW, this is a duplicate of the bug reported by Robert (finding2 in
[1]: /messages/by-id/CA+Tgmob7iSM9YkRM44VjUDuaCchW-fY54MV5njpTZTL9uNyV4w@mail.gmail.com

[1]: /messages/by-id/CA+Tgmob7iSM9YkRM44VjUDuaCchW-fY54MV5njpTZTL9uNyV4w@mail.gmail.com

- Richard