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,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.

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

At Fri, 31 Mar 2017 12:32:39 +0900, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote in <f14ace82-d77e-f1fd-e313-f07df2c10a1b@lab.ntt.co.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 31 March 2017 at 16:32, Etsuro Fujita <fujita.etsuro@lab.ntt.co.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