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