Re: executeQuery and busy waiting - Mailing list pgsql-jdbc

It's about as simple a JDBC program as you can get.  Source included
below, minus company info ofcourse.

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection("jdbc:postgresql://server_ip/db_name", "user", "password");
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select myFunction(2)");
            if(rs.next()){
                System.out.println("Function Execution Successfull.");
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try{    rs.close();    } catch (Exception e){}
            try{    stmt.close();    } catch (Exception e){}
            try{    conn.close();    } catch (Exception e){}
        }
    }

Garick Dasbach


On Fri, 2003-06-27 at 13:53, Dave Cramer wrote:
> Garrick,
>
> It would help if you posted a small program to replicate the problem.
>
> Dave
> On Fri, 2003-06-27 at 14:44, Garrick Dasbach wrote:
> > I'm working on a project with Postgresql and I'm running into a strange
> > problem.
> >
> > I have a Java Program running on the Database server that periodicly
> > connects to the Database and runs a pl/pgsql function.  This function
> > should run fairly fast, but could take several minutes based on the load
> > of the server and amount of information it needs to process.
> >
> > Running the function from psql takes 40 seconds under no load and
> > minimal data, but when I run the function from java using JDBC it takes
> > 20-30 minutes.
> >
> > Checking top, this is a Linux system, I see that the java program takes
> > up 99% of the CPU when it's running this function through executeQuery.
> > Is executeQuery() doing a busy wait for the data from postgres?  It
> > seems a bit absurd that the executeQuery method would hijack 99% of the
> > CPU waiting for results and slowing everything else on the system down.
> >
> > The second problem I am noticing is that if I move the java program to
> > another machine, to keep java from stealing all the CPU cycles, the
> > function still takes 20-30 minutes to run through java, but only takes
> > 40 seconds to run through psql.  What's the deal?
> >
> > Any help would be appreciated.
> >
> > Garrick Dasbach
> >
> > Software Developer
> > MusicRebellion.com, Inc.
> > Garrick@musicrebellion.com
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> >       joining column's datatypes do not match
> >
> --
> Dave Cramer <Dave@micro-automation.net>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org