>> 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
