"Henry - Zen Search SA" <henry@xxxxxxxxx> 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
