Re: Detecting 'socket errors' - closing the Connection object

On Mon, Jul 21, 2003 at 10:38:42AM -0700, David Wall wrote:
> I have a connection pool that opens several connections and passes them out
> to individual threads as needed.  Normally this works just fine.  However,
> if the postgresql database is restarted, or if the database is on another
> network segment, hiccups on the network will cause socket errors on the
> underlying JDBC connection.
>
> When a connection is returned to the pool, I'm calling the 'isClosed()'
> method to see if the underlying connection has been closed, but apparently
> when there is a socket error, the JDBC connection is not automatically being
> closed, despite the fact that such an error will always be unrecoverable.

Unfortunately the isClosed() javadoc is fairly explicit in saying you can't
use it for this:

  Retrieves whether this Connection object has been closed. A connection is
  closed if the method close  has been called on it or if certain fatal errors
  have occurred. This method is guaranteed to return true only when it is
  called after the method Connection.close has been called.

  This method generally cannot be called to determine whether a connection to
  a database is valid or invalid. A typical client can determine that a
  connection is invalid by catching any exceptions that might be thrown when
  an operation is attempted.

There is javax.sql.ConnectionEventListener.connectionErrorOccurred().
However, in my experience the current postgresql driver never calls this
(in fact, I'm yet to find a jdbc driver that does).

My current implementation wraps a DataSource in a custom
ConnectionPoolDataSource / PooledConnection pair that calls
connectionErrorOccurred() when any SQLException is thrown. This is a bit
gross, but works for our app which in general does not expect to see
exceptions under normal operation.

The final bit of the puzzle is that you usually need to *do* something on
the connection before you will notice connection errors, as at least the
postgres driver does not have a separate read thread running. So if you want
timely detection of errors, you need to periodically "select 1;" or
something similar on connections in your idle pool.

-O