Re: ROLLBACK in a function - Mailing list pgsql-general

On 24/05/10 02:51, Ken Winter wrote:
> How can I write a PL/PgSQL function that rolls back every database
> change it has done?

Have it raise an exception, causing the surrounding transaction to
terminate with an error.

Another function calling yours can still catch the exception and handle
it, so it's possible for your function's changes to be applied, but only
if someone explicitly and intentionally catches and ignores the error.

> BEGIN
>
> RAISE EXCEPTION '';
>
> EXCEPTION
>
> WHEN raise_exception THEN
>
> SET search_path TO public, tests;
>
> -- Test code:
>
> SELECT count(*) INTO old_count FROM person_i;
>
> INSERT INTO person (last_name) VALUES ('_test');
>
> SELECT count(*) INTO new_count FROM person_i;
>
> IF NOT new_count = old_count + 1 THEN
>
> errs := errs || ': Failed to insert into ''person''’;
>
> END IF;


> The function runs OK, but it does not roll back the actions it did (in
> this case, the INSERT).

You're on the right track, but you've got the code that changes the
database in the exception handler _after_ the exception is thrown.

You want it in the BEGIN block _before_ the exception is thrown.
However, you're also doing this quite an expensive way.

An INSERT/UPDATE/DELETE  can only fail to have any effect without
throwing an exception if if has a WHERE clause, is intercepted by a
BEFORE trigger that returns NULL, or is rewritten by a rule. If none of
those are possible, your INSERT/UPDATE/DELETE is guaranteed to work or
throw an exception.

eg (untested code, please verify before relying on it):

   BEGIN
     SELECT count(*) INTO old_count FROM person_i;
     INSERT INTO person (last_name) VALUES ('_test');
     SELECT count(*) INTO new_count FROM person_i;
     IF NOT old_count + 1 = new_count THEN
         RAISE EXCEPTION 'Update failed';
     END IF;
   EXCEPTION WHEN raise_exception THEN
     errs := errs || ': Failed to insert into ''person''’;
   END;


But ... please consider whether you really need this check with two
expensive count(*) queries. Unless you're using BEFORE triggers that
return NULL or rewrite rules, you can trust that an INSERT without a
WHERE clause will always succeed or throw an exception.

If you *are* using triggers or rules, you should be able to use GET
DIAGNOSTICS to see the affected row count, saving yourself those
count(*) queries. See:

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

eg (untested code, should give you the idea):

DECLARE
    rowcount INTEGER;
BEGIN
   -- function body here
   BEGIN
     INSERT INTO person (last_name) VALUES ('_test');
     GET DIAGNOSTICS rowcount = ROW_COUNT;
     IF NOT rowcount = 1 THEN
        RAISE EXCEPTION 'Update failed';
     END IF;
   EXCEPTION WHEN raise_exception THEN
     errs := errs || ': Failed to insert into ''person''’;
   END;
END;


You *can* trick Pg into not seeing that an update has happened even when
it really has. For example, a BEFORE trigger can do its own  INSERT then
return NULL to tell Pg to ignore the INSERT that invoked the trigger.
Stored functions that do INSERTs don't set the rowcount either. If
you're not doing anything like that, though, you're set.

--
Craig Ringer