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 🙂