Re: dblink() cursor error/issue (TopMemoryContext)

"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