Skip site navigation (1) Skip section navigation (2)

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

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: PostgreSQL JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
Date: 2012-06-29 03:09:09
Message-ID: 4FED1C55.4050507@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
(I accidentally sent this direct a couple of days ago; re-posting on-list)

On 06/28/2012 06:15 AM, Kiran Kulkarni wrote:
> I am getting this error couple of time in a day. How do I avoid it?
>
> Before making the call, I am checking these below things.
> session.isDirty: false
> session.isOpen: true
> session.isConnected: true
> session.getStatistics: SessionStatistics[entity count=0collection 
> count=0]
>
> connManager.isCurrentlyConnected(): true
> connManager.hasBorrowedConnection(): false
>
> But even after this check i am getting this error. Pls help.

Your connections are timing out. Without knowing how you connect to your 
database (host, via what conection, routers and nat in the way, etc) 
it's had to say why. You also haven't shown the PostgreSQL logs, which 
may contain additional information.

Setting a tcp keepalive may help.

More importantly though, your app must be robust in the face of errors 
and failed connections. You can test that a connection is OK, then go to 
us it and find that it broke in the tiny interval since you tested it - 
maybe the DB server got restated, maybe the network had a hiccup, 
whatever. You need to be able to open a new connection and retry your 
work when the connection fails, same way you need to be able to reissue 
a unit of work when there's a serialization failure in a serializable 
transaction.

Most JDBC work should look roughly like:

boolean work_done = false;
do {
   try {
      .. open a transaction ...
      .. do the work ...
      .. commit ...
   } catch ( ... various jdbc and ORM exceptions ... ) {
     .. Terminate the transaction
     try {
       ... issue a dummy query to see if the connection is usable
     } catch ( ... exception types ... ) {
       .. connection unusable, get a new connection
     }
   }
} while (!work_done);


The same logic applies if you're using an ORM like Hibernate, but you 
*also* have to cope with the fact that Hibernate and other JPA 
implementations leave your object graphs in an invalid state if a 
database operation fails. To cope with this, use Hibernate's 
SerializatiionHelper ( 
http://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/util/SerializationHelper.html) 
to clone your object graph before merging/persisting. If there's an 
error in the database operation, discard the objects used in the 
operation, clone a new set from your backup copy, and try to commit them 
instead.

You can avoid cloning backups if it's easier for you to discard the 
object graph, fetch another from the database (usually actually from 
Hibernate's L1 cache), modify them again, and re-merge them.

Either way, your database access logic then looks like this:

.. (maybe) clone object graph ...
boolean work_done = false;
do {
   try {
      .. open a transaction ...
      .. do the work ...
      .. commit ...
   } catch ( ... various jdbc and ORM exceptions ... ) {
     .. Terminate the transaction
     try {
       ... issue a dummy query to see if the connection is usable
     } catch ( ... exception types ... ) {
       .. connection unusable, get a new connection
     }
     .. replace the object being merged/committed with a fresh one
     .. either by repeating the work or by restoring a cloned copy.
   }
} while (!work_done);


Annoying? Welcome to robustly using databases from application code. The 
alternative is accepting that your users will see database access errors 
you could prevent and possibly have to re-do work when a transient 
database issue occurs. I don't think that's good enough.

You shouldn't be repeating this stuff everywhere, though; when I was 
using non-container-managed transactions I had helper class that does 
most of the work. I just extended it with anonymous inner classes that 
overrode prepareGraphForPersist() and persistGraph() methods in my 
helper. I might still be able to find it somewhere; let me know if it'd 
be useful.

--
Craig Ringer

In response to

pgsql-jdbc by date

Next:From: Craig RingerDate: 2012-06-29 03:15:05
Subject: Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend
Previous:From: Kiran KulkarniDate: 2012-06-28 16:15:24
Subject: Re: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group