Error with PostgreSQL and setQueryTimeout

[Tama-Chan]

Hi all,

I'm using Spring's JdbcTemplate (through JdbcDaoSupport) to do some operations on a table. I'm using Spring 2.5.6.SEC01 and c3p0 0.9.1.2 (and Hibernate 3.3.1.GA, but it's not involved in this example).

When I run my code with a MySQL 5.0 back-end and mysql-connector-java 5.1.7 as a JDBC driver, everything works smoothly.

However, when I switch to PostgreSQL 8.3 with postgresql 8.3-603.jdbc3 or 8.3-603.jdbc4 driver, I get the following exception:

org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'run' on target class [class $Proxy15] failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update Config set value = ? where name = ?]; SQL state [0A000]; error code [0]; La fonction org.postgresql.jdbc3.Jdbc3PreparedStatement.setQueryTimeout(int) n'est pas encore implémentée.; nested exception is org.postgresql.util.PSQLException: La fonction org.postgresql.jdbc3.Jdbc3PreparedStatement.setQueryTimeout(int) n'est pas encore implémentée.
	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:269)
	at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:525)
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update Config set value = ? where name = ?]; SQL state [0A000]; error code [0]; La fonction org.postgresql.jdbc3.Jdbc3PreparedStatement.setQueryTimeout(int) n'est pas encore implémentée.; nested exception is org.postgresql.util.PSQLException: La fonction org.postgresql.jdbc3.Jdbc3PreparedStatement.setQueryTimeout(int) n'est pas encore implémentée.
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:858)
	at test.dao.misc.jdbc.ConfigDaoImpl.updateValue(ConfigDaoImpl.java:34)
	at test.dao.misc.jdbc.ConfigDaoImpl.recordProcessingTime(ConfigDaoImpl.java:48)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy5.recordProcessingTime(Unknown Source)
	at test.service.main.MainServiceImpl.run(mainServiceImpl.java:135)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
	at $Proxy15.run(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:276)
	at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:260)
	... 3 more
Caused by: org.postgresql.util.PSQLException: La fonction org.postgresql.jdbc3.Jdbc3PreparedStatement.setQueryTimeout(int) n'est pas encore implémentée.
	at org.postgresql.Driver.notImplemented(Driver.java:738)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.setQueryTimeout(AbstractJdbc2Statement.java:603)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setQueryTimeout(NewProxyPreparedStatement.java:1304)
	at org.springframework.jdbc.datasource.DataSourceUtils.applyTimeout(DataSourceUtils.java:255)
	at org.springframework.jdbc.core.JdbcTemplate.applyStatementSettings(JdbcTemplate.java:1203)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
	... 33 more

It's basically complaining that the function setQueryTimeout(int) is not implemented for this driver... However, I recall having worked with Spring and PostgreSQL before, and I've never encountered this. Could someone shed some light?

[dr_pompeii]

Hello Tama-Chan

It seems that this an error from the driver itself

http://www.nabble.com/setQueryTimeou...html#a14803093
http://archives.postgresql.org/pgsql...5/msg00047.php
and perhaps not resolved yet

Try to move back to some previous release , like you said it works.

[Tama-Chan]

Hello Tama-Chan

It seems that this an error from the driver itself

http://www.nabble.com/setQueryTimeou...html#a14803093
http://archives.postgresql.org/pgsql...5/msg00047.php
and perhaps not resolved yet

Try to move back to some previous release , like you said it works.

I though I had already tried this, but it turns out I hadn't been thorough enough... Reverting to version 8.2-504.jdbc3 or 8.2-504.jdbc4 of the driver did the trick. Thanks!

[brunojcm]

I though I had already tried this, but it turns out I hadn't been thorough enough... Reverting to version 8.2-504.jdbc3 or 8.2-504.jdbc4 of the driver did the trick. Thanks!

It did the trick because older versions don't validate the call to the setQueryTimeout(int), as you can see here:

===
Version 8.3-603 (2008-01-31):
...
Throw an exception if people try to use setQueryTimeout with a non-zero value as it is not implemented. Committed by jurka.
...
===

As this is not implemented yet (http://jdbc.postgresql.org/todo.html), the value passed to this parameter is ignored in older versions.