Thread: ROLLBACK in a function
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
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
Craig ~
Your rollback solution works! For anyone who wants to use Craig's solution,
the new version of my example (with the test code omitted to clarify the
structural pattern) is:
CREATE OR REPLACE FUNCTION tests() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations: <omitted>
BEGIN
-- Test code: <omitted>
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
By contrast, the old version (with the test code omitted) was:
CREATE OR REPLACE FUNCTION tests() RETURNS varchar AS
$BODY$
DECLARE
errs VARCHAR = '';
-- Test declarations: <omitted>
BEGIN
RAISE EXCEPTION '';
EXCEPTION
WHEN raise_exception THEN
-- Test code: <omitted>
RETURN errs;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
To clarify the purpose of this structure, for anyone who may want to do the
same thing:
This is a framework for running test code whose job is to run one or more
tests against the database. Every test that fails is supposed to simply add
a diagnostic message to the "errs" string, which is then returned. Every
test that is passed is to do nothing. All tests need to be designed so that
they do not cause an exception that causes the function to abort. And then,
at the end, the framework of the function returns errs and rolls back all
data changes it has made.
~ Thanks!
~ Ken
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Craig Ringer
> Sent: Sunday, May 23, 2010 8:48 PM
> To: Ken Winter
> Cc: PostgreSQL pg-general List
> Subject: Re: [GENERAL] ROLLBACK in a function
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> St