Re: Something broken around FDW connection close

On 31 March 2017 at 16:32, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> On 2017/03/31 8:28, David Rowley wrote:
>
>> create table t (a int, b int);
>> insert into t1 select x/100,x/100 from generate_series(1,100000) x;
>> create extension if not exists postgres_fdw;
>> 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 'create user mapping for current_user server test_server
>> options(user ''' || current_user || ''');';
>> \gexec
>> 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)
>>
>> select count(*) from pg_stat_Activity; --> 105
>>
>> I've not had a moment to check into what's going on.
>>
>
> 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.
>

Oh of course... I see exactly what I did wrong :-( sorry for the noise.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services