ROLLBACK in a function

["Ken Winter" <ken@xxxxxxxxxxx>]

How can I write a PL/PgSQL function that rolls back every database change it has done?

 

I’m about to write a set of database test functions.  Each function needs to do some INSERT, UPDATE, or DELETE actions, test whether they had their intended effect, and then roll back the test changes to restore the database to its prior state.  Here’s a simple example:

 

CREATE OR REPLACE FUNCTION tests.person() RETURNS varchar AS

$BODY$

DECLARE

          errs VARCHAR = '';

          -- Test declarations:

          old_count BIGINT;

          new_count BIGINT;

BEGIN

--       RAISE EXCEPTION '';

-- EXCEPTION

--       WHEN raise_exception THEN

          SAVEPOINT s;

          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;

          ROLLBACK TO SAVEPOINT s;

          RETURN errs;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

;

When I run this function, I get this error:

 

ERROR: SPI_execute_plan failed executing query "SAVEPOINT s": SPI_ERROR_TRANSACTION

SQL state: XX000

Context: PL/pgSQL function "person" line 11 at SQL statement

 

I tried another way to make the rollback happen: The last paragraph of http://www.postgresql.org/docs/8.3/static/plpgsql-structure.html said “a block containing an EXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction.”  So I rewrote the test function like this:

 

CREATE OR REPLACE FUNCTION tests.person() RETURNS varchar AS

$BODY$

DECLARE

          errs VARCHAR = '';

          -- Test declarations:

          old_count BIGINT;

          new_count BIGINT;

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;

                   RETURN errs;

END;

$BODY$

LANGUAGE plpgsql VOLATILE

;

 

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

 

~ TIA

~ Ken