ROLLBACK in a function

From: "Ken Winter" <ken(at)sunward(dot)org>
To: "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org>
Subject: ROLLBACK in a function
Date: 2010-05-23 18:51:36
Message-ID: C40884B22A0E4AAC8278E471EF0AE368@KenIBM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Grzegorz Jaśkiewicz 2010-05-23 20:29:53 Re: UPDATE ... RETURNING atomicity
Previous Message Lew 2010-05-23 17:15:40 Re: UPDATE ... RETURNING atomicity