Re: Serializable transactions and SQLException

Giampaolo Tomassoni wrote:
> Dears,
> 
> I would like to develop java code using the pgsql-jdbc driver on serializable 
> isolated transactions. The (general) pgsql manual states that this, of 
> course, may cause concurrent updating transaction failures to inform the 
> client to retry the transaction as a whole.
> 
> This is said to be reported by the error message:
> 
> 	ERROR:  Can't serialize access due to concurrent update
> 
> 
> Great. I want to cope with it. But what's the SQLException.getSQLState() value 
> associated to this? Or is it a SQLException.getErrorCode()? Is this value 
> something 'standard', in the sense that, ie., if I need to switch to Oracle 
> it works fine? Is there a better way to identify 'please, retry' suggestions 
> than browsing the SQLException object?

Your best bet is to use a 7.4 or later server and inspect the SQLState 
of the exception.

To find out what SQLState to expect, either try it and see, or take a 
look at src/include/utils/errcodes.h in the server source tree and pair 
it up with the appropriate ereport() call you are interested in. For 
transaction serialization failures it is 
ERRCODE_T_R_SERIALIZATION_FAILURE which has a SQLState of 40001.

SQLStates are somewhat standardized around SQL99. The comments in 
errcodes.h say:

>>  * The SQL99 code set is rather impoverished, especially in the area of
>>  * syntactical and semantic errors.  We have borrowed codes from IBM's DB2
>>  * and invented our own codes to develop a useful code set.

I have no idea what Oracle does in this area.

-O