Hi,
We've encountered some unexpected behavior with statement_timeout not
cancelling a query in DECLARE CURSOR, but only if the DECLARE CURSOR is outside
of a transaction:
xof=# select version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 on x86_64-apple-darwin19.6.0, compiled by Apple clang version
12.0.0 (clang-1200.0.32.29), 64-bit
(1 row)
xof=# set statement_timeout = '1s';
SET
xof=# \timing
Timing is on.
xof=# select * from (with test as (select pg_sleep(10), current_timestamp as
cur_time) select 1 from test ) as slp;
ERROR: canceling statement due to statement timeout
Time: 1000.506 ms (00:01.001)
xof=# declare x no scroll cursor with hold for select * from (with test as
(select pg_sleep(10), current_timestamp as cur_time) select 1 from test ) as
slp;
DECLARE CURSOR
Time: 10001.929 ms (00:10.002)
xof=#
but:
xof=# set statement_timeout = '1s';
SET
xof=# \timing
Timing is on.
xof=# begin;
BEGIN
Time: 0.161 ms
xof=*# declare x no scroll cursor with hold for select * from (with test as
(select pg_sleep(10), current_timestamp as cur_time) select 1 from test ) as
slp;
DECLARE CURSOR
Time: 0.949 ms
xof=*# fetch all from x;
ERROR: canceling statement due to statement timeout
Time: 1000.520 ms (00:01.001)
xof=!# abort;
ROLLBACK
Time: 0.205 ms
xof=#
