FDW RTE join pushdown fails to create plan with aggregates

From: Kirill Reshke <reshkekirill(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Alexander Korotkov <aekorotkov(at)gmail(dot)com>, a(dot)pyhalov(at)postgrespro(dot)ru
Subject: FDW RTE join pushdown fails to create plan with aggregates
Date: 2026-09-25 04:51:42
Message-ID: CALdSSPh9SYVD3LRot6_fVtSt=jW=S=iVPEU-onJ-5ozn64n=Qw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

--
Best regards,
Kirill Reshke

Attachment Content-Type Size
v1-0001-Guard-FDW-deparse-from-relations-with-partial-agg.patch application/octet-stream 3.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Trakshan Mishra 2026-09-25 04:56:52 Re: Re: Re: [PATCH] Fix segmentation fault caused by reentrancy in RI_Fkey_cascade_del (ri_triggers.c)
Previous Message shihao zhong 2026-09-25 04:45:01 ON CONFLICT DO SELECT returns rows hidden by a view