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

On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)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.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com