PostgreSQL例外:org.postgresql.util。PSQLException:在发送到后端时发生I/O错误。

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.

我得到了一个异常。PSQLException:在发送到后端时发生I/O错误。在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)

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 ,SELECT executes fine .

在深入研究这个问题的过程中，我发现延迟只针对特定的查询，如删除、更新。如INSERT,SELECT execute fine。

The specialty of DELETE and UPDATEqueries are which return nothing .

DELETE和UPDATEqueries的特性是什么都不返回。

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.

但我无法找到解决这个问题的方法。

1 个解决方案

#1

The problem was with the synchronous_standby_names parameter in postgresql.conf.

在postgresql.conf中，问题在于同步_standby_name参数。

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

在我的postgresql。conf，它是同步_standby_name = '*'。

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.

同步_standby_names(字符串):在任何时候，最多只会有一个主动同步备用;等待提交的事务将被允许在此备用服务器确认收到其数据之后进行。

. . .

。

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

如果这里没有指定同步备用名称，则不启用同步复制，事务提交将不等待复制。

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)正在等待数据库服务器响应，但是对于这些查询，服务器没有返回任何东西，因为启用了同步复制。因此，客户机继续等待，超时之后它抛出一个异常。

#1