Jeremiasz Miedzinski wrote:
2006/11/9, Richard Huxton <dev@xxxxxxxxxxxx>:
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