Thread: BUG #2060: Issue with Data base connection

BUG #2060: Issue with Data base connection

From

"Manideep Kalisetty"

Date:

21 November 2005, 13:30:02

The following bug has been logged online:

Bug reference:      2060
Logged by:          Manideep Kalisetty
Email address:      mani7deep@yahoo.com
PostgreSQL version: 7.2.2
Operating system:   Windows and Linux
Description:        Issue with Data base connection
Details:

I am using postgresql-8.1dev-402.jdbc3.jar JDBC driver and websphere
application server. Even though I am successfully closing connections, the
error message I am receivng is "Backend start-up failed: FATAL 1:  Sorry,
too many clients already". Please refer to the Exception report below:


[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R
org.postgresql.util.PSQLException: Backend start-up failed: FATAL 1:  Sorry,
too many clients already
.
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.core.v2.ConnectionFactoryImpl.readStartupMessages(ConnectionF
actoryImpl.java(Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.core.v2.ConnectionFactoryImpl.openConnectionImpl(ConnectionFa
ctoryImpl.java(Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java(
Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.
java(Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.
java(Inlined Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java(Inlined
Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.Driver.makeConnection(Driver.java(Inlined Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
org.postgresql.Driver.connect(Driver.java(Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
java.sql.DriverManager.getConnection(DriverManager.java(Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
java.sql.DriverManager.getConnection(DriverManager.java(Inlined Compiled
Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
kanbay.dao.DBAccess.getConnection(DBAccess.java(Compiled Code))
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
kanbay.dao.ContributorsDAO.getContributorsOfTheMonth(ContributorsDAO.java:11
9)
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
kanbay.com.GenericServlet.doPost(GenericServlet.java:124)
[11/21/05 10:28:54:836 IST] 50b9631a SystemErr     R     at
kanbay.com.GenericServlet.doGet(GenericServlet.java:48)
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
javax.servlet.http.HttpServlet.service(HttpServlet.java(Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServle
tInstance.java(Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecy
cleServlet.java(Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServ
let.java:313)
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecyc
leServlet.java(Inlined Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java
(Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidSer
vletReferenceState.java(Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletIns
tanceReference.java(Inlined Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(
WebAppRequestDispatcher.java(Compiled Code))
[11/21/05 10:28:54:846 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppReques
tDispatcher.java(Compiled Code))
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequest
Dispatcher.java:200)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker
.java:276)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(C
achedInvocation.java:71)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(C
acheableInvocationContext.java:116)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletReq
uestProcessor.java:186)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListene
r.java:334)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.jav
a:56)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)

[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.http.HttpConnection.run(HttpConnection.java:443)
[11/21/05 10:28:54:867 IST] 50b9631a SystemErr     R     at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:672)

Re: BUG #2060: Issue with Data base connection

From

Kris Jurka

Date:

21 November 2005, 23:39:48

On Mon, 21 Nov 2005, Manideep Kalisetty wrote:

> Bug reference:      2060
> Email address:      mani7deep@yahoo.com
> PostgreSQL version: 7.2.2
> Description:        Issue with Data base connection
> Details:
>
> I am using postgresql-8.1dev-402.jdbc3.jar JDBC driver and websphere
> application server. Even though I am successfully closing connections, the
> error message I am receivng is "Backend start-up failed: FATAL 1:  Sorry,
> too many clients already". Please refer to the Exception report below:
>

Well, something is leaving connections open.  Checking the results of
SELECT * FROM pg_stat_activity; will tell you what database and username
they are connected to which may help.  While this code doesn't appear to
be using a connection pool, perhaps you have another application that is
using a pool that is configured to open too many connections?  Still, the
most likely cause is a coding error failing to close connections.

Kris Jurka

Re: BUG #2060: Issue with Data base connection

From

Kris Jurka

Date:

23 November 2005, 18:44:07

kalisetty manideep wrote:
> [Backend reports "too many open connections"]
 >
> But I am 100% sure that its not the issue with the
> code.
>
> Do you know any Postgresql - JDBC driver, which is not
> from Postgresql development group. I think JDBC driver
> is not closing the connection even though I am closing
> the connection.

I have no reason to believe that and you certainly haven't shown that.

> Please refer to the DBACCESS file attached.
>

All this shows is the Connection being opened, not closed.

I've implemented an idea I got from rupa in #postgresql a while ago
about a way of logging leaked connections.  I've added some code to the
Connection's finalize() method that prints some logging information if
the Connection was not closed.  This is enabled by using the new
logUnclosedConnections URL parameter.

See the attached code for an example.  I've put up a new jar that
contains this here:  http://www.ejurka.com/pgsql/jars/km
import java.sql.*;

public class OpenConn {

    public static void main(String args[]) throws Exception {
        Class.forName("org.postgresql.Driver");
        leakConnection();
        System.gc();
    }

    private static void leakConnection() throws Exception {
        Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost:5810/jurka?logUnclosedConnections=true","jurka","");
    }

}

Re: [JDBC] BUG #2060: Issue with Data base connection

From

Dave Cramer

Date:

24 November 2005, 00:47:00

It's interesting to note that *everyone* else using the JDBC driver
doesn't have this problem ?

Dave
On 23-Nov-05, at 2:44 PM, Kris Jurka wrote:

> kalisetty manideep wrote:
>> [Backend reports "too many open connections"]
> >
>> But I am 100% sure that its not the issue with the
>> code.
>> Do you know any Postgresql - JDBC driver, which is not
>> from Postgresql development group. I think JDBC driver
>> is not closing the connection even though I am closing
>> the connection.
>
> I have no reason to believe that and you certainly haven't shown that.
>
>> Please refer to the DBACCESS file attached.
>
> All this shows is the Connection being opened, not closed.
>
> I've implemented an idea I got from rupa in #postgresql a while ago
> about a way of logging leaked connections.  I've added some code to
> the Connection's finalize() method that prints some logging
> information if the Connection was not closed.  This is enabled by
> using the new logUnclosedConnections URL parameter.
>
> See the attached code for an example.  I've put up a new jar that
> contains this here:  http://www.ejurka.com/pgsql/jars/km
> import java.sql.*;
>
> public class OpenConn {
>
>     public static void main(String args[]) throws Exception {
>         Class.forName("org.postgresql.Driver");
>         leakConnection();
>         System.gc();
>     }
>
>     private static void leakConnection() throws Exception {
>         Connection conn = DriverManager.getConnection("jdbc:postgresql://
> localhost:5810/jurka?logUnclosedConnections=true","jurka","");
>     }
>
> }
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend