Thread

Re: [PATCH] Fix infinite recursion when foreign table references itself

Tomas Vondra <tomas@vondra.me> — 2026-05-12T14:43:48Z

On 5/12/26 16:06, John Mikk wrote:
> Hi, hackers.
> 
> If you create a foreign table referencing itself on a loopback server, an unpleasant error occurs:
> 
> ```sql
> create extension if not exists postgres_fdw;
> 
> drop server if exists loopback cascade ;
> create server loopback
> foreign data wrapper postgres_fdw
> options (dbname 'postgres', host 'localhost');
> 
> create user mapping for current_user server loopback;
> 
> create foreign table test_self (id int)
> server loopback options (table_name 'test_self');
> --> Ok
> 
> insert into test_self select 1;
> --> Err
> /*
> [08001] ERROR: could not connect to server "loopback"
> Detail: connection to server on socket "/tmp/.s.PGSQL.54321" failed:
> FATAL: sorry, too many clients already
> Where: remote SQL command: INSERT INTO public.test_self(id) VALUES ($1)
> remote SQL command: INSERT INTO public.test_self(id) VALUES ($1)
> remote SQL command: INSERT INTO public.test_self(id) VALUES ( ...
> */
> ```
> 
> The proposed patch fixes this error.
> 
> ```sql
> create foreign table test_self (id int)
> server loopback options (table_name 'test_self');
> --> Err
> /*
> [42P16] ERROR: foreign table "test_self" cannot reference itself
> Hint: Foreign table pointing to the same table on the same database
> creates circular reference
> */
> ```
> 

I don't think this is a bug. There's probably a million other ways to
cause similar connection loops - consider for example two servers with
foreign tables pointing at each other. That'll trigger exactly the same
issue like your example.

The patch has a variety of other issues :-(

- The options (table_name ...) are specific to the postgres_fdw
extension. The code in src/backend/commands/foreigncmds.c should not be
checking that. There could be an arbitrary other FDW, which happens to
have the same option, or whatever.

- It checks the dbname and table_name, but there's no guarantee it
points at the same machine/instance. So it actually prevents references
to the same (dbname,table_name) on any server. I doubt that makes sense.

- It can't actually check the server in a reliable way, because it could
go through an arbitrary IP for the machine, hostname, connection pool or
whatever other proxy.

- It handles CREATE FOREIGN TABLE, but there's all kinds of ways to
adjust options for an existing table, e.g.

  ALTER FOREIGN TABLE test_self OPTIONS (SET table_name '...')

The patch would have add protections in all those places.

There's probably more issues. I don't think this is feasible / worth it.
The connection failure seems like a reasonable end result.


regards

-- 
Tomas Vondra