Re: Cancelling idle in transaction state

On Wed, 2009-12-30 at 05:02 -0500, Kris Jurka wrote:
> 
> On Tue, 29 Dec 2009, Simon Riggs wrote:
> 
> >> 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).
> >
> > Would this work with JDBC driver and/or general protocol clients?
> >
> 
> A Notice would be easy to overlook.  The JDBC driver wraps that as a 
> SQLWarning which callers need to explicitly check for (and rarely do in my 
> experience).  So when they run their next statement they'll get an error 
> saying that the current transaction is aborted, but they'll have no idea 
> why as the warning was silently eaten.  I'd prefer the transaction 
> cancellation to come as an Error because that's what it really is.

I'm not certain of all of these points, but here goes:

AFAIK, NOTICE was suggested because it can be sent at any time, whereas
ERRORs are only associated with statements.

http://developer.postgresql.org/pgdocs/postgres/protocol-flow.html#PROTOCOL-ASYNC
"It is possible for NoticeResponse messages to be generated due to
outside activity; for example, if the database administrator commands a
"fast" database shutdown, the backend will send a NoticeResponse
indicating this fact before closing the connection. Accordingly,
frontends should always be prepared to accept and display NoticeResponse
messages, even when the connection is nominally idle."

Can JDBC accept a NOTICE, yet throw an error? NOTICEs have a SQLState
field just like ERRORs do, so you should be able to special case that.

I understand that this will mean that we are enhancing the protocol for
this release, but I don't have a better suggestion.

> The only downside I can see is that a client would get confused if:
> 
> 1) Transaction starts.
> 2) Idle transaction is killed and error message is given.
> 3) Client issues rollback
> 4) Client gets error message from saying the transaction was cancelled.

Are you saying that the client should send rollback and that it should
generate no message?

-- Simon Riggs           www.2ndQuadrant.com