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 🙂

  1. 6 comments

  2. # Vik Fearing
    Mar 22, 2016

    You’re welcome 🙂

    (big thanks to Andres Freund for this, too)

  3. Mar 23, 2016

    thanks to all how helped get this is. This is a very nice feature.

  4. # therealgaxbo
    Mar 24, 2016

    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!

  5. # sivananda
    Apr 7, 2016

    Really this is a great future for PostgreSQL DBA’s

  6. # jeffe
    Sep 20, 2016

    Forgive my ignorance, is this something that can be backported/patched into older versions, say 9.2 🙂 ?

  7. Sep 20, 2016

    @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.

Leave a comment