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
