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