Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend

On 06/29/2012 12:15 AM, Kiran Kulkarni wrote:
> Hello David,
> We are using postgres 9.0 and JDBC driver is postgresql-8.4-701.jdbc3.jar.

Try to use a JDBC driver at least as new, if not newer than, the server.
The client(s) should always be the same release as or newer than the
server. The same goes for psql, libpq, etc.

> Our app server & DB, both are running on Linux and there is a firewall
> between app server and DB.
Tharrr's yer problem.

Does the firewall do NAT or stateful connection tracking? If so, you
/must/ use keepalives to avoid connection drops if your connections are
idle for long. If you don't know, then your firewall probably does do
connection tracking and thus you must use keepalives.

If you're on a Java EE application server you can get the server to send
a dummy connection check statement like "SELECT 1" every now and then
instead of using OS level keepalives. I'd prefer an OS level keepalive,
though, as unlike connection check statements they don't fill the logs
with noise.

> Can upgrading the driver to higher version fix this problem?

Probably not if there's a firewall involved.
>
> I have seen similar posts on the web. Most tell either to reconnect or
> to try TCP Keep alive.
> For my case, hibernate is managing my connections and I would expect
> hibernate to deal with such scenarios.
It doesn't and it can't.

If you configure your connection pool to verify/check connections you'll
reduce the frequency with which this happens, but not eliminate it. Your
app MUST handle connection and transaction failures if it wants to be
robust. It's a pain, but necessary.

> But since it is not dealing with such issues I would be more
> comfortable configuring tcp keep alive rather than meddling hibernate
> managed connections.
>
> Do you know any configuration in hibernate which takes care of such
> issues? Or else pls give me the steps to configure tcp keep alive.
>
Hibernate cannot do everything for you. If the database connection is
lost partway through a transaction then YOU must reissue the work. It'd
be nice if Hibernate could handle the reconnect-and-retry logic for you,
but at the moment it can't - and in fact it even mangles your entity
objects when a transaction fails.

I wrote a post on another thread about this a day or two ago, but
accidentally forgot to CC the list. I've re-posted it with subject "Re:
[JDBC] org.postgresql.util.PSQLException: An I/O error occured while
sending to the backend" and it should appear in the archives as
http://archives.postgresql.org/message-id/4FED1C55.4050507@ringerc.id.au
when the archives are next updated.

--
Craig Ringer