Re: [PL/pgSQL] Commit every N rows. Is it possible ?

Jeremiasz Miedzinski wrote:
> 2006/11/9, Richard Huxton <dev@archonet.com>:
>
>> It's not clear to me why your function does what it does anyway. I can't
>> see why you wouldn't just do this as standard queries.
>
> As it was mentioned on http://orafaq.com/faqplsql.htm
>
> Contrary to popular believe, one should *COMMIT less frequently* within a
> PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the
> frequency of commit, the sooner the extents in the rollback segments
> will be
> cleared for new transactions, causing ORA-1555 errors.
>
> So, I understand that if function/procedure in postgreSQL is treated as one
> transaction I can for example execute 15000 delete queries and nothing
> similar to ORA-1555 shouldn't happen.

I don't believe we have ORA-1555 errors in PG. We don't have the
resources to implement all of Oracle's failure modes :-)

Two areas where you might want to keep an eye on resource usage though:
1. Lots of savepoints (exception handling in plpgsql)
2. Returning large result sets (where the function will assemble the
entire set before returning it). Consider returning a cursor if you want
millions of rows.


--
   Richard Huxton
   Archonet Ltd