Re: Cancelling idle in transaction state

On Wed, 30 Dec 2009, Simon Riggs wrote:

> 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."

The problem is that frontends won't check the backend connection until
they've already been given the next command to execute at which point it's
too late. I think a lot of the discussion on this thread is wishful
thinking about when a frontend will see the message and what they'll do
with it. You would either need a multithreaded frontend that had some
type of callback mechanism for these notices, or you'd need to poll the
socket every so often to see if you'd received a notice. I don't think
that describes most applications or client libraries.

>
> 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.

Yes, that's possible.

>> 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?

No, I'm saying if for some business logic reason the client decided it
needed to rollback as it hadn't seen the error message yet.

Kris Jurka