Re: PSQLException: An I/O error occured while sending to the backend

2010/10/12 Juan Argandoña <jargandona(at)bluestarenergy(dot)com>

> Hi Anthony.
> I have the same error into
> http://archives.postgresql.org/pgsql-jdbc/2005-04/msg00036.php . I have
> two locations, one from I connected and other from the users have
> connections. I think that they have wireless connection.
> I have a app with postgresql-8.2-506.jdbc3.jar.
>
> Can you talk how fixed these error, please?
>
>
If it is the firewall losing state about idle connections (and that seems a
reasonably good guess), then you could try using a connection pool within
the app and then configure it to test a connection before handing it back to
the app. You'll need to change your architecture such that your application
code calls close on a connection as soon as it is done with a transaction in
order to return the connection to the pool and then retrieve a fresh
connection whenever it is about to interact with the db. For example, the
commons DBCP BasicDataSource allows you to instruct it to test a connection
when a connection is returned to the pool, periodically while it resides in
the pool, and/or whenever a connection is retrieved from the pool. You can
specify the query that gets run to test the connection ('select 1'), etc.
You can either perform idle testing frequently enough that the firewall's
state table remains current, keeping the connections alive, or just allow
the testOnBorrow functionality to ensure that any dead connections will be
purged from the pool before they are handed to the application. If you've
already got a global connection pool, then you can configure the size of the
pool that is local to the application instance to be very small (or 1).

That's all assuming that the diagnosis provided in your linked email is
correct, of course.

You might also use a more up to date version of the jdbc driver. Enabling
tcp keepalive on the underlying tcp connection ought to be enough to keep
the connection up to date in the firewall's state table. Your referenced
email is quite old, so perhaps more recent versions of the driver do so.
See section 2.4 at the bottom of this page for an explanation of what I am
assuming is causing your problem:
http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/overview.html

--sam