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

2015-11-04 23:53 GMT+01:00 Stephen Frost <sfrost(at)snowman(dot)net>:

> JD,
>
> On Wednesday, November 4, 2015, Joshua D. Drake <jd(at)commandprompt(dot)com>
> wrote:
>
>> On 11/04/2015 02:15 PM, Stephen Frost wrote:
>>
>> Yeah but anything holding a lock that long can be terminated via
>>>> statement_timeout can it not?
>>>>
>>>
>>> Well, no? statement_timeout is per-statement, while transaction_timeout
>>> is, well, per transaction. If there's a process which is going and has
>>> an open transaction and it's holding locks, that can be an issue.
>>>
>>
>> No, what I mean is this:
>>
>> BEGIN;
>> select * from foo;
>> update bar;
>> delete baz;
>>
>> Each one of those is subject to statement_timeout, yes? If so, then I
>> don't see a point for transaction timeout. You set statement_timeout for
>> what works for your environment. Once the timeout is reached within the
>> statement (within the transaction), the transaction is going to rollback
>> too.
>>
>
> This implies that a statement used takes a long time. It may not. The lock
> is held at the transaction level not the statement level, which is why a
> transaction level timeout is actually more useful than a statement level
> timeout.
>

It hard to compare these proposals because any proposal solves slightly
different issue and has different advantages and disadvantages. The flat
solution probably will by too limited. I see a possible advantages of
transaction_timeout (max lock duration), transaction_idle_timeout,
statement_timeout. Any of these limits has sense, and can helps with
resource management. There is not full substitution.

Regards

Pavel

>
> What I'm most interested in, in the use case which I described and which
> David built a system for, is getting that lock released from the lower
> priority process to let the higher priority process run. I couldn't care
> less about statement level anything.
>
> Thanks!
>
> Stephen
>