Re: Cancelling idle in transaction state

On 30/12/2009 7:37 PM, Simon Riggs wrote:

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

The JDBC driver would have to throw when the app code next interacted 
with the connection object anyway. It can't asynchronously throw an 
exception. Since the next interaction that can throw SQLException() is 
likely to be setup for or execution of a query, I'm not sure it makes 
any difference to the JDBC user whether query cancellation is reported 
as a NOTICE or an ERROR behind the scenes.

Since the proposed patch leaves cancelled transactions in the error 
state, rather than closing them and leaving the connection clean and 
idle, it doesn't matter much if a client doesn't understand or check for 
the NOTICE. The app code will try to do work on the connection and that 
work will fail because the transaction is aborted, resulting in a normal 
SQLException reporting that the "current transaction is aborted ...".

JDBC-using code has to be prepared to handle exceptions at any point of 
interaction with the JDBC driver anyway, and any code that isn't is 
buggy. Consequently there's LOTS of buggy JDBC code out there :-( as 
people often ignore exceptions thrown during operations they think 
"can't fail". However, such buggy code is already broken by 
pg_cancel_backend() and pg_terminate_backend(), and won't be broken any 
more or differently by the proposed change, so I don't see a problem 
with it.

Personally, I'd be happy to leave the JDBC driver as it was. It might be 
kind of handy if I could getWarnings() on the connection object without 
blocking so I could call it before I executed a statement on the 
connection ... but that'd always introduce a race between transaction 
cancellation/timeout and statement execution, so code must always be 
prepared to handle timeout/cancellation related failure anyway.

As you say, the driver can special-case connection cancelled NOTICE 
mesages as errors and throw them at next user interaction it wants. But 
I'm not sure that's anything more than a kind of nice-to-have cosmetic 
feature. If the JDBC driver handled the NOTICE and threw a more 
informative SQLException to tell the app why the transaction was dead, 
that'd be nice, but hardly vital. It'd want to preserve the notice as an 
SQLWarning as well.

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

Only in an extremely backward compatible way - and it's more of a 
behavior change for the backend than a protocol change. Pg's backends 
change behaviour a whole lot more than that in a typical release...

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

For JDBC users, "there is no transaction in progress" is only reported 
as a SQLWarning via getWarnings(), so I'd be surprised if anything used 
it for more than logging or debugging purposes.

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

--
Craig Ringer