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(at)postgresql(dot)org)
>>>
>>>