Обсуждение: Looping through cursor row batches

Greetings,

I gather the following isn't possible (which would have been elegant
and ideal):

FOR rec IN
   FETCH 100000 FROM cursor
LOOP
   ...


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.

Cheers
Henry

"Henry Combrinck" <henry@zen.co.za> writes:
> 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.  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.

            regards, tom lane

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