Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

org.postgresql.util.PSQLException: The connection attempt failed

I am connecting to postgres data base using java web services (apache axis) with JDBC connections to get the data.But suddenly in most of times i am getting an exception of org.postgresql.util.PSQLException: The connection attempt failed. and some times it is working fine. Here I am using many prepared statements. My sample code is

Connection connection=null;
try
{
    Class.forName(driver);
    connection = DriverManager.getConnection(url, username, password);
    ps1=connection.prepareStatement("select * from emp");
    rs1=ps1.executeQuery();
    while(rs1.next())
    {            
        ps2=connection.prepareStatement("select * from dept where dept_no="+rs1.getInt("dept_no"));
        rs2=ps2.executeQuery();
        while(rs2.next())
        {
            ................
            ................
        }
    }
}
catch(Exception e)
{
    System.out.println("Exception occurred is"+e.getMessage());            
}
finally
{
    try{if(rs1!=null)rs1.close();}catch(Exception e){ System.out.println("*1closing error--->"+e);}
    try{if(ps1!=null)ps1.close();}catch(Exception e){ System.out.println("**1closing error--->"+e);}
    try{if(rs2!=null)rs2.close();}catch(Exception e){ System.out.println("*2closing error--->"+e);}
    try{if(ps2!=null)ps2.close();}catch(Exception e){ System.out.println("**2closing error--->"+e);}
    try{if(connection!=null)connection.close();}catch(Exception e){ System.out.println("***closing error--->"+e);}
}

stack trace about this exception is

org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:137)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:386)
    at org.postgresql.Driver.connect(Driver.java:260)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.codon.service.WareHouseServer.get_picks(WareHouseServer.java:7415)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
    at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
    at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
    at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
    at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
    at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
    at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
    at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
    at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
    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:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(Unknown Source)
    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.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:254)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:95)
    ... 37 more

I checked the postgres logs and i found the following statements in different cases

1.WARNING: worker took too long to start; cancelled.

2.could not reattach to shared memory (key=...., addr=.....): 487.

3.could not receive data from client: No connection could be made because the target machine actively refused it.

4.unexpected EOF on client connection.

please help me.Thanks In advance

like image 493
Sasi Vara Prasad Avatar asked Apr 04 '13 08:04

Sasi Vara Prasad


3 Answers

The real problem is:

Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read

The connection was closed when Java tried to read from it. This can be caused by:

  • The PostgreSQL server being restarted
  • The PostgreSQL backend you were connected to being terminated
  • The PostgreSQL backend you were connected to crashing
  • Dodgy network connection
  • Badly behaved stateful firewalls
  • Idle connections timing out of the NAT connection tables of NAT firewall/routers

... and probably more. Check the PostgreSQL server logs to see if there's anything informative there; also consider doing some network tracing with a tool like Wireshark.

like image 127
Craig Ringer Avatar answered Oct 19 '22 01:10

Craig Ringer


Try chekin Postgresql version, in my case I deployed my app with:

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.16</version>
    </dependency>

But in local I used 42.2.18 and worked fine, so I din't know after checking version, so you should try another versions maybe.

like image 38
David Huertas Avatar answered Oct 19 '22 02:10

David Huertas


Fixed for me:

hop onto the pg_hba.conf file and under IPV4 connection change its 127.xxxx to the 0.0.0.0/0

double check: hop onto the postgresql.conf and change the listenaddresses to * (im sure you know that you need to delete the starting # first in order to make it work)

Good luck!

like image 1
Aramis NSR Avatar answered Oct 19 '22 00:10

Aramis NSR