Fdw development

fdw-development — Foreign Data Wrappers

An FDW lets a PG backend access external data (another PG, MySQL, files, HTTP APIs) as if it were a local relation. The system supports:

• SELECT (with pushdown of quals, joins, aggregates, LIMITs).
• INSERT / UPDATE / DELETE (with pushdown for postgres_fdw).
• TRUNCATE.
• COPY FROM.
• Async scans (PG 14+).
• Extended-explain output.

The core FDW support is small — src/backend/foreign/foreign.c (22 KB) — and lives around the FdwRoutine callback struct. The heavy work is in per-wrapper contrib modules (contrib/postgres_fdw/ for PG-to-PG, contrib/file_fdw/ for CSV/text files).

The file map

File	Role
src/backend/foreign/foreign.c	Core FDW support — foreign-table cache, GetForeignServer / GetUserMapping helpers, wrapper's handler fn is looked up + cached here.
src/backend/commands/foreigncmds.c	CREATE / ALTER / DROP for SERVER / USER MAPPING / FOREIGN TABLE.
src/backend/executor/nodeForeignscan.c	ForeignScan executor node — dispatches to the FDW's Begin/Iterate/End callbacks.
src/backend/optimizer/util/appendinfo.c + plancat.c	Planner-side foreign-table hooks.
src/include/foreign/fdwapi.h	The FdwRoutine struct definition — 30+ callback pointers.
contrib/postgres_fdw/postgres_fdw.c	Reference implementation. Big and non-trivial (has to handle pushdown + connection pooling + prepared statements + async).
contrib/postgres_fdw/deparse.c	Deparse — turn PG parse-tree back into SQL text to send to the remote. Where "shipping expressions" lives.
contrib/postgres_fdw/option.c	Server/user-mapping/foreign-table options parsing.
contrib/postgres_fdw/connection.c	Libpq connection cache — one connection per (user, server) tuple, kept warm.

The FdwRoutine callback taxonomy

Grouped by lifecycle stage:

Planner-time

• GetForeignRelSize — populate baserel->rows estimate.
• GetForeignPaths — add ForeignPath entries to baserel->pathlist.
• GetForeignPlan — build the ForeignScan node from a picked Path.
• EstimateDSMForeignScan / InitializeDSMForeignScan / InitializeWorkerForeignScan — parallel-aware FDW support (rare).

Executor-time, scan

• BeginForeignScan — one-time setup (open connection, prepare remote statement).
• IterateForeignScan — return the next tuple (or NULL for end-of-stream).
• ReScanForeignScan — restart if re-iterated in a nested loop.
• EndForeignScan — clean up (release connection back to pool).

Executor-time, DML

• BeginForeignInsert / ExecForeignInsert / EndForeignInsert — INSERT path.
• BeginForeignModify / ExecForeignUpdate / ExecForeignDelete / EndForeignModify — UPDATE + DELETE.
• Also: BeginDirectModify / IterateDirectModify / EndDirectModify — for pushed-down "delete this whole range" queries.

Planner-time, pushdown decisions

• GetForeignJoinPaths — add ForeignPath for a foreign-side JOIN.
• GetForeignUpperPaths — add ForeignPath for GROUP BY / DISTINCT / LIMIT / etc. that can be pushed to the foreign side.
• IsForeignRelUpdatable — is UPDATE/DELETE supported on this table?

Extras

• ExplainForeignScan / ExplainForeignModify — populate the EXPLAIN output with FDW-specific info.
• AnalyzeForeignTable — support for local ANALYZE of a foreign table (sample rows).
• ImportForeignSchema — for IMPORT FOREIGN SCHEMA — return CreateForeignTableStmt list.

The postgres_fdw pushdown philosophy

By default, postgres_fdw tries to push as much work as possible to the remote:

• Quals — WHERE predicates that are shippable (see is_foreign_expr) go to the remote. Deterministic + non-volatile + no local-only functions.
• Joins — foreign-to-foreign joins on the same server get pushed as a subquery.
• Aggregates — GROUP BY / SUM / COUNT on foreign-only data pushed via GetForeignUpperPaths.
• LIMIT — pushed as long as ORDER BY is also shippable.
• UPDATE / DELETE — DirectModify — if the WHERE clause is shippable, PG sends one UPDATE/DELETE statement instead of scanning + updating row-by-row.

use_remote_estimate (server option) toggles whether to run EXPLAIN on the remote to get better cost estimates (slower to plan; better plans).

The password_required two-layered defense

A recurring security concern with postgres_fdw: an unprivileged user's foreign-table access could be abused to send commands as the FDW's connecting user (potentially a superuser on the remote).

Two-layered protection:

1. password_required = true (default) — the user mapping MUST provide a password. Excludes trust / peer auth from being used.
2. User mapping ownership — only the mapping's owner can use it (unless PUBLIC). Superuser + application user split.

Non-superusers who create user mappings CAN'T set password_required = false. This is the gold-standard defense pattern; audit any FDW you write for equivalent.

Common patch shapes

Write a new FDW (minimum viable)

1. _PG_init in the extension registers the wrapper via pg_foreign_data_wrapper (create-time SQL).
2. Handler function returns FdwRoutine * filled with your callbacks.
3. Minimal callbacks: GetForeignRelSize, GetForeignPaths, GetForeignPlan, BeginForeignScan, IterateForeignScan, EndForeignScan, ReScanForeignScan.
4. ExplainForeignScan — even a stub — makes debugging much easier.
5. Test via CREATE EXTENSION my_fdw; CREATE SERVER ...; CREATE USER MAPPING ...; CREATE FOREIGN TABLE ....
6. See contrib/file_fdw/ for the simplest example.

Add a new pushdown

Consider carefully — pushdown is where FDWs get correctness bugs:

• Add a case in is_foreign_expr for the expression you want to push.
• Verify the deparse.c handles it — deparseExpr needs to know how to write it back as SQL.
• Add regression coverage — every pushdown case needs a test that verifies:
  • Result correctness.
  • EXPLAIN shows the pushed-down form.
  • It DOESN'T push when a non-shippable qual is involved.

Cache connection to remote

• Per-user, per-server connection cache in contrib/postgres_fdw/connection.c.
• Held in the backend's process-lifetime (no shmem).
• Re-established on subtransaction abort — connection state is torn down.
• If you're writing an FDW that talks to something with expensive connection setup (HTTP, etc.), copy this pattern.

Debug "postgres_fdw isn't pushing my WHERE clause"

• EXPLAIN VERBOSE — shows the exact SQL being sent to remote as Remote SQL.
• Common blockers: qual uses a volatile function, references a local table, uses a subquery not shippable.
• SET postgres_fdw.debug_pushdown = on — logs pushdown decisions.
• Check if the qual is a JOIN condition — join pushdown has stricter rules.

Pitfalls

• User mapping password can leak — if the FDW forwards the password to a debug/error message. postgres_fdw scrubs but a naive new FDW may not.
• Loopback to self bypasses RLS — creating a postgres_fdw server pointing at the same cluster with a superuser mapping lets you bypass RLS on your own database. Well-documented but frequently misused.
• Volatility marking of remote functions — the planner assumes remote functions have the volatility marked on the local shell function. Mismatch → wrong plans (caching wrong result).
• Async scans require careful state — the async API (ForeignAsyncRequest etc.) has multiple interleaved iterators. Managing state per-iterator is fiddly.
• User Mapping owner vs current_user — some FDWs check the "connect as" user carefully; others don't. Confusing "who owns the mapping" with "who's using it" is a security bug source.
• fdw_startup_cost + fdw_tuple_cost — the planner uses these to decide whether to bother pushing down. Wrong defaults produce nonsensical plans.
• use_remote_estimate for JOINs — enabling it can turn planning into O(N²) remote round-trips for cross-joins. Enable per-table where it helps.
• DML doesn't push implicitly — INSERT ... SELECT doesn't push the SELECT's INSERT logic; it fetches the rows to local then re-INSERTs. Only DirectModify (UPDATE/DELETE with all-remote WHERE) pushes.
• postgres_fdw prepared-statement cache is per-connection — if the connection restarts, all prepared statements are re-prepared.

Related corpus

• Idioms: fdw-iterate-scan (the pull-next-tuple discipline), fdw-routine-callbacks (the FdwRoutine struct), cursor-and-portal (foreign-scan portals).
• Subsystems: foreign (this skill's home subsystem), executor (ForeignScan node), contrib-postgres_fdw (the reference), contrib-file_fdw (the simplest example), contrib-dblink (different pattern — imperative not declarative FDW).
• Sessions: 2026-06-04-a11-contrib-top.md (deep-read of the top-4 contrib modules including postgres_fdw's password_required gold-standard).

Corpus-chain shortcut

python3 scripts/corpus-chain.py --idiom fdw-routine-callbacks
python3 scripts/corpus-chain.py --file contrib/postgres_fdw/postgres_fdw.c

Second surfaces the reference implementation's neighborhood: 6 files in the contrib module.

Boundary

Use this skill for FDW architecture + postgres_fdw internals + writing new FDWs.

Don't use for:

• Specific non-core FDWs (oracle_fdw / mysql_fdw / etc.) — external contribs with their own idioms.
• dblink — different pattern (per-call connection + explicit dblink('SELECT ...')), not an FDW.
• postgres_fdw bug fixing at the connection layer — if you're on the wire protocol level, wire-protocol skill has the byte-level details.
• ImportForeignSchema DDL processing — related but handled by commands/foreigncmds.c; that's more DDL-plumbing than FDW-authoring.