Re: statement_timeout is not working as expected with postgres_fdw

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