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

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.

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