BUG #19896: transaction_timeout misbehaves after ROLLBACK TO SAVEPOINT and after errors

Поиск
Список
Период
Сортировка
Искать

BUG #19896: transaction_timeout misbehaves after ROLLBACK TO SAVEPOINT and after errors

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:

Bug reference:      19896
Logged by:          Nicke van Oorschot
Email address:      nickevanoorschot@gmail.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 22.04.5 LTS
Description:        

PostgreSQL versions: 18.6; 20devel (development snapshot
postgresql-snapshot.tar.bz2 of
2026-11-12 04:22 UTC, master at debfb3c8a8c93e7bcc3638523b7ffc0846cc2db8).
First observed
on 18.1 ("PostgreSQL 18.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine
15.2.0) 15.2.0, 64-bit").

Documentation of transaction_timeout: "Terminate any session that spans
longer than the
specified amount of time in a transaction." The cases below differ from
that. Each script was
run with "psql -X -f " against a freshly initialised cluster with
default settings.
Output below is from 18.6; 20devel gives the same output apart from the
LOCATION line numbers.


Case 1: a timeout set inside a rolled-back savepoint terminates a later
transaction

--- case1.sql
\set VERBOSITY verbose
-- Transaction 1 sets transaction_timeout inside a savepoint and rolls the
savepoint back.
BEGIN;
SELECT now() AS transaction_1_start;
SAVEPOINT s;
SET LOCAL transaction_timeout = '2s';
ROLLBACK TO SAVEPOINT s;
SHOW transaction_timeout;
COMMIT;
-- Transaction 2 sets a 10 s transaction_timeout and runs for 3 s.
BEGIN;
SELECT now() AS transaction_2_start;
SET LOCAL transaction_timeout = '10s';
SELECT pg_sleep(3);
COMMIT;
SELECT 'transaction 2 committed' AS result;
---

Output:
BEGIN
      transaction_1_start
-------------------------------
 2026-11-12 11:33:03.520214+00
(1 row)

SAVEPOINT
SET
ROLLBACK
 transaction_timeout
---------------------
 0
(1 row)

COMMIT
BEGIN
      transaction_2_start
-------------------------------
 2026-11-12 11:33:03.520757+00
(1 row)

SET
psql:case1.sql:14: FATAL:  25P04: terminating connection due to transaction
timeout
LOCATION:  ProcessInterrupts, postgres.c:3498
psql:case1.sql:14: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:case1.sql:14: error: connection to server was lost

Server log:
2026-11-12 11:33:05.520 UTC [297561] FATAL:  terminating connection due to
transaction timeout
2026-11-12 11:33:05.520 UTC [297561] STATEMENT:  SELECT pg_sleep(3);

The connection is terminated 2 s after transaction 1 started, while
transaction 2 has run for
less than 2 s with a 10 s transaction_timeout.

Expected: transaction 2 completes and "transaction 2 committed" is returned.
Transaction 1
ended with transaction_timeout = 0, and transaction 2 stays within its 10 s.


Case 2: after an error and ROLLBACK TO SAVEPOINT, the transaction is not
terminated

--- case2.sql
\set VERBOSITY verbose
-- The transaction sets a 2 s transaction_timeout, recovers from an error
with ROLLBACK TO SAVEPOINT, and runs for 3 s.
BEGIN;
SET LOCAL transaction_timeout = '2s';
SAVEPOINT s;
SELECT 1/0;
ROLLBACK TO SAVEPOINT s;
SHOW transaction_timeout;
SELECT pg_sleep(3);
COMMIT;
SELECT 'transaction committed after 3 s' AS result;
---

Output:
BEGIN
SET
SAVEPOINT
psql:case2.sql:6: ERROR:  22012: division by zero
LOCATION:  int4div, int.c:870
ROLLBACK
 transaction_timeout
---------------------
 2s
(1 row)

 pg_sleep
----------

(1 row)

COMMIT
             result
---------------------------------
 transaction committed after 3 s
(1 row)

