Re: Cancelling idle in transaction state

Simon Riggs wrote:
> 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."

Could we send an asynchronous notification immediately when the
transaction is cancelled, but also change the error message you get in
the subsequent commands. Clients that ignore the async notification
would still see a proper error message at the ERROR.

Something like:

ERROR: current transaction was aborted because of conflict with
recovery, commands ignored until end of transaction block

instead of the usual "current transaction is aborted, commands ignored
until end of transaction block" message.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com