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

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

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