statement_timeout vs DECLARE CURSOR

From: Christophe Pettus <xof(at)thebuild(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: statement_timeout vs DECLARE CURSOR
Date: 2021-09-27 17:42:09
Message-ID: A226617F-F248-4F92-BF62-5CF89EAAA148@thebuild.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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=#

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2021-09-27 18:10:19 Re: statement_timeout vs DECLARE CURSOR
Previous Message Zwettler Markus (OIZ) 2021-09-27 13:01:21 change ownership of schema public?

Browse pgsql-hackers by date

  From Date Subject
Next Message Bossart, Nathan 2021-09-27 18:03:33 Re: .ready and .done files considered harmful
Previous Message Robert Haas 2021-09-27 17:22:31 Re: Gather performance analysis