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

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.

merlin