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

On Fri, 25 Sept 2026 at 13:22, Kirill Reshke <reshkekirill(at)gmail(dot)com> wrote:
>
> On Fri, 25 Sept 2026 at 12:20, Alexander Pyhalov
> <a(dot)pyhalov(at)postgrespro(dot)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