Re: idle_in_transaction_timeout

Hello

pgbouncer has idle_transaction_timeout defined some years without any
problems, so we can take this design

idle_transaction_timeout

If client has been in "idle in transaction" state longer, it will be
disconnected. [seconds]

Default: 0.0 (disabled)

This feature can be very important, and I seen a few databases thas was
unavailable due leaked transaction.

Regards

Pavel

2014-06-19 1:46 GMT+02:00 Josh Berkus <josh(at)agliodbs(dot)com>:

> On 06/18/2014 02:52 PM, Bruce Momjian wrote:
> > On Wed, Jun 18, 2014 at 04:41:30PM -0400, Robert Haas wrote:
> >> The only problem I see is that it makes the semantics kind of weird
> >> and confusing. "Kill connections that are idle in transaction for too
> >> long" is a pretty clear spec; "kill connections that are idle in
> >> transaction except if they haven't executed any commands yet because
> >> we think you don't care about that case" is not quite as clear, and
> >> not really what the GUC name says, and maybe not what everybody wants,
> >> and maybe masterminding.
> >
> > "Kill connections that are idle in non-empty transaction block for too
> > long"
>
> Here's the POLS violation in this:
>
> "I have idle_in_transaction_timeout set to 10min, but according to
> pg_stat_activity I have six connections which are IIT for over an hour.
> What gives?"
>
> Robert's right, not killing the "BEGIN;" only transactions is liable to
> result in user confusion unless we label those sessions differently in
> pg_stat_activity. Tom is right in that killing them will cause some
> users to not use IIT_timeout when they should, or will set the timeout
> too high to be useful.
>
> So it seems like what we should do is NOT call sessions IIT if they've
> merely executed a BEGIN; and not done anything else. Then the timeout
> and pg_stat_activity would be consistent.
>
> Counter-argument: most app frameworks which do an automatic BEGIN; also
> do other stuff like SET TIMEZONE each time as well. Is this really a
> case worth worrying about?
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>