Thread: transactions in plpgsql
I'm building some functions in plpgsql, and I got to a point where I thought:
How are transactions used in the function? Is it posible to rollback the
modifications that were made to the tables at some point in the plpgsql
function?
--
09:58:01 up 10 days, 14:29, 4 users, load average: 0.11, 0.31, 0.32
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
On Friday 19 March 2004 13:01, Martin Marques wrote: > I'm building some functions in plpgsql, and I got to a point where I > thought: How are transactions used in the function? Is it posible to > rollback the modifications that were made to the tables at some point in > the plpgsql function? You can abort the entire transaction by raising an exception. Nothing more sophisticated is available at the moment. There might well be nested transactions in 7.5 -- Richard Huxton Archonet Ltd
El Vie 19 Mar 2004 12:49, Richard Huxton escribió:
> On Friday 19 March 2004 13:01, Martin Marques wrote:
> > I'm building some functions in plpgsql, and I got to a point where I
> > thought: How are transactions used in the function? Is it posible to
> > rollback the modifications that were made to the tables at some point in
> > the plpgsql function?
>
> You can abort the entire transaction by raising an exception. Nothing more
> sophisticated is available at the moment.
OK, this would mean that the whole function is in a transaction. But what if I
call another function from within this one and it that function gets an error
which is RAISED? Can I do something about it on the original function, or do
I have to let it all rollback?
--
09:19:02 up 11 days, 13:50, 2 users, load average: 1.13, 0.88, 0.38
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
> El Vie 19 Mar 2004 12:49, Richard Huxton escribió: > >>On Friday 19 March 2004 13:01, Martin Marques wrote: >> >>>I'm building some functions in plpgsql, and I got to a point where I >>>thought: How are transactions used in the function? Is it posible to >>>rollback the modifications that were made to the tables at some point in >>>the plpgsql function? >> >>You can abort the entire transaction by raising an exception. Nothing more >>sophisticated is available at the moment. > > OK, this would mean that the whole function is in a transaction. But what if I > call another function from within this one and it that function gets an error > which is RAISED? Can I do something about it on the original function, or do > I have to let it all rollback? Until nested transactions are in place (7.5 maybe?), it all has to rollback. Greg
Joe Conway has recently created a dblink patch, which allows the use of
local dblink calls to simulate nested transactions. It worked nicely for
my purposes.
Oleg
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, March 19, 2004 8:49 AM
To: Martin Marques; pgsql-general@postgresql.org
Subject: Re: [GENERAL] transactions in plpgsql
On Friday 19 March 2004 13:01, Martin Marques wrote:
> I'm building some functions in plpgsql, and I got to a point where I
> thought: How are transactions used in the function? Is it posible to
> rollback the modifications that were made to the tables at some point
> in the plpgsql function?
You can abort the entire transaction by raising an exception. Nothing
more
sophisticated is available at the moment.
There might well be nested transactions in 7.5
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
*************************************
This e-mail may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.
*************************************