Re: Connection.isValid(int timeout) implementation

On 06/08/2012 07:32 PM, Vlad Arkhipov wrote:
> The current PostgreSQL driver (9.1-904) does not contain an
> implementation of JDBC4 method Connection.isValid(int timeout).

Looks like it's since been implemented. It was commited by Dave Cramer
on 2012-01-19 in commit 773f8e45, based on a patch by Louis Flores.

commit 773f8e45fada12357d7b8650278fa3d37ac4d237
Author: Dave Cramer <davec(at)fastcrypt(dot)com>
Date: Thu Jan 19 20:00:51 2012 +0000

added isValid implementation from Luis Flores

$ git blame ./org/postgresql/jdbc4/AbstractJdbc4Connection.java
0cf874a4 (Kris Jurka 2006-06-08 10:34:52 +0000 116) public boolean
isValid(int timeout) throws SQLException
0cf874a4 (Kris Jurka 2006-06-08 10:34:52 +0000 117) {
3f525f1a (Kris Jurka 2009-11-18 11:19:31 +0000 118) checkClosed();
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 119) if (timeout < 0) {
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 120)
throw new PSQLException(GT.tr("Invalid timeout ({0}<0).", timeout),
PSQLState.INVALID_PARAMETER_VALUE);
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 121) }
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 122) boolean
valid = false;
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 123) Statement stmt =
null;
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 124) try {
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 125) if
(!isClosed()) {
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 126)
stmt = createStatement();
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 127)
stmt.setQueryTimeout( timeout );
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 128)
stmt.executeQuery( "SELECT 1" );
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 129)
valid = true;
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 130) }
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 131) }
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 132) catch (
SQLException e) {
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 133)
getLogger().log(GT.tr("Validating connection."),e);
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 134) }
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 135) finally
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 136) {
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 137)
if(stmt!=null) try {stmt.close();}catch(Exception ex){}
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 138) }
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 139) return
valid;
773f8e45 (Dave Cramer 2012-01-19 20:00:51 +0000 140) }

> However
> there are some projects that strongly rely on this method to test the
> connection (for example Arjuna Transaction Manager that is used by JBoss
> Application Server). Are there any plans of implementing this feature?

Really? JBoss AS 7.1.1.Final is quite happy with PgJDBC
(postgresql-9.1-901-1.jdbc4.jar) here. What's your configuration? Your
-ds.xml or jboss-cli "create datasource" command?

IMO the whole "validate" concept in JDBC is pretty broken, but that
doesn't mean PgJDBC shouldn't support it. The only way to validate a
connection is to use it. If it doesn't work, close it and get a new one.
Any other approach courts race conditions between isValid() and the use
of the connection.

It's arguably still OK to use isValid() to proactively weed dead
connections after server restarts to avoid the case where an app gets a
replacement for a dead connection and it's dead too, as it its
replacement, and so on. It's often misused, though.

BTW, I'm not convinced we should be sending a "SELECT 1;". It's valid to
send an empty string and I'd be inclined to do that instead. Still, I
presume there's a reason `SELECT 1` was used.

--
Craig Ringer