Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux