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

On 10/30/2015 10:20 PM, Merlin Moncure 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.
> Similarly, transaction_timeout would be a lot more effective than
> statement_timeout.  It's nice to think about a world where
> applications don't do such things, but in this endless sea of
> enterprise java soup I live it it's, uh, not realistic.  This would be
> lot cleaner than the cron driven sweep I'm forced to implement now,
> and could be made to be part of the standard configuration across the
> enterprise.

I would like to request that no one work on this.

I wrote a patch to do just that a year and a half ago[1] which was
rejected for technical reasons.  Since then, Andres has fixed those
reasons, and prodded me last week at PGConf.EU to pick my patch back up.I am planning on resubmitting it for the next
commitfest. I will also
 
take into account the things said on this thread.

[1] http://www.postgresql.org/message-id/538DC843.2070608@dalibo.com
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support