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

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.

> Similarly, transaction_timeout would be a lot more effective than
> statement_timeout.
>

I think here by transaction_timeout you mean to say cancel all
transactions that are idle for transaction_timeout time. So it is better
to call it as transaction_idle_timeout. Having said that I am not sure
if holding such a connection is meaningful either because I think there
is high probablity that user of such a session might not perform any further
action for a long time, so why not have idle_timeout to indicate the
termination
of session if it is idle for idle_timeout time.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com