Hello,
We have statement_timeout, idle_in_transaction_timeout,
idle_session_timeout and many more! But we have no
transaction_timeout. I've skimmed thread [0,1] about existing timeouts
and found no contraindications to implement transaction_timeout.
Nikolay asked me if I can prototype the feature for testing by him,
and it seems straightforward. Please find attached. If it's not known
to be a bad idea - we'll work on it.
Thanks!! It was a super quick reaction to my proposal Honestly, I was thinking about it for several years, wondering why it's still not implemented.
The reasons to have it should be straightforward – here are a couple of them I can see.
First one. In the OLTP context, we usually have:
- a hard timeout set in application server
- a hard timeout set in HTTP server
- users not willing to wait more than several seconds – and almost never being ready to wait for more than 30-60s.
If Postgres allows longer transactions (it does since we cannot reliably limit their duration now, it's always virtually unlimited), it might be doing the work that nobody is waiting / is needing anymore, speeding resources, affecting health, etc.
Why we cannot limit transaction duration reliably? The existing timeouts (namely, statement_timeout + idle_session_timeout) don't protect from having transactions consisting of a series of small statements and short pauses between them. If such behavior happens (e.g., a long series of fast UPDATEs in a loop). It can be dangerous, affecting general DB health (bloat issues). This is reason number two – DBAs might want to decide to minimize the cases of long transactions, setting transaction limits globally (and allowing to set it locally for particular sessions or for some users in rare cases).
postgres=# set transaction_timeout to '2s';
SET
postgres=# select pg_sleep(3);
ERROR: canceling transaction due to transaction timeout
But it fails in the "worst" case I've described above – a series of small statements:
postgres=# set transaction_timeout to '2s';
SET
postgres=# begin; select pg_sleep(1); select pg_sleep(1); select pg_sleep(1); select pg_sleep(1); select pg_sleep(1); commit;
BEGIN
pg_sleep
----------
(1 row)
pg_sleep
----------
(1 row)
pg_sleep
----------
(1 row)
pg_sleep
----------
(1 row)
pg_sleep
----------
(1 row)
COMMIT
postgres=#
