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
thenissue 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
jdbcapi 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") -
unfortunatelyConnection.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
needto 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
connectionsand automatically close and reconnect every so often) or when I've had an application bug that was not
releasingconnections 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
>