On Jul 5, 2007, at 1:34 PM, Nykolyn, Andrew wrote:
Is it possible to nest transactions within a stored procedure? I have a stored procedure that calls many other stored procedures and what happens it that after a certain amount of time the server runs out of shared memory. I know I can increase the PostgreSQL shared memory. However, that would be a temporary fix. I know it will eventually run out again as more data is processed. The right way to do it is to do issue a save point or commit at various places in my long stored procedure. I want to believe that there is a way to issue commits within a stored procedure since PostgreSQL now supports nested transactions.
PL/pgSQL functions implicitly run within a transaction, so I don't think you can issue BEGIN/COMMIT/ROLLBACK. But save points should be OK. Any reason that won't work for your case?
John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
