Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions - Mailing list pgsql-hackers

On Mon, Nov 2, 2015 at 1:28 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> 2015-11-02 5:23 GMT+01:00 Amit Kapila <amit.kapila16@gmail.com>:
>>
>> On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >
>> > Magnus Hagander <magnus@hagander.net> writes:
>> > > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit.kapila16@gmail.com>
>> > > wrote:
>> > >> 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.
>> >
>>
>> Okay, thats right and the reason is that while reading message from
>> client,
>> if an error occurs, it can loose track of previous and next messages and
>> that
>> could lead to an unrecoverable state.
>>
>> >
>> > I think in principle it could be done by transitioning the backend into
>> > a new xact.c state, wherein we know that the active transaction has been
>> > canceled (at least to the extent of releasing externally visible
>> > resources
>> > such as locks and snapshots), but this fact hasn't been reported to the
>> > connected client.  Then the next command submitted by the client would
>> > get
>> > a "transaction cancelled" error and we'd go into the normal transaction-
>> > failed state.
>> >
>>
>> 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.

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.

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.

merlin