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