"Henry - Zen Search SA" <henry@zen.co.za> writes:
> One other thing: the docs mention that functions use cursors
> automatically to prevent OOM errors on large selects (as in my case).
> Well, the second part of my function does this:
> FOR rec in SELECT * FROM bigtable
> LOOP
> ...begin/insert/exception...
> END LOOP;
> and bang, OOM.
How soon is "bang"? The above causes one subtransaction to be
instantiated per loop execution, since we have to have a new XID
for each inserted row (else it's not possible to roll back just
that row on failure). The memory overhead per subtransaction is
not zero, though I think it's fairly small if you don't have any
triggers pending as a result of the insert. (Hm ... any foreign
keys on the table being inserted into?)
> This is in 8.3.1. I'll rewrite this to use cursors, but
> was hoping to take advantage of the implicit cursors to keep the code nice
> and simple... or am I misunderstanding "...FOR loops automatically use a
> cursor internally to avoid memory problems." from section 37.8 in the
> manual?
The FOR loop is not your problem.
regards, tom lane
