statement_timeout vs DECLARE CURSOR
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t44865psql -h localhost -U postgresBuilt from patchset v5 (message #5), September 20, 2026 at 11:41 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t44865_5 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t44865_5 && git checkout t44865_5Patchset v5 (message #5) is on t44865_5
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=#
On Sep 27, 2021, at 10:42, Christophe Pettus <xof@thebuild.com> wrote:
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:
A bit more poking revealed the reason: The ON HOLD cursor's query is executed at commit time (which is, logically, not interruptible), but that's all wrapped in the single statement outside of a transaction.
Christophe Pettus <xof@thebuild.com> writes:
On Sep 27, 2021, at 10:42, Christophe Pettus <xof@thebuild.com> wrote:
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:
A bit more poking revealed the reason: The ON HOLD cursor's query is executed at commit time (which is, logically, not interruptible), but that's all wrapped in the single statement outside of a transaction.
Hmm ... seems like a bit of a UX failure. I wonder why we don't persist
such cursors before we get into the uninterruptible part of COMMIT.
regards, tom lane
I wrote:
Christophe Pettus <xof@thebuild.com> writes:
A bit more poking revealed the reason: The ON HOLD cursor's query is executed at commit time (which is, logically, not interruptible), but that's all wrapped in the single statement outside of a transaction.
Hmm ... seems like a bit of a UX failure. I wonder why we don't persist
such cursors before we get into the uninterruptible part of COMMIT.
Oh, I see the issue. It's not that that part of COMMIT isn't
interruptible; you can control-C out of it just fine. The problem
is that finish_xact_command() disarms the statement timeout before
starting CommitTransactionCommand at all.
We could imagine pushing the responsibility for that down into
xact.c, allowing it to happen after CommitTransaction has finished
running user-defined code. But it seems like a bit of a mess
because there are so many other code paths there. Not sure how
to avoid future bugs-of-omission.
regards, tom lane
[ redirect to -hackers ]
I wrote:
Christophe Pettus <xof@thebuild.com> writes:
A bit more poking revealed the reason: The ON HOLD cursor's query is executed at commit time (which is, logically, not interruptible), but that's all wrapped in the single statement outside of a transaction.
Hmm ... seems like a bit of a UX failure. I wonder why we don't persist
such cursors before we get into the uninterruptible part of COMMIT.
Oh, I see the issue. It's not that that part of COMMIT isn't
interruptible; you can control-C out of it just fine. The problem
is that finish_xact_command() disarms the statement timeout before
starting CommitTransactionCommand at all.
We could imagine pushing the responsibility for that down into
xact.c, allowing it to happen after CommitTransaction has finished
running user-defined code. But it seems like a bit of a mess
because there are so many other code paths there. Not sure how
to avoid future bugs-of-omission.
Actually ... maybe it needn't be any harder than the attached?
This makes it possible for a statement timeout interrupt to occur
anytime during CommitTransactionCommand, but I think
CommitTransactionCommand had better be able to protect itself
against that anyway, for a couple of reasons:
1. It's not significantly different from a query-cancel interrupt,
which surely could arrive during that window.
2. COMMIT-within-procedures already exposes us to statement timeout
during COMMIT.
regards, tom lane