Re: idle_in_transaction_timeout

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:

>>> Which makes me wonder whether we shouldn't default this to
>>> something non-zero -- even if it is 5 or 10 days.
>
>> I'd go for even shorter: 48 hours.  I'd suggest 24 hours, but that
>> would trip up some users who just need really long pg_dumps.
>
> FWIW, I do not think we should have a nonzero default for this.
> We could not safely set it to any value that would be small enough
> to be really useful in the field.

I have seen production environments where users asked for help when
performance had gradually degraded to a fraction of what it was,
due to a connection sitting "idle in transaction" for several
weeks.  Even a timeout of five or ten days would have saved a lot
of pain.  What concerns me on the other side is that I've been
known to start a long-running conversion or data fix on a Friday
and check the results on Monday before committing.  Something like
that might sit for a day or two with little or no concurrent
activity to cause a problem.  It would be a real forehead-slapper
to have forgotten to set a longer timeout before starting the run
on Friday.  A five day timeout seems likely to prevent extreme pain
in the former circumstances while not being likely to mess up ad
hoc bulk activity like the latter.

Of course, if I were managing a cluster and was knowingly and
consciously setting a value, it would probably be more like 5min.
If I have actually set such a policy I am much less likely to
forget it when it needs to be extended or disabled, and far less
likely to be mad at anyone else if it cancels my work.

> 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 thought about it enough to mention it briefly.  I haven't taken
it further than to note that it would be a great follow-up patch
once this is in.  I'm not sure that a few minutes would be
sufficient, though.  Theoretically, a crash of the transaction
manager, or one of the other data stores managed by it, or even a
WAN connection to one of the servers, should cause the transaction
manager to finish things up after recovery from the problem.  I
think that a default would need to allow sufficient time for that,
so we can have some confidence that the transaction manager has
actually lost track of it.  If I were configuring this for a real
production environment, I would be in mind of frequently having
seen WAN outages of several hours, and a few which lasted two or
three days.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company