sql / expert
Snippet
Granular Error Recovery with Transactional Savepoints
Savepoints allow you to roll back a specific portion of a transaction without aborting the entire sequence. This is critical for complex workflows where some steps are optional or require specific error handling.
snippet.sql
1
2
3
4
5
6
7
8
9
BEGIN;INSERT INTO audit_log (event) VALUES ('Complex Update Started');SAVEPOINT post_audit;UPDATE account_balances SET amount = amount - 500 WHERE id = 1;-- If this fails, we only roll back to the savepoint-- ROLLBACK TO SAVEPOINT post_audit;COMMIT;
Breakdown
1
SAVEPOINT post_audit;
Marks a point in the transaction to which we can later return if an error occurs.
2
ROLLBACK TO SAVEPOINT post_audit;
Reverts all changes made after the savepoint while keeping the preceding INSERT active.