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

From: David Steele <david(at)pgmasters(dot)net>
To: Stephen Frost <sfrost(at)snowman(dot)net>, Joe Conway <mail(at)joeconway(dot)com>
Cc: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Magnus Hagander <magnus(at)hagander(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions
Date: 2015-11-05 12:42:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 11/4/15 4: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.
> David (adding him to the CC) actually developed a utility specifically
> to identify what transactions are blocking what others and to kill off
> other processes if they were running for too long and blocking higher
> priority processes. It didn't matter, in that environment, if they were
> idle-in-transaction or actively running.

You are remembering correctly, Stephen, though there were different
timeouts for blocking transactions that were running and those that were
idle-in-transaction. We usually set the idle-in-transaction timeout
much lower as it measured not total transaction time but idle time since
the last state change. In that environment, at least, an
idle-in-transaction session was always due to a stuck process, bug, or
user session left open overnight. Because partitions and FKs were
continuously being created even ACCESS SHARE locks could be a problem.

The important thing about this implementation was that nothing was
terminated unless it had exceed a timeout AND was blocking another
process. A feature of this particular system was that it had very long
running transactions that needed to execute unless there was a conflict.

Even then, we'd get an alert some time in advance of the transaction
being terminated so we could make the judgement call to terminate the
other process(es) instead.


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2015-11-05 12:49:03 Re: September 2015 Commitfest
Previous Message Craig Ringer 2015-11-05 12:31:44 Re: Note about comparation PL/SQL packages and our schema/extensions