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

Craig, David,

Thank you very much for your detailed explanation. 

Regards,
Kiran Kulkarni

________________________________
From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Kiran Kulkarni <kkiran_kulkarni(at)yahoo(dot)com>
Cc: Dave Cramer <pg(at)fastcrypt(dot)com>; "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Sent: Thursday, June 28, 2012 11:15 PM
Subject: Re: [JDBC] 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