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

On Thu, Nov 5, 2015 at 12:09 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> Dne 5.11.2015 19:02 napsal uživatel "Merlin Moncure" <mmoncure(at)gmail(dot)com>:
>>
>> 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.
>>
>
> I am sorry, I see a consensus between you and Stephen only.

:-). I guess then maybe not. Note, I'm not taking a position on other
proposed settings. I'm not claiming they (noted above) solve every
problem, just that they are good. IOW, the (narrow) claim
pg_cancel_backend and statement_timeout are broken and need to be
fixed.

If you disagree, then you are arguing that it's bad to give
administrator ability to cancel running transaction regardless of
execution state.

merlin