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
