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

On Apr 22, 12:05=A0pm, amit.muja...@gmail.com ("Amit Mujawar") wrote:
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A04123
> Logged by: =A0 =A0 =A0 =A0 =A0Amit Mujawar
> Email address: =A0 =A0 =A0amit.muja...@gmail.com
> PostgreSQL version: 8.1
> Operating system: =A0 Windows XP
> Description: =A0 =A0 =A0 =A0Statement.setQueryTimeout does not work with =
Postgres
> Java Driver
> Details:
>
> I am using PostgreSQL through JDBC
> PostgreSQL =96 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 [m=
ay
> 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 =3D null;
ResultSet rs =3D null;
try {
  s =3D c.prepareStatement("SELECT set_config('statement_timeout', ?,
false);" );
  s.setInt(1, 35000);
  rs =3D s.executeQuery();
  if ( rs.next() ) {
    String newTimeout =3D 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 !=3D null ) rs.close();
  if ( s !=3D 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);