PostgreSQL Exception: "An I/O error occured while sending to the backend"

Question

I am testing some code which processes registration to a website. The java code is as follows (excerpt):

if (request.getParameter("method").equals("checkEmail")){
            String email= request.getParameter("email");
            ResultSet rs =null;
            PreparedStatement ps = db.prepareStatement(query);
            ps.setString(1, email);
            rs = ps.executeQuery();             
            if(rs.next()){ 
                            //email already present in Db 
            } else {
                            //proceed with registration.....

Most of the time the process executes without any problem, but I am getting an intermittent issue where it fails because connection to the database is closing. Every time it fails, it fails at the same point - when running the prepared statement above (which checks whether the email being submitted is already in the database obviously).

Version of Postgres is 8.1.23

Any help or suggestions appreciated. Stacktrace is as follows (EDIT: Sometimes the Stacktrace says caused by Stream Closed, and sometimes Socket Closed as below):

13:53:00,973 ERROR Registration:334 - org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.

  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
  at Registration.doPost(Registration.java:113)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
  at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
  at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
  at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)
  at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:769)
  at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:698)
  at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:891)
  at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
  at java.lang.Thread.run(Thread.java:595)

Caused by: java.net.SocketException: Socket closed

  at java.net.SocketInputStream.socketRead0(Native Method)
  at java.net.SocketInputStream.read(SocketInputStream.java:129)
  at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
  at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
  at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
  at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    ... 22 more      

Answer 1

I got the same Exception with PostgreSQL 8.4, but I connect to a local db on the same host. The reason was the connection, which was not valid any more and so I needed to open it again.

There is a ticket on postgresql.org which has a related topic. Their answer is pretty similar, by just catching the exception and re-open the connection

Answer 2

I suspect that your application and the database are on different machines and there's a (stateful) firewall somewhere in between. My guess is that the firewall is dropping the connection after it has been open for a certain amount of time perhaps with no traffic on it. The connection pool wouldn't be able to detect this before handing you a broken connection.

The only thing that makes me doubt this is that it's always happening in the same place in the code but if that is the first database query in a new session (or something like that) it's not inconceivable it could always appear in the same place.