Re: Memory exeception

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@postgresql.org)
>>>
>>>