Thread: idle processes - pg_recvbuf

Hello list,

I am rather new to JDBC programming with postgresql (making the switch
from PhP/MySQL).  I recently created a web application for learning
JDBC/postgresql for an online links database (how's that for reinventing
the wheel?)

Anyway, I am noticing some odd problems, every now and then I see the
following message on my server:

DEBUG:  pq_recvbuf: unexpected EOF on client connection

Searching google for this doesn't provide many answers.  Some posts on the
postgres lists, but no concrete answers.  Hopefully this time...

I also see an increasing number of idle connection when doing:

ps -deaf | grep postgres

At the moment I have 10 idle processes.

I am using postgresql version 7.2.2 and tomcat 4.1.12 with Java 1.4.1 on
SuSE 7.3.  My pgjdbc2.jar is dated Sept. 24th, no idea on the version
number.

I designed a database utilities class so I could have all my queries in
one spot.  I think this is the culprit, but I am not sure.  Here is part
of the code:

import blah blah blah;

public class DBUtils extends HttpServlet {
    private java.sql.Connection connection;
    private PrintWriter out;

    public DBUtils (HttpServletResponse response) {
        try {
            out = response.getWriter();

            Class.forName("org.postgresql.Driver").netInstance();
            connection = DriverManager.getConnection(
                 "jdbc:postgresql://localhost/mydb", "me", "mypass");

        } catch (SQLException sqle) and so on
    }

    public void finalize() throws java.lang.Throwable {
        try {
            connection.close();
        } catch (SQLException sqle) {
            out.println("Error closing connection" + sqle);
        }
    }

    public ArrayList getLinks () {
        // query db, return array
    }
}

It would seem that my finalize routine is being called most of the time,
but not all of the time.  Do other users have a better way of doing this?
I want to keep all my query stuff seperate from the presentation layer,
but I also want to close every connection cleanly.  This is coming from
normal use (e.g. I didn't kill the browser in the middle of a query or
something).

Thanks in advance,

Josh Trutwin
http://trutwins.homeip.net

Josh,

Couple of things:
1) you only need to load the driver once ie only do the Class.forName
once
2) you should close the connection in a finally

ie try{
    get connection
   }catch()
   {
     handle your error
   }
   finally
   {
     close connection
   }

3) now that being said, you should actually cache your connections

4) close the connections in the cache from the destroy() method in the
servlet

Dave
On Fri, 2002-11-08 at 13:43, Josh Trutwin wrote:
> Hello list,
>
> I am rather new to JDBC programming with postgresql (making the switch
> from PhP/MySQL).  I recently created a web application for learning
> JDBC/postgresql for an online links database (how's that for reinventing
> the wheel?)
>
> Anyway, I am noticing some odd problems, every now and then I see the
> following message on my server:
>
> DEBUG:  pq_recvbuf: unexpected EOF on client connection
>
> Searching google for this doesn't provide many answers.  Some posts on the
> postgres lists, but no concrete answers.  Hopefully this time...
>
> I also see an increasing number of idle connection when doing:
>
> ps -deaf | grep postgres
>
> At the moment I have 10 idle processes.
>
> I am using postgresql version 7.2.2 and tomcat 4.1.12 with Java 1.4.1 on
> SuSE 7.3.  My pgjdbc2.jar is dated Sept. 24th, no idea on the version
> number.
>
> I designed a database utilities class so I could have all my queries in
> one spot.  I think this is the culprit, but I am not sure.  Here is part
> of the code:
>
> import blah blah blah;
>
> public class DBUtils extends HttpServlet {
>     private java.sql.Connection connection;
>     private PrintWriter out;
>
>     public DBUtils (HttpServletResponse response) {
>         try {
>             out = response.getWriter();
>
>             Class.forName("org.postgresql.Driver").netInstance();
>             connection = DriverManager.getConnection(
>                  "jdbc:postgresql://localhost/mydb", "me", "mypass");
>
>         } catch (SQLException sqle) and so on
>     }
>
>     public void finalize() throws java.lang.Throwable {
>         try {
>             connection.close();
>         } catch (SQLException sqle) {
>             out.println("Error closing connection" + sqle);
>         }
>     }
>
>     public ArrayList getLinks () {
>         // query db, return array
>     }
> }
>
> It would seem that my finalize routine is being called most of the time,
> but not all of the time.  Do other users have a better way of doing this?
> I want to keep all my query stuff seperate from the presentation layer,
> but I also want to close every connection cleanly.  This is coming from
> normal use (e.g. I didn't kill the browser in the middle of a query or
> something).
>
> Thanks in advance,
>
> Josh Trutwin
> http://trutwins.homeip.net
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Dave Cramer <Dave@micro-automation.net>