Re: BUG #4123: Statement.setQueryTimeout does not work with Postgres Java Driver

On Apr 22, 12:05 pm, amit(dot)muja(dot)(dot)(dot)(at)gmail(dot)com ("Amit Mujawar") wrote:
> The following bug has been logged online:
>
> Bug reference:      4123
> Logged by:          Amit Mujawar
> Email address:      amit(dot)muja(dot)(dot)(dot)(at)gmail(dot)com
> PostgreSQL version: 8.1
> Operating system:   Windows XP
> Description:        Statement.setQueryTimeout does not work with Postgres
> Java Driver
> Details:
>
> I am using PostgreSQL through JDBC
> PostgreSQL – 8.1, Driver - org.postgresql.Driver 8.1-408.jdbc3
>
> When I set Statement.setQueryTimeout, the timeout value does not show any
> effect on actual timeout...The query blocks for a specific time always [may
> be configured by another global variable - statement_timeout? not sure]
>
> I suspect there is a problem with JDBC driver implementation for
> setQueryTimeout API.
>

It is in the TODO list of the driver to be implemented. Actually the
TODO list for Postgres JDBC It is a good place to see what the
features you cannot use :-)

I am setting the timeout by the acquisition of the connection by the
pool (in ConnectionCustomizer in C3P0 pooling library) like that:

PreparedStatement s = null;
ResultSet rs = null;
try {
s = c.prepareStatement("SELECT set_config('statement_timeout', ?,
false);" );
s.setInt(1, 35000);
rs = s.executeQuery();
if ( rs.next() ) {
String newTimeout = rs.getString(1);
if ( logger.isInfoEnabled() ) {
logger.info("STATEMENT_TIMEOUT set to '" + newTimeout + "' (" +
parentDataSourceIdentityToken + ")");
}
} else {
if ( logger.isErrorEnabled() ) {
logger.error("STATEMENT_TIMEOUT could not be set! (" +
parentDataSourceIdentityToken + ")");
}
}
} catch (SQLException e) {
if ( logger.isErrorEnabled() ) {
logger.error("STATEMENT_TIMEOUT could not be set! (" +
parentDataSourceIdentityToken + ")", e);
}
} finally {
if ( rs != null ) rs.close();
if ( s != null ) s.close();
}

if you want to do it before you start some transaction, you can bring
this code into a function like Utils.setStatementTimeout(Connection c,
boolean isTransactionLocal) and call it after
Connection.setAutoCommit(false);