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

On Wed, Nov 4, 2015 at 10:02 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Nov 4, 2015 at 8:54 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2015-11-04 15:50 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>>>
>>> On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>> wrote:
>>> >> > Okay, I think one more point to consider is that it would be
>>> >> > preferable
>>> >> > to
>>> >> > have such an option for backend sessions and not for other processes
>>> >> > like WalSender.
>>> >>
>>> >> All right...I see the usage.. I withdraw my objection to 'session'
>>> >> prefix then now that I understand the case. So, do you agree that:
>>> >>
>>> >> *) session_idle_timeout: dumps the backend after X time in 'idle' state
>>> >> and
>>> >> *) transaction_timeout: cancels transaction after X time, regardless
>>> >> of
>>> >> state
>>> >>
>>> >> sounds good?
>>> >
>>> >
>>> > Not too much
>>> >
>>> > *) transaction_timeout: cancels transaction after X time, regardless of
>>> > state
>>> >
>>> > This is next level of statement_timeout. I can't to image sense. What is
>>> > a
>>> > issue solved by this property?
>>>
>>> That's the entire point of the thread (or so I thought): cancel
>>> transactions 'idle in transaction'. This is entirely different than
>>> killing idle sessions. BTW, I would never configure
>>> session_idle_timeout, because I have no idea what that would do to
>>> benign cases where connection poolers have grabbed a few extra
>>> connections during a load spike. It's pretty common not to have
>>> those applications have coded connection retry properly and it would
>>> cause issues.
>>
>> you wrote "transaction_timeout: cancels transaction after X time, regardless
>
> Yes, and that is what I meant. I have two problems with
> transaction_idle_timeout (as opposed to transaction_timeout):
>
> A) It's more complex. Unsophisticated administrators may not
> understand or set it properly
>
> B) There is no way to enforce an upper bound on transaction time with
> that setting. A pathological application could keep a transaction
> open forever without running into any timeouts -- that's a dealbreaker
> for me.
>
> From my point of view the purpose of the setting should be to protect
> you from any single actor from doing things that damage the database.
> 'idle in transaction' happens to be one obvious way, but upper bound
> on transaction time protects you in general way.

Note, having both settings would work too.

merlin