Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

PostgreSQL Version 8's Transaction Savepoints Can Save Your Data : Page 2

While transactions are nice, PostgreSQL's current all-or-nothing transaction mechanism leaves much to be desired when an error occurs within the transaction code. Fortunately, the upcoming PostgreSQL version 8 addresses that problem by adding "savepoints," letting you roll back only part of a transaction and recover from errors gracefully.


advertisement

WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

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.



Joshua D. Drake is President of Command Prompt, Inc. a dedicated PostgreSQL support and custom programming company. He is also the co-author of "Practical PostgreSQL" from O'Reilly and Associates.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date