Search Postgresql Archives

Re: Nested Transactions in PL/pgSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxx] 
Sent: Thursday, July 05, 2007 8:06 PM
To: John DeSoi
Cc: Nykolyn, Andrew; pgsql-general@xxxxxxxxxxxxxx
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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux