1. Rolling back to a savepoint
Rolling back a whole transaction is pretty useful, but it would be awesome if we could roll back to specific spots in our transaction. Thankfully much like in a video game, PostgreSQL offers savepoints. A savepoint is a distinctive point inside a transaction that allows all commands that after it to be rolled back, restoring the transaction's state to that point. Let's take a look at an example.
2. Without savepoints
Here we are updating the cost of Linga cookies to $2.33 and Macaron cookies to $5.00. We've made a mistake in the statement to update Macaron cookies by leaving out the decimal point. We could rollback the whole transaction.
3. Using Savepoints and targeted rollbacks
However, if we use a save point, we would only need to add one change between the two statements as shown here with the keyword SAVEPOINT and the name 'oops'.
Now when we want to roll back, we can use ROLLBACK TO SAVEPOINT oops; to return to the transaction state at that time. We call these roll backs to a savepoint a targeted rollback
4. Releasing a savepoint when done with it
We can have any number of statements before and after the savepoint and it will dutifully maintain it's state at that time. It's worth noting that you don't want to leave savepoints open for a large number of statements for performance reasons. So if you aren't ready to commit but everything is fine and your no longer need the savepoint, there is also RELEASE SAVEPOINT 'oops'. This will basically remove the savepoint.
5. Two critical things about rollbacks and savepoints
There are two important things to keep in mind as you are working with savepoints. ROLLBACK without a TO will still rollback the whole transactions. Additionally, rolling back to a savepoint name that does not exist will throw an error. So make sure to be very intensional when using rollback with savepoints.
6. Let's practice!
Let's go leverage some savepoints and targeted rollbacks