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

On Wed, Nov 4, 2015 at 4:15 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Joshua D. Drake (jd(at)commandprompt(dot)com) wrote:
>> On 11/04/2015 01:55 PM, Stephen Frost wrote:
>> >* Joe Conway (mail(at)joeconway(dot)com) wrote:
>> >>On 11/04/2015 01:24 PM, Alvaro Herrera wrote:
>> >>>I agree with Pavel. Having a transaction timeout just does not make any
>> >>>sense. I can see absolutely no use for it. An idle-in-transaction
>> >>>timeout, on the other hand, is very useful.
>> >>
>> >>+1 -- agreed
>> >
>> >I'm not sure of that. I can certainly see a use for transaction
>> >timeouts- after all, they hold locks and can be very disruptive in the
>> >long run. Further, there are cases where a transaction is normally very
>> >fast and in a corner case it becomes extremely slow and disruptive to
>> >the rest of the system. In those cases, having a timeout for it is
>> >valuable.
>>
>> 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.
>
> To be frank, my gut feeling is that transaction_timeout is actually more
> useful than statement_timeout.

Exactly. statement_timeout is weak because it resets for every
statement regardless of transaction. Similarly, pg_cancel_backend is
weak because it only works if a backend is actually in statement
regardless of transaction state (reading this thread, it's clear that
this is not widely known even among -hackers which further reinforces
the point).

Thus, I think we have consensus that transaction_timeout is good -- it
would deprecate statement_timeout essentially. Likewise,
pg_cancel_transaction is good and would deprecate pg_cancel_backend;
it's hard for me to imagine a scenario where a user would call
pg_cancel_backend if pg_cancel_transaction were to be available.

merlin