Re: Something broken around FDW connection close

At Fri, 31 Mar 2017 12:32:39 +0900, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <f14ace82-d77e-f1fd-e313-f07df2c10a1b(at)lab(dot)ntt(dot)co(dot)jp>
> On 2017/03/31 8:28, David Rowley wrote:
..
> > create server test_server foreign data wrapper postgres_fdw options
> > (host 'localhost', port '5432', dbname 'postgres');
> > create foreign table ft_t (a int,b int) server test_server;
...
> > select count(*) from pg_stat_Activity; -- > 6
> > analyze ft_t;
> > ERROR: could not connect to server "test_server"
> > DETAIL: FATAL: sorry, too many clients already
> > CONTEXT: Remote SQL command: DECLARE c1 CURSOR FOR SELECT a, b FROM
> > public.ft_t
> > Remote SQL command: SELECT a, b FROM public.ft_t
> > Remote SQL command: SELECT a, b FROM public.ft_t
> > Remote SQL command: SELECT a, b FROM public.ft_t
> > (lots of these)
...
> IIUC, I think the cause would be that since the foreign table ft_t is
> considered to be still foreign on the foreign server, which is
> actually the same server, postgres_fdw recursively repeats the
> loopback access to ft_t. (So, the same thing would happen for
> something like: select * from ft_t.) If the analysis is right, ISTM
> that it's the user's fault.

Agreed, this behavior is mentioned here.

https://www.postgresql.org/docs/9.6/static/postgres-fdw.html#AEN182920

| table_name
|
| This option, which can be specified for a foreign table, gives
| the table name to use for the foreign table on the remote
| server. If this option is omitted, the foreign table's name is
| used.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center