Re: Idle In Transaction Session Timeout, revived

On 4 February 2016 at 09:04, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> > I have the same uneasy feeling about it as JD. However, you could
> > certainly argue that if the client application has lost its marbles
> > to the extent of allowing a transaction to time out, there's no good
> > reason to suppose that it will wake up any time soon, ...
>
> That's exactly what I think. If you imagine a user who starts a
> transaction and then leaves for lunch, aborting the transaction seems
> nicer than killing the connection. But what I think really happens is
> some badly-written Java application loses track of a connection
> someplace and just never finds it again. Again, I'm not averse to
> having both behavior someday, but my gut feeling is that killing the
> connection will be the more useful one.
>

Applications - and users - must be prepared for the fact that uncommitted
data and session state may be lost at any time. The fact that PostgreSQL
tries not to lose it is quite nice, but gives people a false sense of
security too. Someone trips over a cable, a carrier has a bit of a BGP
hiccup, a NAT conntrack timeout occurs, there's an ECC parity check error
causing a proc kill ... your state can go away.

If you really don't want your session terminated, don't set an idle in
transaction session idle timeout (or override it).

(In some ways I think we're too good at this; I really should write an
extension that randomly aborts some low percentage of xacts with fake
deadlocks or serialization failures and randomly kills occasional
connections so that apps actually use their retry paths...)

Sure, it'd be *nice* to just terminate the xact and have a separate param
for timing out idle sessions whether or not they're in an xact. Cleaner -
terminate the xact if there's an xact-related timeout, terminate the
session if there's a session-related timeout. But nobody's written that
patch and this proposal solves a real world problem well enough.
Terminating the xact without terminating the session is a little tricky as
noted earlier so it's not a simple change to switch to that.

I'd be happy to have this. I won't mind having it if we eventually add an
idle_xact_timeout and idle_session_timeout in 9.something too.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services