In PostgreSQL, how do statement_timeout, idle_in_transaction_session_timeout, transaction_timeout and idle_session_timeout differ in what they measure and what they end?
answer
- four different clocks
- cancel versus terminate
- ERROR 57014 versus FATAL
- idle inside versus outside a transaction
- transaction_timeout arrived in 17
basics
~10 sstatement_timeout cancels one statement with ERROR 57014 and the session survives. idle_in_transaction_session_timeout, transaction_timeout (17+) and idle_session_timeout end the whole session with FATAL. All four are off by default and take milliseconds.
solid answer
~40 s`statement_timeout` limits one statement from arrival to completion and cancels it with `ERROR: canceling statement due to statement timeout` (SQLSTATE `57014`); the connection survives, though an open transaction is now aborted. The other three end the session with FATAL: `idle_in_transaction_session_timeout` (`25P03`) when a session sits idle inside an open transaction, `transaction_timeout` (`25P04`, new in PostgreSQL 17) when a transaction, explicit or single-statement, runs too long in total, and `idle_session_timeout` (`57P05`) when a session idles outside any transaction. All default to 0 and read a unitless value as milliseconds. The manual advises against setting `statement_timeout` or `transaction_timeout` in `postgresql.conf`, since that affects every session; set them per role, database or transaction instead.
code
sql · 10 linesALTER ROLE dispatch_api SET statement_timeout = '3s';
ALTER ROLE dispatch_api SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE dispatch_api SET transaction_timeout = '60s';
BEGIN;
SET LOCAL statement_timeout = '500ms';
UPDATE courier_assignments
SET status = 'picked_up'
WHERE order_id = 918274;
COMMIT;go deeper
Know that statement_timeout stops a single slow query and that a session left idle in an open transaction can be ended by its own timeout.
Explain all four clocks, ERROR versus FATAL, the SQLSTATEs, millisecond units, and why statement_timeout belongs on a role rather than in postgresql.conf.
Choose per-role values for a real fleet, predict how transaction_timeout interacts with the others, and make sure pools handle a server-closed connection.
Treat timeouts as a contract between services and the database, and weigh killing sessions against bloat, lock queues and retry storms.
## Four clocks, two outcomes PostgreSQL has four session-level timeouts, and they differ on two axes: **what is being timed** and **what happens when time runs out**. All four have the `user` context, so any session may set them; all four default to **0**, meaning disabled; and a value given without a unit is taken as **milliseconds**. | Setting | The clock runs while… | On expiry | SQLSTATE | Message | |---|---|---|---|---| | `statement_timeout` | one statement executes | ERROR, statement cancelled | `57014` | `canceling statement due to statement timeout` | | `idle_in_transaction_session_timeout` | the session waits for the client inside an open transaction | FATAL, session ends | `25P03` | `terminating connection due to idle-in-transaction timeout` | | `transaction_timeout` | a transaction is open, explicit or implicit | FATAL, session ends | `25P04` | `terminating connection due to transaction timeout` | | `idle_session_timeout` | the session waits for the client outside any transaction | FATAL, session ends | `57P05` | `terminating connection due to idle-session timeout` | The split matters to the application: after a `statement_timeout` the same connection can roll back and carry on, while the other three leave the client holding a closed connection that its pool must discard and replace. ## statement_timeout in detail - The timer starts when the command **arrives** at the server and stops when the server completes it, so time spent waiting for locks counts too. - Several statements sent in one simple-query message are timed **separately**. In the extended query protocol the timer starts on the first Parse, Bind, Execute or Describe message and stops at Execute completion or Sync. - The cancellation is an ordinary **ERROR**: inside a `BEGIN` block the transaction becomes aborted and must be rolled back, but the session stays. - With `log_min_error_statement` at its default of `ERROR`, the statement that timed out is written to the server log. - The same SQLSTATE `57014` is used for a manual cancel, which logs `canceling statement due to user request`, so the message text tells the two apart. ## The three session-ending timeouts - **`idle_in_transaction_session_timeout`** targets the classic leak: an application that ran `BEGIN` and a write, then went off to call another service or forgot to commit. Such a session holds its locks, and even without significant locks its open transaction stops vacuum from removing dead rows that only it might still see, which feeds table bloat. The timer is armed in the aborted state too. - **`transaction_timeout`** (added in **PostgreSQL 17**) bounds the total lifetime of a transaction, whether started with `BEGIN` or implicitly by a single statement in autocommit mode. Prepared transactions are not subject to it. - **`idle_session_timeout`** ends sessions idle outside a transaction. Such sessions are comparatively cheap, so the manual suggests it mainly for interactive users, and warns that connection pools and middleware may not react well to connections closed under them. ## How they interact 1. If `transaction_timeout` is **shorter than or equal to** `statement_timeout` or `idle_in_transaction_session_timeout`, the longer one is ignored: the transaction clock will always fire first. 2. So with `transaction_timeout = 5s`, an autocommit statement that runs 8 seconds ends the **session** with FATAL `25P04`, not a mere statement cancel. 3. Waiting for a lock is bounded separately by `lock_timeout`, covered with schema changes; `deadlock_timeout` belongs to the locking topic. ## Where to set them The manual explicitly does not recommend putting `statement_timeout` or `transaction_timeout` in `postgresql.conf`, because they would then hit every session, including a long `CREATE INDEX`, a manual `VACUUM` or a nightly payout batch. (`pg_dump` protects itself by setting these timeouts to 0 in its own session, but most tools do not.) The usual pattern is per role or per database (the `ALTER ROLE … SET` mechanics belong to the parameter-files topic), with `SET LOCAL` inside a transaction for one exceptional statement: - the delivery fleet's `dispatch_api` role: a few seconds of `statement_timeout`, tens of seconds of `idle_in_transaction_session_timeout`; - the batch role that settles courier payouts: generous or no statement limit, but still an idle-in-transaction limit; - interactive users: `idle_session_timeout`, so forgotten terminal sessions give their slots back. On PostgreSQL 16 and earlier, `transaction_timeout` does not exist; the other three cover the same ground except a cap on total transaction length.
- With transaction_timeout = 5s and statement_timeout = 30s for a role, what does the client see when an autocommit UPDATE runs 8 seconds?The session is terminated with `FATAL: terminating connection due to transaction timeout`, SQLSTATE `25P04`. A single autocommit statement is an implicit transaction, so `transaction_timeout` covers it, and because 5s is shorter than the 30s `statement_timeout`, the longer timeout is ignored. The client's pool sees a closed connection, not a recoverable statement error.
- Why set idle_in_transaction_session_timeout even when the idle session holds no locks that block anyone?Because an open transaction still holds back cleanup: vacuum cannot remove dead row versions that the idle transaction might still need to see, so busy tables bloat for as long as it stays open. The timeout ends such sessions with FATAL `25P03`, releasing that hold along with any locks. How vacuum's cleanup horizon works is covered under autovacuum.
- How do you tell from a PostgreSQL log whether a cancelled query hit statement_timeout or was cancelled by an operator?Both carry SQLSTATE `57014`, so read the message: `canceling statement due to statement timeout` versus `canceling statement due to user request`. With `log_min_error_statement` at its default `ERROR`, the log also shows the statement that was cancelled.
- statement_timeout closes the client connection when it fires.
- idle_in_transaction_session_timeout rolls back the transaction but keeps the session open.
- transaction_timeout only applies to explicit BEGIN ... COMMIT blocks.
- Setting statement_timeout in postgresql.conf is the recommended way to protect the server.
- A bare statement_timeout = 30 means thirty seconds.
- idle_session_timeout is what stops idle-in-transaction sessions holding back vacuum.
Assumes PostgreSQL 18; transaction_timeout exists from 17 on, so a 16 server offers only the other three timeouts.