Re: Rollback with functions

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.