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

Asked 24/6, 2013 at 21:21 Answered 28/12, 2023 at 6:35

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

Amberly answered 24/6, 2013 at 21:21 Comment(2)

Are you creating a single database connection and holding it open for a long period of time? – Trotman 24/6, 2013 at 21:38

The socket is being closed by the server, probably by an idle timeout. – Lacrimatory 24/6, 2013 at 21:39

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

Grasso answered 17/2, 2015 at 15:51 Comment(8)

Same on 9.3.2 on localhost (from a java jar called via command line) – Ambassador 29/2, 2016 at 9:47

I also getting the same problem. But I'm wondoring how the connection getting invalid? – Swor 7/7, 2016 at 7:4

I think because of socket time-outs when the connection is not used for a particular time span. – Grasso 9/7, 2016 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? – Varuna 30/12, 2019 at 11:10

@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. Kind regards, David – Grasso 2/1, 2020 at 8: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 – Varuna 2/1, 2020 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. – Grasso 3/1, 2020 at 19:27

@DavidArtmann Here is the question .#59530569 Please have a look – Varuna 6/1, 2020 at 6:39

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.

Lithiasis answered 24/6, 2013 at 21:39 Comment(3)

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. – Amberly 25/6, 2013 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. – Lithiasis 25/6, 2013 at 18:46

I'm not using a connection pool at present Wobbly. – Amberly 26/6, 2013 at 9:15

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.

Balaklava answered 23/8, 2021 at 4:37 Comment(1)

This was very helpful, I was curios to know how you noticed that the memory was a bottleneck – Miun 15/9, 2023 at 15:21

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.

Bonnice answered 24/8, 2016 at 20:10 Comment(0)

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)

Hardesty answered 21/6, 2019 at 20:48 Comment(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.

Tatianna answered 22/9, 2022 at 7:17 Comment(1)

or free some host OS memory so that docker can take it – Erzurum 13/10, 2023 at 12:33

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.

Heretic answered 27/1, 2023 at 2:51 Comment(0)

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

Orv answered 3/4, 2023 at 9:37 Comment(0)

I had the same problem (Postgresql 13). The error message suggests a connection problem, but that was misleading. I had edited a plpython3u Python function, which later produced a run time error, which in turn produced the "I/O error while sending to the backend". A similar error could occur when the windows Python related environment variables have been changed and prevent Python from running properly.

Germinant answered 18/9, 2023 at 13:59 Comment(0)

I was with the same error, and it doesnot making sense to me, so i read in some articles that this problem can be a firewall problem in the db connection. What works to me is change the http port of my quarkus project, it was on 8181, then i change to 9090 and it works!

Revocable answered 14/12, 2023 at 12:50 Comment(1)

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Succor 19/12, 2023 at 12:13

In my case, I have a query retrieving 1G bytes data from Postgresql, and My SQL has a big IN condition like my_in_filed IN (?,?,?,?,? ...) which contains 300+ values.

I used advice from @Morteza Jalambadani to refactor MySQL, and use sub-query as an alternative to the original one, and the error disappeared.

Holloman answered 28/12, 2023 at 6:35 Comment(0)

-4

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.

Triclinium answered 3/12, 2019 at 22:16 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags