Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend - Mailing list pgsql-jdbc

From Kiran Kulkarni
Subject Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
Date
Msg-id 1340947028.19372.YahooMailNeo@web161406.mail.bf1.yahoo.com
Whole thread Raw
In response to Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend  (Craig Ringer <ringerc@ringerc.id.au>)
List pgsql-jdbc
Craig, David,

Thank you very much for your detailed explanation

Regards,
Kiran Kulkarni


From: Craig Ringer <ringerc@ringerc.id.au>
To: Kiran Kulkarni <kkiran_kulkarni@yahoo.com>
Cc: Dave Cramer <pg@fastcrypt.com>; "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.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


pgsql-jdbc by date:

Previous
From: Craig Ringer
Date:
Subject: Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
Next
From: "Darr, Terry L"
Date:
Subject: postgresql and db2 as the data bases using XA JDBC running java programs in EJB