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