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
benice; 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.
merlin