Re: Cancelling idle in transaction state

On Thu, 2009-12-24 at 21:38 +0100, Joachim Wieland wrote:
> On Sun, Dec 6, 2009 at 4:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > We are using NOTICE, not NOTIFY, assuming that we use anything at all
> > (which I still regard as unnecessary).  Please stop injecting confusion
> > into the discussion.
> 
> Attached is a minimal POC patch that allows to cancel an idle
> transaction with SIGINT. The HS patch also allows this in its current
> form but as Simon points out the client gets out of sync with it.

Thanks for working on this.

> The proposal is to send an additional NOTICE to the client and abort
> all open transactions and subtransactions (this is what I got from the
> previous discussion).

(Adding Kris into the discussion here.)

Would this work with JDBC driver and/or general protocol clients?

> I had to write an additional function AbortAnyTransaction() which
> aborts all transactions and subtransactions and leaves the transaction
> in the aborted state, is there an existing function to do this?

AbortOutOfAnyTransaction()

> We'd probably want to add a timeout for idle transactions also (which
> is a wishlist item since quite some time) and could also offer user
> functions like pg_cancel_idle_transaction(). Along this we might need
> to add internal reasons like we do for SIGUSR1 because we are now
> multiplexing different functionality onto the SIGINT signal. One might
> want to cancel an idle transaction only and not a running query,
> without keeping track of internal reasons one risks to cancel a
> legitimate query if that backend has started to work on a query again.

Next project, not both at once.

-- Simon Riggs           www.2ndQuadrant.com