Обсуждение: Rollback with functions

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?

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...

A B <gentosaker@gmail.com> writes:
> 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)?

Yes, it rolls back to the start of the transaction, which in this case
is outside the function altogether.

> 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?

When control gets to the "clean up code", everything that happened
inside the begin-block is already rolled back.  (An exception is that
the local variables of the function still have the values they had
when the error was thrown.  But effects out in the database have been
undone.)

            regards, tom lane

On 2010-01-22, A B <gentosaker@gmail.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.