Re: idle_in_transaction_timeout

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2014-06-24 10:17:49 -0700, Tom Lane wrote:
> >> BTW, has anyone thought about the interaction of this feature with
> >> prepared transactions? I wonder whether there shouldn't be a similar but
> >> separately-settable maximum time for a transaction to stay in the prepared
> >> state. If we could set a nonzero default on that, perhaps on the order of
> >> a few minutes, we could solve the ancient bugaboo that "prepared
> >> transactions are too dangerous to enable by default".
>
> > I'd very much like that feature, but I'm not sure how to implement
> > it. Which process would do that check? We currently only allow rollbacks
> > from the corresponding database...
> > The best idea I have is to do it via autovacuum.
>
> I did not actually have any plan in mind when I wrote that, but your
> mention of autovacuum suggests an idea for it: consider the code that
> kicks autovacuum off a table when somebody wants exclusive lock.
> In the same way, we could teach processes that want a lock that conflicts
> with a prepared xact that they can kill the prepared xact if it's more
> than X seconds old.
>
> The other way in which old prepared xacts are dangerous is in blocking
> cleanup of dead tuples, and I agree with your thought that maybe
> autovacuum is the place to deal with that. I don't know whether we'd
> really need both mechanisms, or if just one would be enough.
>
> In either case, this wouldn't directly be a timeout but rather a "license
> to kill" once a prepared xact exceeds the threshold and is getting in
> somebody's way.

Why isn't this what we want for idle-in-transaction sessions..?

Sounds like exactly what I'd want, at least. Don't kill it off unless
it's blocking something or preventing xmin progression...

Indeed, we have specifically implemented a Nagios check which does
exactly this- looks to see if any idle-in-transaction process is
blocking something else and if it's been idle for too long it gets
killed. We don't have prepared transactions enabled, so we havn't had
to address that. We do have a check which alerts (but doesn't kill,
yet) idle-in-transaction processes which have been idle for a long time.

Thanks,

Stephen