On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure <mmoncure@gmail.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 :)
