Savepoints to the Rescue
The upcoming PostgreSQL version 8 addresses the problem via savepoints. Savepoints are named placeholders that cause the database to save the current state at a specified point within a transaction, thus the term "savepoint." Specifically, if an error occurs within a transaction but after a savepoint, you can rollback the transaction to that savepoint without losing the work accomplished by statements that lie before the savepoint. Each savepoint has a name.
To initialize a savepoint you must be within a transaction block.
template1=# BEGIN;
BEGIN
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);
INSERT 17231 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);
INSERT 17232 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,0);
INSERT 17233 1
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
1 | 2 | 0
1 | 2 | 0
1 | 2 | 0
(3 rows)
template1=# SAVEPOINT main_values_inserted;
SAVEPOINT
template1=# INSERT INTO foo(column1,column2,column3) VALUES (1,2,1/0);
ERROR: division by zero
ERROR: division by zero
With the current PostgreSQL version, you'd lose all the
INSERT operations in the preceding code when the division by zero error occurs at the last
INSERT statement, but with version 8, you can rollback to a specific savepoint. Note that the code contains a savepoint named "
main_values_inserted." To rollback the transaction to that point, you can write:
template1=# ROLLBACK TO main_values_inserted;
ROLLBACK
By performing a rollback to that savepoint, you preserve all the work performed up to the savepoint (the first four
INSERT statements), losing only the work (in this case an error) executed after the
main_values_inserted savepoint.
After the rollback you can continue your work without restarting the entire transaction.
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17234 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17235 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17236 1
template1=# INSERT INTO foo(column1,column2,column3) VALUES (5,9,10);
INSERT 17237 1
template1=# SAVEPOINT secondary_values_inserted;
SAVEPOINT
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
1 | 2 | 0
1 | 2 | 0
1 | 2 | 0
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
(7 rows)
template1=# SAVEPOINT all_values_inserted;
SAVEPOINT
template1=# DELETE FROM foo;
DELETE 7
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
(0 rows)
Oops. Just as in the first example in this article, you actually meant to delete only rows where "column1 = 1". But now you can rollback to the second "
all_values_inserted" savepoint in the code above,
template1=# ROLLBACK TO all_values_inserted;
ROLLBACK
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
1 | 2 | 0
1 | 2 | 0
1 | 2 | 0
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
(7 rows)
Note that that restored the data. Now you can run the correct delete statement.
template1=# DELETE FROM foo WHERE column1 = 1;
DELETE 3
template1=# SELECT * FROM foo;
column1 | column2 | column3
---------+--------+--------
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
(4 rows)
Finally, you can commit the entire transaction. The final
SELECT statement shows that the integrity of the data after all these inserts and rollbacks is intact.
template1=# COMMIT;
COMMIT
template1=# select * from foo;
column1 | column2 | column3
---------+--------+--------
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
5 | 9 | 10
(4 rows)
The upcoming version of PostgreSQL, 8.0 has many new features such as savepoints, exceptions in plPgSQL, and "Point in Time" recovery that continue to make PostgreSQL the Open Source database of choice for serious developers.
Previous Page
