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
