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

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Jasen BettsDate: 2010-01-23 09:19:56
Subject: Re: Rollback with functions
Previous:From: A BDate: 2010-01-22 12:40:49
Subject: Rollback with functions

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