Re: Nested Transactions in PL/pgSQL

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alvaro Herrera
Sent: Friday, July 06, 2007 9:49 AM
To: Nykolyn, Andrew
Cc: John DeSoi; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Nested Transactions in PL/pgSQL

Nykolyn, Andrew wrote:
> My real issue is not that I need subtransactions but I need to flush
> the buffer so that I can regain memory for my stored procedure to
> complete without getting a memory error.

Normal operation does not "fill buffers", thus they don't need any
flushing. There must be something weird about what your SP is doing.
Can we see it? Are you maybe opening and keeping half-scanned a lot of
cursors? If not, can you explain in much detail what it is doing?

Alvaro,

The stored procedures are long and complicated with recursive routines
and temporary tables and deal with a lot of data. What ended up
happening is that we added another very large chuck of data to the
processing and that gave us the memory error. After looking closer at
the error we upped the max_locks_per_transaction parameter to 256 and
then we were able to execute without an error. Thanks for your help.

Andy Nykolyn
Northrop Grumman