Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

On Mon, Nov 2, 2015 at 8:42 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>>
>> It is 100% true. But the users can do strange things. If we solve idle
>> transactions and not idle session, then they are able to increase
>> max_connections to thousands with happy smile in face.
>>
>> I have not strong idea about how to solve it well - maybe introduce
>> transaction_idle_timeout and session_idle_timeout?
>>
>
> What exactly do we want to define session_idle_timeout? Some
> possibilities:
> a. Reset the session related variables like transaction, prepared
> statements, etc. and retain it for connection pool kind of stuff
> b. Exit from the session
>
> If we want something on lines of option (a), then I think it is better
> to have just a single time out (session_idle_timeout/idle_timeout)

I'm not thrilled about the prefix 'session_': most .conf variables
apply to the session (like statement_timeout) and we don't use the
session prefix for any of those.

"transaction_idle_timeout" is ok, if you want the timeout to apply as
an expiration for a transaction going idle.

"idle_timeout" doesn't make much sense to me. It's the responsibility
of the pooler to mange idle-but-not-in-transaction sessions and we
already have machinery to support that (DISCARD).

"transaction_timeout" is the best, and simplest, hypothetical setting
IMNSHO. It gives you a well defined upper bound guarantee of
transaction time regardless of application behavior, which neither
statement_timeout or transaction_idle_timeout give, even when used in
conjunction as I understand them. It would completely displace
statement_timeout in all servers I manage.

merlin