Обсуждение: Connection.isValid(int timeout) implementation

The current PostgreSQL driver (9.1-904) does not contain an
implementation of JDBC4 method Connection.isValid(int timeout). 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?

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@fastcrypt.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

On 06/08/2012 10:43 PM, Craig Ringer wrote:

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.

Found it on master branch. Thank you.

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?

They have a memory leak in JTS 4.16.4 (JBoss AS 7.1.1), so if you use XA datasource and the driver does not implement Connection.isValid, Transaction Manager will create and store in memory a new XA connection every 2 minutes.

<xa-datasource jndi-name="java:/datasources/Test" pool-name="Test" enabled="true" use-ccm="false">
  <xa-datasource-property name="ServerName">server</xa-datasource-property>
  <xa-datasource-property name="DatabaseName">database</xa-datasource-property>
  <driver>postgresql</driver>
  <xa-pool>
    <is-same-rm-override>false</is-same-rm-override>
    <interleaving>false</interleaving>
    <pad-xid>false</pad-xid>
    <wrap-xa-resource>false</wrap-xa-resource>
  </xa-pool>
  <security>
    <user-name>user</user-name>
    <password>password</password>
  </security>
  <statement>
    <share-prepared-statements>false</share-prepared-statements>
  </statement>
</xa-datasource>

On 06/09/2012 10:28 AM, Vlad Arkhipov wrote:

On 06/08/2012 10:43 PM, Craig Ringer wrote:

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.

Found it on master branch. Thank you.

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?

They have a memory leak in JTS 4.16.4 (JBoss AS 7.1.1), so if you use XA datasource and the driver does not implement Connection.isValid, Transaction Manager will create and store in memory a new XA connection every 2 minutes.

Good to know. Please post the reference to the JBoss JIRA ticket - assuming, as it sounds like, you're referring to an acknowledged bug.

It sounds like it'll be important to make sure that isValid() makes it into the 9.2 JDBC driver release.

Re your -ds.xml, does setting an explicit connection validator help?

<validation>     
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"></valid-connection-checker>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"></exception-sorter>
</validation>


Otherwise I think it'll use org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker, which I expect won't work without isValid().

--
Craig Ringer

On 06/09/2012 03:19 PM, Craig Ringer wrote:

On 06/09/2012 10:28 AM, Vlad Arkhipov wrote:

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?

They have a memory leak in JTS 4.16.4 (JBoss AS 7.1.1), so if you use XA datasource and the driver does not implement Connection.isValid, Transaction Manager will create and store in memory a new XA connection every 2 minutes.

Good to know. Please post the reference to the JBoss JIRA ticket - assuming, as it sounds like, you're referring to an acknowledged bug.

It sounds like it'll be important to make sure that isValid() makes it into the 9.2 JDBC driver release.

https://issues.jboss.org/browse/JBTM-1183

Re your -ds.xml, does setting an explicit connection validator help?

<validation>     
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"></valid-connection-checker>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"></exception-sorter>
</validation>


Otherwise I think it'll use org.jboss.jca.adapters.jdbc.extensions.novendor.JDBC4ValidConnectionChecker, which I expect won't work without isValid().

Yes, I have these lines in the datasource description. However the recovery subsystem does not use them.
I debugged a bit more and found out that it's also necessary to specify the recovery plugin.

<recovery no-recovery="false">
  <recovery-plugin class-name="org.jboss.jca.core.recovery.ValidatingManagedConnectionFactoryRecoveryPlugin" />
</recovery>