Re: Looping through cursor row batches

>> Anyone know the most efficient way of FETCHing a batch of rows, and
>> looping >> through them in a function? FETCHing a record at a
>> time will work, but I >> was wondering whether this could be done.
>
> You're outsmarting yourself.

:-) One can only try.

> plpgsql already does the equivalent of
> this under the hood, there is no need for you to try to make it happen
> at user level. Just use a plain ol' FOR rec IN SELECT and forget the
> explicit cursor.

I'm aware of the implicit cursor use in functions, but recall that (pg8.3.3)

(#1)
FOR rec IN SELECT col from dblink_fetch('cursor'..) DO

is running out of memory (see discussion
http://archives.postgresql.org/pgsql-general/2008-06/msg00031.php) due
to an exception block inside the loop (which is possibly leaking
memory - I tried to reduce it to a concise failing case, still trying).

I'm pre-emptively expecting (pessimistically, I know) an OOM error again with:

(#2)
FOR rec IN SELECT col FROM really_huge_table DO
exception block...
END LOOP;

Anyway, I've found that fetching a batch of (say) 100,000 (instead of
10,000) at a time I reduce the likelihood of running out of memory (a
process which does exactly this has been running for the past day or
two; time will tell). I was pondering whether it's possible to do
what I mentioned in my original post - ie, an explicit cursor as the
source object in a FOR loop so I can have a bit more control over how
many rows are fetched each time (instead of 1 at a time).

So, setting aside my self-outsmartiness, is there a way to achieve this?

Regards
Henry