Re: executeQuery and busy waiting

Garrick,

You are correct, it should run in the same time over the jdbc
connection.
Obviously there is a problem somewhere, but given that something like

select nextval(...) or select now() both call functions and return
quickly.

I am at a loss to explain the behaviour.

If I can't see it on my machine, how can I fix it?


Dave

On Fri, 2003-06-27 at 15:10, Garrick Dasbach wrote:
> 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@xxxxxxxxxxxxxxxxxx
> > > > > 
> > > > > 
> > > > > ---------------------------(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@xxxxxxxxxxxxxxxxxxxx>
> > > > 
> > > > 
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
> > > 
> > > 
> > -- 
> > Dave Cramer <Dave@xxxxxxxxxxxxxxxxxxxx>
> > 
> 
> 
-- 
Dave Cramer <Dave@xxxxxxxxxxxxxxxxxxxx>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend