Skip site navigation (1) Skip section navigation (2)

Re: Rollback with functions

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Rollback with functions
Date: 2010-01-23 09:19:56
Message-ID: hjeevs$ikv$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
On 2010-01-22, A B <gentosaker(at)gmail(dot)com> wrote:
> Greetings!
>
> I have yet not fully understood the magic with transactions in
> combination with plpgsql functions.
>
> Assume I have a function
>
> create function foo()
> begin
>        do stuff
>        ....
>        ....  <--- here it fails
>        ....
> end
>
> and a call to the function fails (as indicated in the code), will
> everything that  has been done inside the function be automatically
> undone (rollbacked)?
>
> If I had added code for exceptions, like this
>
> create function foo()
> begin
>    begin
>        do stuff
>        ....
>        ....
>        ....
>    exception when others then
>        ....  <--- clean up code
>    end;
> end
>
> Then I would need the "clean up code", or else there would be some
> changes in the database caused by all the commands that were actually
> run before the failure, right?

some changes are immune to rollback (getting nextval from sequences
being one such change) all other changes are undone when the function
fails.

> So is there then any other reason besides having a way to tell exactly
> what went wrong (which I understand is a good thing ), to have the
> "clean up code"? (you might of course need to have more exception
> statements and add exceptions to the clean up code, and so on...

If I want a function to do something when it encounters an error
(instead of doing nothing but raising an exception), that is when I
use exceptions.




In response to

pgsql-novice by date

Next:From: Mike EllsworthDate: 2010-01-23 18:03:34
Subject: 8.5 beta manual
Previous:From: Tom LaneDate: 2010-01-22 16:06:03
Subject: Re: Rollback with functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group