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

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

> On Wed, Nov 4, 2015 at 11:15 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >
> >
> > 2015-11-04 18:11 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >>
> >> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> >> >> 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.
> >>
> >> I'd vote for just transaction_timeout. The way our timeout manager
> >> logic works, that should be more efficient, as the timeout would only
> >> have to be established once at transaction start, not every time the
> >> main command loop iterates.
> >
> >
> > I cannot to say, so transaction_timeout is not useful, but it cannot be
> > effective solution for some mentioned issues. With larger data you
> cannot to
> > set transaction_timeout less than few hours.
>
> sure. note however any process can manually opt in to a longer timeout.
>

it doesn't help. How I can set transaction_timeout if I have series of slow
statements? In this case I cannot to set transaction_timeout before any
statement or after any success statement.

>
> merlin
>