>> > >> 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? Regards Pavel > > merlin >
