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