>> >> That sounds to be a solution for this problem or otherwise for such a case >> can't we completely abort the active transaction and set a flag like >> PrevCommandFailed/PrevTransFailed and on receiving next message if >> such a flag is set, then throw an appropriate error. > > This is only partial solution - when some application is broken, then there > will be orphaned sessions. It is less wrong, than orphaned connections, but > it can enforce some issues too. The solution of this problem should to work > well with session pool sw like pgbouncer and similar.
I wrote a nonsense - should be "It is less wrong, than orphaned transaction"
Sure. Unfortunately it's not always practical to do so when you have 100's of applications running against 100's of databases, all written by teams of variable quality, some of whom have been ejected for overseas devlopment or vice versa. This is the world I live in.
I would to say so the breaking transaction is not enough - it needs some protocol enhancing. There is a advantage of terminate_session, because if keep_alive packets are used, then client can to know so session is broken in few seconds.
The point stands that neither pg_cancel_backend or statement_timeout (especially) provide *any* kind of safety guarantees because they only work if execution is in the database. All the locks they hold and other long running issues pertaining to long running transactions (say, advancing xmin) are silent killers with no automatic way of detecting or destroying. I understand the challenges here -- not griping in any way -- the workaround is to cron up an executioner. Just pointing out we have an issue.
It is 100% true. But the users can do strange things. If we solve idle transactions and not idle session, then they are able to increase max_connections to thousands with happy smile in face.
I have not strong idea about how to solve it well - maybe introduce transaction_idle_timeout and session_idle_timeout?