Expected: the connection is terminated 2 s into the transaction, during
pg_sleep(3), and the
transaction does not commit.

The same script with "SELECT 1;" instead of "SELECT 1/0;" is terminated as
expected:
psql:case2_control.sql:8: FATAL:  25P04: terminating connection due to
transaction timeout

Setting the timeout for the session ("SET transaction_timeout = '2s';"
before BEGIN, no SET
LOCAL) gives the same result as case 2: the transaction commits after 3 s.


Case 3: an aborted transaction is not terminated

--- case3.sql
\set VERBOSITY verbose
-- The transaction sets a 2 s transaction_timeout, fails, and stays open
(aborted) for 3 s.
BEGIN;
SET LOCAL transaction_timeout = '2s';
SELECT 1/0;
\! sleep 3
ROLLBACK;
SELECT 'connection still open after 3 s' AS result;
---

Output:
BEGIN
SET
psql:case3.sql:5: ERROR:  22012: division by zero
LOCATION:  int4div, int.c:870
ROLLBACK
             result
---------------------------------
 connection still open after 3 s
(1 row)

Expected: the connection is terminated 2 s into the transaction, while it is
open in the
aborted state.


Environment of the 18.6 and 20devel runs:
- Built from the source tarballs with
  ./configure --without-readline --without-zlib --without-icu
--without-openssl; make; make install
  (no other deviation from the installation instructions)
- Cluster: initdb -A trust -U postgres, no changes to postgresql.conf
- Server started with: pg_ctl -o "-p  -k  -c
listen_addresses=''" start
- psql from the same build, run with -X (no .psqlrc)
- OS: Ubuntu 22.04.5 LTS, Linux 7.0.0-43-generic x86_64, glibc 2.35
- Compiler: gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0
- CPU: AMD Ryzen 9 7940HS (16 threads), 53 GB RAM




Re: BUG #19896: transaction_timeout misbehaves after ROLLBACK TO SAVEPOINT and after errors

От:
Andrey Borodin <x4mmm@yandex-team.ru>
Дата:
Вложения:
Hi Nicke,

Thank you for the report. Yes, this looks like a bug.

I'm attaching draft fixes for cases 1 and 2. GUC restoration runs after
the subtransaction enters TRANS_ABORT, so IsTransactionState() prevented
the assign hook from updating the timer. Separately, PostgresMain
cancelled the surviving transaction's timer on ERROR. The draft
preserves its original deadline rather than restarting the timeout.

For case 3, I had already noted missing coverage for terminating a
session in "idle in transaction (aborted)" [0]. AbortTransaction()
currently stops the timer and rolls back SET LOCAL. These drafts still
leave that lifetime issue unresolved.

The last patch is particularly tentative. With transaction_timeout = 2s
and idle_in_transaction_session_timeout = 3s, both set at session level,
a full abort leaves neither timer running. Here time is measured from
BEGIN, with an error at t=1s and no further commands:

  t=0s        t=1s        t=2s                    t=4s
  |-----------|-----------|-----------------------|
  BEGIN       ERROR       transaction deadline    idle deadline
              full abort  FATAL if kept active    FATAL with 0004

The patch would terminate the session at t=4s: the 3s idle wait starts
after the error at t=1s. Keeping the transaction timer effective would
terminate it at the original t=2s deadline.
We had deliberately suppressed longer or equal timeouts [1]. Should a
fully aborted transaction block be covered by transaction_timeout,
idle_in_transaction_session_timeout, or neither? I lean slightly
towards keeping transaction_timeout effective until the transaction
block ends, including while it is aborted.

First patch with test is for an exploration, maybe some of it will be
useful in future.

Feedback is welcome. I'll keep thinking about this.

Thank you!

Best regards, Andrey Borodin.

[0] https://postgr.es/m/4C30F833-C0F2-4664-B0FF-192FD8AB2E2E%40yandex-team.ru
[1] https://postgr.es/m/A8009945-7FB7-4E75-9239-CCC351F8050C%40yandex-team.ru



FAQ