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

>>
> >> 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
>