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

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

> On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > 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.
>
> Not quite following you. The client has to go:
> BEGIN;
> SET transaction_timeout = x;
> ....
>

where is the point when transaction_timeout start? In BEGIN or in SET
transaction_timeout ?

How I can emulate transaction_idle_timeout? Can I refresh
transaction_timeout?

My issue isn't long statements, but broken client, that is broken in wrong
state - connect is still active, but no any statement will coming.

Regards

Pavel

> or the client can do that on session start up. There are two problem
> cases I can think of:
> 1) connection pooler (pgbouncer): This can work, but you have to be
> very careful. Maybe DISCARD needs to be able to undo adjusted
> session settings if it doesn't already.
>
> 2) procedure emulating functions: It's a major pain that you can't
> manage timeout inside a function itself. You also can't manage
> transaction state or isolation level. The real solution here is to
> implement stored procedures though.
>
> merlin
>