Thread: 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:Found it on master branch. Thank you.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.
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.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?
<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:
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/08/2012 10:43 PM, Craig Ringer wrote:On 06/08/2012 07:32 PM, Vlad Arkhipov wrote:Found it on master branch. Thank you.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.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.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?
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:
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>
On 06/09/2012 10:28 AM, Vlad Arkhipov wrote:https://issues.jboss.org/browse/JBTM-1183However 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?Yes, I have these lines in the datasource description. However the recovery subsystem does not use them.
<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().
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>