| From: | vaibhave postgres <postgresvaibhave(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | support parameterized (LATERAL) foreign joins |
| Date: | 2026-07-20 06:47:50 |
| Message-ID: | CAM_eQjwvQ1_3NyjUQePyR2H-hyU0YThmPOCVEWS85xL+SFsPyQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
create_foreign_join_path() currently rejects any parameterized foreign
join outright:
```
if (!bms_is_empty(required_outer) || !bms_is_empty(rel->lateral_relids))
elog(ERROR, "parameterized foreign joins are not supported yet");
```
and postgres_fdw's postgresGetForeignJoinPaths() side-steps that by
declining to push down any join whose relation has lateral references:
```
/* This code does not work for joins with lateral references ... */
if (!bms_is_empty(joinrel->lateral_relids))
return;
```
For example in the below query (foreign i1/i2, local o):
SELECT o.f1, ss.x
FROM local_tbl o,
LATERAL (SELECT 1 AS x
FROM (SELECT o.f1 AS lat, i2.f1 AS loc
FROM int8_tbl i1, int4_tbl i2) ss1
RIGHT JOIN int4_tbl i3 ON (i3.f1 > 1)
WHERE ss1.loc = ss1.lat) ss;
The RIGHT JOIN here keeps the LATERAL reference from being flattened into
an ordinary join clause, so the inner foreign join (i1 x i2) really does
require a parameterized path and is never pushed down.
The attached patch teaches the planner to build such paths and as an
example to postgres_fdw.
create_foreign_join_path() no longer errors on a parameterized request.
Instead it builds a ParamPathInfo via a new helper,
get_joinrel_parampathinfo_pushdown(joinrel, required_outer,
restrict_clauses, rows)
in relnode.c. The FDW does not have a pair of input paths
to describe how the join is formed, so instead it hands us the
parameterization, the estimated rowcount, and the RestrictInfos the path
will enforce.
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-postgres_fdw-support-parameterized-foreign-joins.patch | application/octet-stream | 20.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rafia Sabih | 2026-07-20 06:52:17 | Re: Allow table AMs to define their own reloptions |
| Previous Message | Zsolt Parragi | 2026-07-20 06:47:41 | Re: TDE: Benchmarking WAL encryption approaches |