Multiple savepoints and rollback

1. Multiple savepoints and rollback

There might be times when you would like to use multiple savepoints in a transaction in such a way as to have a block of sql all succeed or fail as group. If that got your brain spinning because it sounded like I had previously talked about transactions... you might be thinking does that mean we are nesting transactions. While many database do support true nested transactions, PostgreSQL does not.

2. Multiple savepoints

It uses multiple savepoints to provide many of the same protections for which you might use nested transactions. Let's take a look at using multiple savepoints. In this example, we are going to update the inventory table to decrement the quanity of flour and sugar by 2 to make some Torun cookies. Then we are going to add 12 torun cookies to the baking_list table, and finally update the qty of torun cookies to be 12. It's important that each on of these steps succeed in order for the whole to succeed. By implementing a SAVEPOINT after each one, I could rollback to just after any successful step if I needed too, such as inventory_step or queuing_step.

3. Duplicating savepoint names

There is a super important cavaet to multiple save points. If you use the same name twice, Postgres will have the new one shadow the old one! This can really result in some odd behaviors if you are not expecting it. In general I encourage you to never reuse savepoint names as when under the pressure of debugging an issue related to them, it makes reasoning about the outcome of your code that much more complex.

4. Duplicate savepoint name example

For example, we've used the oops name twice here with the prior code. Now if I was to release oops. That will release the newest savepoint, just after the baking_list insert with that name; however, the old one will take it's place! now if I rollbacked back to oops, I would rollback all the way to just after the decrement to sugar's quantity. This could have a cause a significant data integrity issue in your database.

5. Let's practice!

Let's test out multiple savepoints.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.