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

(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