Re: executeQuery and busy waiting

Well, we found the problem.  Using a Packet Sniffer we found that
Postgres was sending Thousands of 8K messages back to our JDBC test
Program.  Inside these packets were the text from the 'NOTICE' commands
we were using to debug our function.  We didn't realize that Postgres
sent these messages back.  Thus our program was constantly recieving
information, and Postgres could only continue processing the transaction
when it's output buffer had been cleared.

Now that we know what's going on, is there any way to tap into these
NOTICE messages from the server?  Maybe to use as way to track a
function's execution/progress from JDBC.

Thanks for your patience.

Garrick

On Fri, 2003-06-27 at 14:21, Dave Cramer wrote:
> 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@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>
> > >
> >
> >
> --
> Dave Cramer <Dave@micro-automation.net>
>