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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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