ne much-lauded feature of PostgreSQL is transactions. Transactions in a database help prevent accidental data loss or misrepresentation.
For example, let’s say you want to delete records from a table. In PostgreSQL the command is:
template1=# DELETE FROM foo; However, the above command will delete all of the records in the table. This is probably not what you want, and?unless you were using transactions?the only way to get the data back would be from a backup. Using transactions, getting the data back is simple. The command sequence would be:
BEGIN; DELETE FROM foo; DELETE 50The BEGIN statement causes the database to initiate a transaction, so as soon as you realize that the preceding command failed to include a WHERE clause, thus deleting the entire table, you could rollback the transaction.
BEGIN; DELETE FROM foo; DELETE 50 ROLLBACK; There is a drawback to using transactions in the current version of PostgreSQL. If any error occurs in the statements within your transaction, you must issue a rollback. A rollback is issued by executing the rollback command within the transaction and must be executed before any other commands will be processed by your specific connection. Once a rollback is executed you must begin the transaction again and restate the commands in a manner that will not cause an error. This rule includes both user errors, such as deleting all records in a table, and syntactical errors, such as trying to select from a table that does not exist. For example:
BEGIN; UPDATE foo SET bar = (SELECT count(*) FROM baz)); INSERT INTO foo (column1) SELECT column2 FROM bar; ERROR: relation "bar" does not exist CREATE TABLE bar (column1 text, column2 float); ERROR: current transaction is aborted, commands ignored until end of transaction blockBecause of the error, you will have to rollback and all your current work will be lost. This particular aspect of transactions with PostgreSQL is particularly irritating during testing and debugging.
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 zeroWith 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; ROLLBACKBy 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;COMMITtemplate1=# 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.
