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

2015-11-04 17:02 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> 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.
>

I agree so transaction_timeout is more general. But I have same problem
@A. How it set properly. In our production max transaction can 30hours -
(VACUUM) or 5hours (ETL). But transaction_idle_timeout can be 5minutes, I
know so 5 minutes in "idle in transaction" state signalizes some issue.

It looks very similar to relation between statement_timeout and
lock_timeout I am think.

Regards

Pavel

> merlin
>