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

Are you creating a single database connection and holding it open for a long period of time? — DaveH, Jun 24 '13 at 21:38
The socket is being closed by the server, probably by an idle timeout. — Brian Roach, Jun 24 '13 at 21:39

score 24 · Answer 1 · edited Apr 21 '22 at 10:36

24

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

edited Apr 21 '22 at 10:36

Kalle Richter

8,008
26
77
177

answered Feb 17 '15 at 15:51

David Artmann

4,272
1
16
24

Same on 9.3.2 on localhost (from a java jar called via command line) – Alessandro S. Feb 29 '16 at 09:47
1

I also getting the same problem. But I'm wondoring how the connection getting invalid? – apm Jul 07 '16 at 07:04
I think because of socket time-outs when the connection is not used for a particular time span. – David Artmann Jul 09 '16 at 10:34
I am getting the same excetion.Its a springboot project and i use Hikari connection pool.Where Hikari manages all connection related stuff.How can i resolve this in such a scenario? – Shamil Puthukkot Dec 30 '19 at 11:10
1

@ShamilPuthukkot are you using latest Spring Boot and Hikari Versions? Did you tweak any Hikari configration parameters? Also please take a look at this [tutorial](https://www.baeldung.com/spring-boot-hikari). Kind regards, David – David Artmann Jan 02 '20 at 08:57
@David Thanks for responding.I am using spring 2.1.3 and hikari 3.2.0. Also i have tweaked the hikari configs,which i have given in the question – Shamil Puthukkot Jan 02 '20 at 16:36
@ShamilPuthukkot unfortuantely, the comment section is the wrong place for further debugging. I suggest you ask a new question (if this problem is not already asked in a similar way) on SO. Then maybe link the question here as a comment. All the best. – David Artmann Jan 03 '20 at 19:27
@DavidArtmann Here is the question .https://stackoverflow.com/questions/59530069/intermittent-connection-issues-from-springboot-to-postgres Please have a look – Shamil Puthukkot Jan 06 '20 at 06:39

score 10 · Answer 2 · answered Jun 24 '13 at 21:39

10

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.

answered Jun 24 '13 at 21:39

wobblycogs

4,083
7
37
48

Thanks for your answers. @Dave - I have tried both ways, opening a new connection before the query and also leaving it open from a previous query. The problem reproduces itself either way. – James Jun 25 '13 at 13:53
But is the "new" connection coming from a connection pool or is it actually really new? If it's from a pool it's possible that the connection was broken when you got it. – wobblycogs Jun 25 '13 at 18:46
I'm not using a connection pool at present Wobbly. – James Jun 26 '13 at 09:15

score 6 · Answer 3 · answered Aug 23 '21 at 04:37

6

I got the same Exception.

In my case, My DB is getting restarted due to heavy usage of memory by some query. That's why I am getting that error during the restart of the DB.

solution: I optimized my query.

answered Aug 23 '21 at 04:37

Ashok Kumar Dhulipalla

199
3
10

score 1 · Answer 4 · answered Aug 24 '16 at 20:10

I had the same problem in a test, but the reason was in the call of a nextSequenceId between the creation of a PreparedStatement and the executeUpdate method call, using the same Connection object. My solution was move the call of nextSequenceId at the top of the method and the problem disappeared.

score 1 · Answer 5 · answered Jun 21 '19 at 20:48

1

i have same problem and solved my change is any of them:

your query is very large like:

SELECT * FROM 'Table' WHERE id in ?param

param is large list.

your result is very large (for example more than 4 GIG)

answered Jun 21 '19 at 20:48

Morteza Jalambadani

2,190
6
21
35

score 0 · Answer 6 · answered Sep 22 '22 at 07:17

0

We are using PostgreSQL 13.5 in a docker container and have the same exception. Double the memory size of the container solve the issue.

answered Sep 22 '22 at 07:17

Chushu Gao

1
1

score 0 · Answer 7 · answered Jan 27 '23 at 02:51

If you are using a Connection pool, you can setup a test query like "SELECT 1" - this will help to ensure the connection in the pool is ready before your query is executed. Spring Boot JPA - configuring auto reconnect

org.apache.commons.dbcp.BasicDataSource example:

 basicDataSource.testOnBorrow = true
 basicDataSource.validationQuery = "SELECT 1"

I noticed the issue most often after the application was sitting without much action but I think it can generally happen throughout the day as mentioned above.

score 0 · Answer 8 · answered Apr 03 '23 at 09:37

I had the same problem and I did a restart of docker services which solved the issue as said above could be a memory leak/issue with PGSQL.

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

docker-compose down

docker-compose up -d

score -4 · Answer 9 · answered Dec 03 '19 at 22:16

This applies to development environments for the most part.

In case somebody had this issue recently and is simultaneously using docker v19.03.5 under Ubuntu 19.10, it tends to interrupt network connections for some network-manager configurations. I haven't had a chance to debug this issue in details but if you are experiencing this issue I strongly recommend to try

$ sudo service docker stop

and give the connection one more shot.

Worked for me like a charm.

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

9 Answers9