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

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.

> IIRC one of the reasons is that when idle in transaction, the client is not
> expecting any response, and would get out of sync. I know this has been
> discussed a number of times, so a better explanation can probably be found
> in the archives :)

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.

I don't think this would be exactly trivial, but it's probably doable.

regards, tom lane