Postgres Pro
Downloads
Home   >   mailing lists

Thread: Foreign table feedback

Foreign table feedback

From
Thom Brown
Date:
Hi all,

Is there a way to ensure we don't end up with a foreign table feedback loop?

For example:

CREATE SERVER pgserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres');

-- note how I've forgotten to specify host or port

CREATE USER MAPPING FOR PUBLIC SERVER pgserver;

CREATE FOREIGN TABLE test (id int) SERVER pgserver;

postgres=# SELECT * FROM test;
FATAL:  sorry, too many clients already
ERROR:  could not connect to server "pgserver"
DETAIL:  FATAL:  sorry, too many clients already
STATEMENT:  DECLARE c1 CURSOR FOR
SELECT id FROM public.test
ERROR:  could not connect to server "pgserver"
DETAIL:  FATAL:  sorry, too many clients already
CONTEXT:  Remote SQL command: SELECT id FROM public.test
STATEMENT:  FETCH 100 FROM c1
ERROR:  could not connect to server "pgserver"
DETAIL:  FATAL:  sorry, too many clients already
...

This of course carries on until there's 100 lines in the CONTEXT
message, and all connections, which remain in use after the error.

Of course the same issue happens if you try to create a foreign table
to another foreign table on another server, which points back to the
one you're creating.
--
Thom

Re: Foreign table feedback

From
Tom Lane
Date:
Thom Brown <thom@linux.com> writes:
> Is there a way to ensure we don't end up with a foreign table feedback loop?

Yeah, I've run into that too :-(.  I'm not sure how big a deal it is for
real-world use, but certainly the "loopback" server the postgres_fdw
regression test sets up is rather risky.

But I don't really want to disallow chains of foreign tables, so it's
hard to see how to prevent it without breaking possibly-useful cases.
        regards, tom lane

Re: Foreign table feedback

From
Josh Berkus
Date:
> But I don't really want to disallow chains of foreign tables, so it's
> hard to see how to prevent it without breaking possibly-useful cases.

As long as we can prevent such a loop from crashing Postgres.
-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: Foreign table feedback

From
Thom Brown
Date:
On 14 March 2013 20:32, Josh Berkus <josh@agliodbs.com> wrote:
>
>> But I don't really want to disallow chains of foreign tables, so it's
>> hard to see how to prevent it without breaking possibly-useful cases.
>
> As long as we can prevent such a loop from crashing Postgres.

It won't, or at least it really shouldn't.  It'll just take up all
connections until that session ends.

-- 
Thom