Re: [HACKERS] statement_timeout is not working as expected withpostgres_fdw - Mailing list pgsql-hackers

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