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

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
wrote:

> On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure <mmoncure(at)gmail(dot)com>
> wrote:
> >
> > Idle hanging transactions from poorly written applications are the
> > bane of my existence. Several months back one of them took down one
> > of hour production websites for several hours.
> >
> > Unfortunately, the only way to deal with them is to terminate the
> > backend which is heavy handed and in some cases causes further damage.
> > Something like pg_cancel_transaction(pid) would be nice; it would
> > end the transaction regardless if in an actual statement or not.
> >
>
> Why pg_cancel_backend(pid) is not sufficient for the above use case?
> Basically you want to rollback current transaction, I think that can be
> achieved by pg_cancel_backend.
>

Not when the session is idle in transaction, only when it's actually doing
something.

IIRC one of the reasons is that when idle in transaction, the client is not
expecting any response, and would get out of sync. I know this has been
discussed a number of times, so a better explanation can probably be found
in the archives :)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/