Transactions recovering failures in scripts

Administrative Snippets

Transactions recovering failures in scripts

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

You may need to have a portion of a script that could fail or not, but you want the script to recover and keep the transaction going. Failure at that point could be acceptable.

This could be the case of a DDL statement like creating a table that could exists or not.

SAVEPOINT

comes to rescue, but you need to make it

ROLLBACK TO SAVEPOINT

when there is a failure and

RELEASE

when there is no failure.

A Practical Example

BEGIN;

SELECT 1;

SAVEPOINT sp;

CREATE TABLE t1 (
       id bigint,
       s text);

RELEASE sp;
SAVEPOINT sp;
ROLLBACK TO SAVEPOINT sp;
RELEASE sp;

SELECT * FROM t1;
COMMIT;

Run the above code as many times you want and it works!

The trick is that in case of failure

  RELEASE sp;
  SAVEPOINT sp;

from above snippet are simply ignored by the backend because the transaction is in ERROR state. While when there is no failure the savepoint is released and the following is executed:

SAVEPOINT sp;
ROLLBACK TO SAVEPOINT sp;
RELEASE sp;

Which as you can easily see does nothing since the subtransaction is opened and closed and sp is released right after.

Further info on subtransactions can be found here: PostgreSQL Documentation

--Mcm 00:00, 20 June 2010 (UTC)