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

Question

I'm trying connecting to the Postgres database using the java web services (apache axis) with JDBC connections to get the data. But most of the time, 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 

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 also had checked the Postgres logs and I found the below 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 the client: No connection could be made because the target machine actively refused it.

4.Unexpected EOF on client connection. 

Answer 1

The actual issue is:

Caused by: java.net.SocketException: Connection reset

    at java.net.SocketInputStream.read

As far as I know, your connection is getting closed whenever java is trying to read the data from it. It can be caused by several reasons, some of them are listed below:

Either you have restarted the Postgresql server.

The PostgreSQL backend you were connected to, might have been terminated.

The PostgreSQL backend you were connected might have crashed.

It could also be because of a dodgy network connection.

Because of badly behaved stateful firewalls.

Idle connections being timed out of the NAT connection tables of NAT firewall/routers

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

Check out this SQL Tutorial for more information regarding the same.

Want to be a SQL expert? Come and join this SQL Certification course by Intellipaat.