Java PostgreSQL 异常：org.postgresql.util.PSQLException：发送到后端时发生 I/O 错误

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

提问by Sujith PS

I know it is a duplicate question . But I couldn't find solution for the same.

我知道这是一个重复的问题。但我找不到相同的解决方案。

I have hosted my application in the Amazon EC2 cloud. And I am using postgresql .

我在 Amazon EC2 云中托管了我的应用程序。我正在使用 postgresql 。

I am getting an exception org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.while running my application in Amazon cloud.

org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.在Amazon 云中运行我的应用程序时出现异常。

The detailed stack-trace is :

详细的堆栈跟踪是：

org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:331)
    at com.spy2k3.core.business.processor.ProcessorImpl.executeUpdate(ProcessorImpl.java:237)
    at com.spy2k3.core.business.object.BusinessObject.executeUpdate(BusinessObject.java:54)
    at com.spy2k3.core.business.object.LoginObject.deleteSession(LoginObject.java:127)
    at com.spy2k3.core.business.processor.LoginProcessor.userValidation(LoginProcessor.java:79)
    at com.spy2k3.core.business.processor.LoginProcessor.execute(LoginProcessor.java:30)
    at com.spy2k3.core.business.processor.ProcessorImpl.process(ProcessorImpl.java:73)
    at com.spy2k3.core.handler.request.RequestHandler.doService(RequestHandler.java:90)
    at com.spy2k3.core.handler.AbstractHandler.doPost(AbstractHandler.java:25)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
    at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
    at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
    at java.lang.Thread.run(Thread.java:662)
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:143)
    at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112)
    at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71)
    at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:269)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1700)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    ... 37 more

Tests :

测试：

1.I connected my remote postgresql server via PgAdmin from my local system , and I could connect and execute queries.

1.我通过本地系统的 PgAdmin 连接了我的远程 postgresql 服务器，我可以连接和执行查询。

2.I connected to my remote server via putty , and could successfully execute queries. EXAMPLE :

2.我通过 putty 连接到我的远程服务器，并且可以成功执行查询。例子 ：

    [root@ip-xx-xxx-xx-xxx bin]# psql -U myuser -d mydatabase
    psql (9.2.4)
    Type "help" for help.

mydatabase=# SELECT USERID FROM MY_MAST_LOGINSESSION WHERE SESSIONID='5DFD5D1E09D523695D6057SOMETHING';
 userid
--------
(0 rows)

    [root@ip-xx-xxx-xx-xxx bin]# psql -U myuser -d mydatabase
    psql (9.2.4)
    Type "help" for help.

mydatabase=# SELECT USERID FROM MY_MAST_LOGINSESSION WHERE SESSIONID='5DFD5D1E09D523695D6057SOMETHING';
 userid
--------
(0 rows)

3.When I connected my remote database via jdbc from my application , it successfully connected , but it is taking too much time to execute the queries there.

3.当我通过jdbc从我的应用程序连接我的远程数据库时，它成功连接，但在那里执行查询需要太多时间。

Can you suggest any solution to find out this time delay ?

你能提出任何解决方案来找出这个时间延迟吗？

UPDATE :

更新 ：

During going deep into the problem , I found the delay happens only for specific queries such as DELETE, UPDATE. The queries such as INSERT,SELECTexecutes fine .

在深入研究问题的过程中，我发现延迟仅发生在特定查询中，例如DELETE, UPDATE。诸如INSERT, 之类的查询SELECT执行良好。

The specialty of DELETEand UPDATEqueries are which return nothing .

DELETE和 UPDATE查询的特长是什么都不返回。

So the actual problem is the querying client (suppose psql) is waiting for the Database server response , but for these queries server returns nothing . So the client keeps on waiting and after the timeout it throws exception .

所以实际的问题是查询客户端（假设psql）正在等待数据库服务器响应，但对于这些查询服务器什么都不返回。所以客户端一直在等待，超时后它会抛出异常。

But I was unable to find where to change to solve this problem.

但是我无法找到解决此问题的更改位置。

采纳答案by Sujith PS

The problem was with the synchronous_standby_namesparameter in postgresql.conf.

问题出synchronous_standby_names在postgresql.conf.

In my postgresql.conf, it was synchronous_standby_names = '*'.

在我的postgresql.conf，它是synchronous_standby_names = '*'。

When I commented out the line, I was able to execute all queries.

当我注释掉该行时，我能够执行所有查询。

From the PostgreSQL documentation:

从PostgreSQL 文档：

synchronous_standby_names (string): At any one time there will be at most one active synchronous standby; transactions waiting for commit will be allowed to proceed after this standby server confirms receipt of their data.

. . .

If no synchronous standby names are specified here, then synchronous replication is not enabled and transaction commits will not wait for replication.

synchronous_standby_names (string): 任何时候最多有一个活动的同步备用；在此备用服务器确认收到其数据后，将允许等待提交的事务继续进行。

. . .

如果此处未指定同步备用名称，则不会启用同步复制并且事务提交将不会等待复制。

So the actual problem was:
The querying client (suppose psql) was waiting for the Database server response, but for these queries the server returns nothing since synchronous replication was enabled. So the client kept on waiting and after the timeout it threw an exception.

所以实际的问题是：
查询客户端（假设 psql）正在等待数据库服务器响应，但对于这些查询，服务器没有返回任何内容，因为同步复制已启用。所以客户端继续等待，超时后抛出异常。