Technical Analysis: Fix Infinite Recursion When Foreign Table References Itself

The Core Problem

When a foreign table is created using postgres_fdw with a loopback server configuration that points back to the same database and references itself (i.e., the foreign table's table_name option points to its own name), any DML operation triggers infinite recursive connection attempts. Each INSERT spawns a remote connection that attempts the same INSERT, which spawns another connection, and so on until the server exhausts max_connections, producing a cascading too many clients already error with a deeply nested error context stack.

This is fundamentally a resource exhaustion issue rather than a crash or data corruption bug. The behavior is deterministic and bounded by max_connections, so the system does eventually halt with an error — it just consumes all available connection slots in the process.

Architectural Context

The postgres_fdw extension operates through PostgreSQL's Foreign Data Wrapper (FDW) API, which is designed to be generic across arbitrary foreign data sources. The architecture intentionally separates:

1. Core FDW infrastructure (src/backend/commands/foreigncmds.c) — handles DDL for foreign servers, user mappings, and foreign tables
2. FDW-specific logic (e.g., contrib/postgres_fdw/) — implements the actual data access methods and connection management

This separation is a key architectural constraint that makes the proposed fix problematic.

The Proposed Patch

The patch adds a check at CREATE FOREIGN TABLE time in foreigncmds.c that:

• Inspects the table_name option and compares it to the foreign table being created
• Inspects the dbname option on the server and compares it to the current database
• Raises ERROR 42P16 (invalid table definition) if both match, with a hint about circular references

Why the Patch Is Architecturally Unsound

Tomas Vondra's response identifies several fundamental problems that go beyond simple implementation bugs:

1. Layer Violation

The table_name and dbname options are semantics defined by postgres_fdw, not by the core FDW framework. The core infrastructure in foreigncmds.c has no business interpreting FDW-specific options. Another FDW could use table_name with entirely different semantics, or the same loopback pattern might be intentional for a different FDW implementation.

2. Incomplete Identity Resolution

Determining whether a foreign table "points to itself" requires resolving network identity — whether localhost, 127.0.0.1, ::1, the machine's hostname, or any other address all resolve to the same instance. This is fundamentally undecidable at DDL time, especially with connection pools, proxies, DNS changes, or container networking.

3. Incomplete Coverage of Mutation Paths

The check only covers CREATE FOREIGN TABLE, but the self-referential condition can be established through:

• ALTER FOREIGN TABLE ... OPTIONS (SET table_name '...')
• ALTER SERVER ... OPTIONS (SET dbname '...')
• Renaming the foreign table itself (ALTER FOREIGN TABLE ... RENAME TO ...)
• Changes to user mappings or server options after creation

4. The General Case Is Unsolvable

Even if self-reference were detectable, mutual reference between two servers (A→B→A) produces the identical resource exhaustion. Detecting arbitrary cycles in a distributed graph of foreign table references is not feasible at DDL time — it would require querying remote servers, which themselves might be unreachable.

Design Philosophy Implications

This thread illustrates a broader PostgreSQL design principle: the system should not prevent configurations that might fail at runtime if detecting the problematic condition is unreliable or incomplete. The existing behavior — exhausting connections and returning an error — is considered an acceptable outcome because:

1. It doesn't corrupt data
2. It's bounded (limited by max_connections)
3. It's diagnosable from the error message
4. The "fix" would be incomplete and create false positives

This aligns with PostgreSQL's general approach of allowing potentially dangerous configurations (like circular replication topologies) while documenting the pitfalls, rather than implementing incomplete guardrails that create a false sense of safety.

Conclusion

The thread was effectively closed with a single review response. The proposal addresses a real usability issue (confusing error when self-referencing), but the architectural cost of the solution — layer violations, incomplete detection, and false positives — far exceeds the benefit. The correct mitigation is documentation or, potentially, a connection-depth limit within postgres_fdw itself (in contrib/, not in core), though even that was not proposed.