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

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