On 05/04/2017 08:01 AM, Robert Haas wrote:
Patch attached.
I tried at my end after applying the patch against PG HEAD,
Case 1 - without setting statement_timeout i.e default
X machine -
create table test1(a int);
Y machine -
CREATE SERVER myserver_ppas FOREIGN DATA WRAPPER postgres_fdw OPTIONS
(host 'X', dbname 'postgres', port '5432', connect_timeout '3');
CREATE USER MAPPING FOR centos SERVER myserver_ppas OPTIONS (user
'centos', password 'adminedb');
create foreign table ft_test_ppas (a int ) server myserver_ppas options
(table_name 'test1');
statement_timeout =0;
\timing
insert into ft_test_ppas values (generate_series(1,10000000));
X machine-
disconnect network
Y machine -
postgres=# insert into ft_test_ppas values (generate_series(1,10000000));
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
WARNING: could not send cancel request: PQcancel() -- connect() failed:
Connection timed out
ERROR: canceling statement due to user request
Time: 81073.872 ms (01:21.074)
Case 2- when statement_timeout=6000
Y machine -
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'X', dbname 'postgres', port '5432',keepalives '1', keepalives_interval
'3',keepalives_idle '3', keepalives_count '1');
CREATE USER MAPPING FOR centos SERVER myserver OPTIONS (user 'centos',
password 'adminedb');
create foreign table ft_test_ppas1 (a int ) server myserver options
(table_name 'test1');
set statement_timeout=6000;
\timing
insert into ft_test_ppas1 values (generate_series(1,10000000));
X machine-
disconnect network
Y machine
postgres=# insert into ft_test_ppas1 values
(generate_series(1,10000000));
WARNING: could not send cancel request: PQcancel() -- connect() failed:
Connection timed out
ERROR: canceling statement due to statement timeout
Time: 69009.875 ms (01:09.010)
postgres=#
Case 3-when statement_timeout=20000
Y machine -
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'X', dbname 'postgres', port '5432',keepalives '1', keepalives_interval
'3',keepalives_idle '3', keepalives_count '1');
CREATE USER MAPPING FOR centos SERVER myserver OPTIONS (user 'centos',
password 'adminedb');
create foreign table ft_test_ppas1 (a int ) server myserver options
(table_name 'test1');
set statement_timeout=20000;
\timing
insert into ft_test_ppas1 values (generate_series(1,10000000));
X machine-
disconnect network
Y machine -
postgres=# insert into ft_test_ppas1 values
(generate_series(1,10000000));
WARNING: could not send cancel request: PQcancel() -- connect() failed:
Connection timed out
ERROR: canceling statement due to statement timeout
Time: 83014.503 ms (01:23.015)
We can see statement_timeout is working but it is taking some extra
time,not sure this is an expected behavior in above case or not.
--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
