Skip site navigation (1) Skip section navigation (2)

Re: keeping Connection alive

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Andreas Brandl <ml(at)3(dot)141592654(dot)de>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: keeping Connection alive
Date: 2009-12-21 03:05:53
Message-ID: 4B2EE611.7020605@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
On 21/12/2009 10:24 AM, Andreas Brandl wrote:

> In the course of implementing I discovered, that in JDBC 4 there are subtypes of SQLException designed for distinguishing transient and non-transient failures. These are SQLTransientException, SQLNonTransientException and SQLNonTransientConnectionException. Quite an old source is [1].

Hmm, I'd never noticed the subtypes of SQLException.

> My understanding is that in general distinguishing by the type of SQLException is sufficient for this purpose. Though I wonder if the postgresql-jdbc does implement this?

It does not appear to - at least, those exception names don't appear 
anywhere in the sources. It only seems to throw PSQLException, which is 
a subtype of SQLException.

The full heirarchy is here, by the way:

   http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html


Unfortunately, the exception descriptions don't seem to be as clear as 
they could be regarding what exactly "transient" means.

SQLRecoverableException describes something the app can recover from by 
at minimum re-connecting and re-trying. That's clear enough.

SQLTransactionRollbackException would be a good one to be able to use 
for deadlock-rollback. It's similarly clear.

SQLTransientException is for when "a previoulsy failed operation might 
be able to succeed when the operation is retried without any 
intervention by application-level functionality." This isn't very clear. 
Does that mean "retrying *this* *statement* may work, you don't have to 
re-try the whole transaction" ? That's how I read it. If it refers to 
just the one JDBC operation as it seems to, it doesn't really apply to Pg.

In that case, is there even a suitable class for the most important case 
in Pg - "if you re-try the whole transaction this will probably succeed, 
but re-trying this particular statement is pointless. You don't have to 
reconnect." ?



Anyway: To implement the use of SQLTransientException etc might be 
interesting given that the Pg driver supports JDBC2, JDBC3 _and_ JDBC4 
drivers in one source package. It'd need an exception factory that was 
part of the JDBC-version-specific code, which took an exception type 
param as well as the usual msg, sqlstate and cause. The factory for 
JDBC2 / JDBC3 would return a normal PSQLException, and the JDBC4 factory 
would override it to return a PSQLException subclass derived from one of 
the JDBC SQLException subtypes (for JDBC4).

The JDBC4 factory wouldn't even get built when building the JDBC2/3 
drivers, so the exceptions being missing from older JDKs wouldn't matter.



> Another question arising with implementing a more robust connection handling is the following. Suppose there is a 'WorkUnit' containing the concrete jdbc-code which gets executed in a single transaction which may get rolled back and retried some more times.
>
> The execution of the 'WorkUnit' generates some Statement- and ResultSet-instances which have to be closed after execution.
>
> My question is if it is advisable to do this closing of resources on a background worker thread. This would have the advantage of returning faster after executing the relevant JDBC code because resources are closed on the separate background thread.

Closing the statement and associated result set shouldn't take long. I'd 
be reluctant to add the additional complexity, myself. Also, while the 
PgJDBC driver _is_ thread safe, this still seems like it's asking for 
threading issues. I'm no JDBC expert, though.

--
Craig Ringer

In response to

Responses

pgsql-jdbc by date

Next:From: Craig RingerDate: 2009-12-21 03:08:17
Subject: Re: keeping Connection alive
Previous:From: Andreas BrandlDate: 2009-12-21 02:24:35
Subject: Re: keeping Connection alive

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group