Re: idle_in_transaction_timeout

Andres Freund <andres@2ndquadrant.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.
        regards, tom lane