Re: Memory exeception

Hi Barry

Looks good! I thought cursors are only available in embedded SQL. So this approach
seems to work for me.
Thanks for your help.

Peter

Barry Lind wrote:

> Peter,
>
> You certainly can do this with java. I do in my code in many places.
> Consider the following example that processes all rows in the table
> 'bar' by fetching 10 rows at a time.
>
> thanks,
> --Barry
>
> PreparedStatement l_cursorstmt = connection.PrepareStatement("declare
> foo_cursor cursor for select foo from bar");
> PreparedStatement l_fetchstmt = connection.PrepareStatement("fetch
> forward 10 from foo_cursor");
>
> //open the cursor
> l_cursorstmt.execute();
>
> ResultSet l_rset;
> int l_count;
> do {
> //continue looping until you get no rows returned in a fetch
> l_count = 0;
> //fetch rows from the cursor using the fetch statement
> l_rset = l_fetchstmt.executeQuery();
> while (l_rset.next) {
> l_count++;
> //process the rows here
> }
> } while (l_count > 0);
>
> //don't forget to close the cursor
> PreparedStatement l_closestmt = connection.PrepareStatement("close
> foo_cursor");
> l_closestmt.execute();
>
> Peter Wasem wrote:
>
> > Hi Barry
> >
> > This is exactly what I observed. The physical memory dropped down to almost
> > zero (but no swap was used) before the exception was thrown. I'm not sure if I
> > can use cursors (other than ResultSet) since my application is Java-based.
> >
> > Thanks
> >
> > Barry Lind wrote:
> >
> >
> >>Peter,
> >>
> >>Postgres will return the entire result into memory first before
> >>processing the first row of data. If you don't want this behavior you
> >>should use explicit cursors and fetch statements to get data a few rows
> >>at a time. See the postgres documentation on the 'cursor' and 'fetch'
> >>sql statements.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Peter Wasem wrote:
> >>
> >>
> >>>Hi
> >>>
> >>>I have a problem in processing large numbers of rows in ResultSets.
> >>>Here's the code fragment where the problem occurs:
> >>>
> >>>...
> >>> ResultSet rset = stmt.executeQuery( "select ... from ... where ...
> >>>order by ..." ) ;
> >>> while(rset.next() )
> >>> { // Process the row }
> >>>...
> >>>
> >>>The query addresses some 100'000 rows. When stmt.executeQuery() is
> >>>executed suddenly an exception occurs.
> >>>The same program works fine with other JDBC drivers.
> >>>
> >>>Any ideas ?
> >>>
> >>>Greetings
> >>>Peter
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 2: you can get off all lists at once with the unregister command
> >>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >>>
> >>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html