Re: keeping Connection alive

Hi Craig, folks,

I'm coming back to this topic, because I now do have an implementation which is more robust in respect to connection
failuresand the like and which follows Craig's idea. 

> > As far as I know there is no native mechanism for checking the
> state.
>
> Trying something and catching the SQLException if it fails. That
> reflects the design of the JDBC interface and of transactional
> databases
> in general that you should try to do something and cope if it fails,
> rather than ensuring it cannot fail.
>
>  > So one approach may be to implement a DataSource which does check
> (i.e. "SELECT 1") validity before returning the Connection (and
> reconnect if its not valid, i.e. throwing a SQLException?).
>
> Nononono....
>
> Have the DataSource _consumer_ deal with it.
>
> [...]
>
> ... where "invalidateConnection(Connection, Throwable)" tells the
> provider/pool that the connection is broken.
>
> [...]
>
> In practice, you'll also generally test e.getSQLSTate() to see if the
> exception might be a transient once tha doesn't imply the connection
> is
> broken, and re-try with the same connection if it is. There's no point
> dropping and re-creating the connection if it's a deadlock,
> serialization failure, or the like after all.
>
> Here's some code I use to check for SQLStates that may mean an
> operation
> is worth retrying:
>
>     private static final List<String> transientStates =
> Arrays.asList(
>              "08",   // Connection exceptions - refused, broken, etc
>              "53",   // Insufficient resources - disk full, etc
>              "57P0", // Db server shutdown/restart
>              "40001",// Serialization failure
>              "40P01"// Deadlock detected
>              );
>

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

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

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
retriedsome 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
advantageof returning faster after executing the relevant JDBC code because resources are closed on the separate
backgroundthread. 

I.e. in pseudo code:

void execute(WorkUnit unit) {

  do {
     unit.execute();
  } while (failed and retry-count is not reached};

  backgroundThread.close(unit); // a background thread will close the units ressources

}

Thanks a lot, I really appreciate this (and the other pgsql-*) mailinglist(s)!

Andreas

[1] http://www.artima.com/lejava/articles/jdbc_four3.html