On 16th of March, Robert Haas committed patch:
Add idle_in_transaction_session_timeout. Vik Fearing, reviewed by Stéphane Schildknecht and me, and revised slightly by me.
This is something amazing.
I hate “idle in transaction" connections. These cause lots of problems, and while are generally easy to solve, in times where we have orms, web frameworks, various “work flows" in companies, and lots of developers – in many cases noone really seems to care about these (aside from dbas), and they don't get fixed.
Now, with this new patch, the problem is gone.
By default idle_in_transaction_session_timeout is set to 0, which means no timeout – transaction started will just continue forever.
But if I'll change it to something else, like 15 seconds:
$ SET idle_in_transaction_session_timeout = '15s';
Then start transaction, wait for 15 seconds (it's 15 seconds of idle in transaction, not “process some queries" in transaction), and then will try to run a query:
(depesz@[LOCAL]:5960) 20:15:16 [depesz] $ BEGIN; BEGIN (depesz@[LOCAL]:5960) 20:15:18 [depesz] *$ (depesz@[LOCAL]:5960) 20:15:43 [depesz] *$ SELECT 1; FATAL: terminating connection due TO idle-in-TRANSACTION timeout server closed the connection unexpectedly This probably means the server TERMINATED abnormally BEFORE OR while processing the request. The connection TO the server was lost. Attempting reset: Succeeded.
In logs, I can see:
1264 2016-03-21 20:15:33.537 CET depesz@depesz 7649 [LOCAL] FATAL: terminating connection due TO idle-in-TRANSACTION timeout 1265 2016-03-21 20:15:33.537 CET depesz@depesz 7649 [LOCAL] LOG: disconnection: SESSION TIME: 0:00:20.308 USER=depesz DATABASE=depesz host=[LOCAL]
Nice. Something I was waiting for for a very long time. Thanks a lot 🙂
You’re welcome 🙂
(big thanks to Andres Freund for this, too)
thanks to all how helped get this is. This is a very nice feature.
This may not sound that exciting amongst all the parallel query patches, but having seen the problems caused by idle transactions first hand I know what a big deal this really is.
Thanks to all involved!
Really this is a great future for PostgreSQL DBA’s
Forgive my ignorance, is this something that can be backported/patched into older versions, say 9.2 🙂 ?
@Jeffe:
you can try. Extract this patch, and try to appl it to 9.2 sources. Whether it will work from start, or no, or maybe it will take some time to get it to work – no idea.