Обсуждение: Nested Transactions in PL/pgSQL
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.
Any help or work around would be appreciated. Thanks.
Andy Nykolyn
Northrop Grumman
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
John DeSoi wrote:
>
> 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?
It can't be done directly. The only way to have a subtransaction in a
PL/pgSQL function is to use an EXCEPTION clause.
You can nest it -- for example have one BEGIN/EXCEPTION/END block inside
another. You can do it serially as well, along the lines of
BEGIN
BEGIN
do stuff
EXCEPTION WHEN ...
catch it
END
BEGIN
do more stuff
EXCEPTION WHEN ...
same
END
END
Note that BEGIN here delimits a block; it has no relationship at all
with BEGIN in SQL which starts a transaction.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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.
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Thursday, July 05, 2007 8:06 PM
To: John DeSoi
Cc: Nykolyn, Andrew; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Nested Transactions in PL/pgSQL
John DeSoi wrote:
>
> 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?
It can't be done directly. The only way to have a subtransaction in a
PL/pgSQL function is to use an EXCEPTION clause.
You can nest it -- for example have one BEGIN/EXCEPTION/END block inside
another. You can do it serially as well, along the lines of
BEGIN
BEGIN
do stuff
EXCEPTION WHEN ...
catch it
END
BEGIN
do more stuff
EXCEPTION WHEN ...
same
END
END
Note that BEGIN here delimits a block; it has no relationship at all
with BEGIN in SQL which starts a transaction.
--
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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. Please don't top-post. 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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alvaro Herrera Sent: Friday, July 06, 2007 9:49 AM To: Nykolyn, Andrew Cc: John DeSoi; pgsql-general@postgresql.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