Re: Rollback with functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: A B <gentosaker(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Rollback with functions
Date: 2010-01-22 16:06:03
Message-ID: 1620.1264176363@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

A B <gentosaker(at)gmail(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jasen Betts 2010-01-23 09:19:56 Re: Rollback with functions
Previous Message A B 2010-01-22 12:40:49 Rollback with functions