Re: keeping Connection alive

On Dec 12, 2009, at 6:43 PM, Andreas Brandl wrote:
> I'm using a PostgreSQL as backend for a Java application, which may idle for quite a while (i.e. hours) and now and then issue a query on the backend.

Is this a standard Java application or is it running in a Java EE or servlet container?

> -- snip --
> org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
> -- snip --
>
> What is best practice to prevent the connection from breaking, i.e. keeping the Conection alive?
>
> I'm using an instance of PGPoolingDataSource and would expect to retrieve a _valid_ Connection when calling DataSource.getConnection() (which I don't get). I even did call PGPoolingDataSource.setTcpKeepAlive(true) - same result.

If you're not using an application server, and/or don't need pooling, perhaps it would be better to use the regular jdbc api something like this:
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(jdbcConnUrl, username, password);
// then conn.createStatement , etc.

Then you can call conn.close() and create a new connection when necessary.

> My first approach was to use a scheduled task and regularly check the Connection for validity ("ping") - unfortunately Connection.isValid(int timeout) has not been implemented yet.
> My current workaround is a scheduled task issueing a "SELECT 1" on a regularly basis. Which I feel is not the best solution, though.
>
> I read of using a connection pool like dbcp, which I'd like to avoid: I don't need connection pooling at all, just need to have a valid Connection even after hours of no-operation (a reconnect is ok, so no performance issue here).

I've only had issues with pooled connections when I've misconfigured a setting in the app server (it should manage the connections and automatically close and reconnect every so often) or when I've had an application bug that was not releasing connections because of unintended long running transactions.

Hope that helps.

-Jovan Jester

> I'm using PostgreSQL 8.3.8 and postgresql-8.4-701.jdbc4.jar for JDBC.
>
> Thanks alot!
>
> Best regards,
> Andreas
>