Re: [HACKERS] Something broken around FDW connection close
On 31 March 2017 at 16:32, Etsuro Fujita 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,10) 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
Re: [HACKERS] Something broken around FDW connection close
At Fri, 31 Mar 2017 12:32:39 +0900, Etsuro Fujita wrote in > 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Something broken around FDW connection close
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,10) 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. Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Something broken around FDW connection close
create table t (a int, b int); insert into t1 select x/100,x/100 from generate_series(1,10) 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. Adding to open items... -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
