Transactions recovering failures in scripts
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.
comes to rescue, but you need to make it
ROLLBACK TO SAVEPOINT
when there is a failure and
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)