Re: Nested Transactions in PL/pgSQL

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