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

Unfortunately, I can't release the source for the function due to IP
concerns.  However considering the function runs in about 40 seconds
through the psql terminal, should it matter what the function does since
it should run in the same amount of time through JDBC?

Garrick


On Fri, 2003-06-27 at 14:02, Dave Cramer wrote:
> Garrick,
>
> Given that nextval('sequence') is a function and I do those all the
> time, I would probably need to see the function.
>
> Dave
> On Fri, 2003-06-27 at 14:58, Garrick Dasbach wrote:
> > 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
> >
> >
> --
> Dave Cramer <Dave@micro-automation.net>
>