SQL 70 🛢️ Savepoints in Transactions
A savepoint is a named marker inside a transaction. It is set with the SAVEPOINT statement, and it records the state of the transaction at that point. When a later statement fails, the ROLLBACK TO SAVEPOINT returns the transaction to the marker, undoing the changes that came after it while keeping the changes that came before. The transaction stays open and usable. Without the savepoint, a failed statement in PostgreSQL aborts the entire transaction, and the only way out is the ROLLBACK, which undoes everything. The savepoint is the mechanism that turns the all-or-nothing transaction into a transaction with the internal recovery points.
The savepoint solves a specific problem: the partial rollback. A long transaction has several logical steps, and the failure of one step should not undo the steps that already succeeded. An order processing transaction inserts the order, inserts the items, decrements the inventory, and charges the payment. If the payment fails, the order and the items should be rolled back, but the transaction may have other work that should be kept. The savepoint before the payment block allows the failure to be isolated: the payment block is rolled back, and the transaction continues. The savepoint is the boundary of the recovery.
The savepoint has the costs and the rules. The database must track the state at the savepoint, which consumes the resources, and the excessive savepoints can slow the transaction. The RELEASE SAVEPOINT removes the savepoint and the resources it consumed. The savepoints are nested, and the ROLLBACK TO SAVEPOINT destroys the savepoints created after it. The savepoint outside a transaction is an error, and the savepoint inside an aborted transaction is the recovery mechanism. The rules are simple, but the interactions with the aborted state and the nested savepoints are where the subtlety lives.
This chapter covers three areas. First, why savepoints exist — the partial-rollback problem and the aborted-transaction problem. Second, the savepoint commands — the SAVEPOINT, the ROLLBACK TO SAVEPOINT, the RELEASE SAVEPOINT, and the nested savepoints. Third, the practical patterns — the error recovery, the retry within the transaction, the batch processing, and the best practices. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the savepoint mechanics.
Key point: A savepoint is a named marker inside a transaction. The SAVEPOINT name sets the marker, the ROLLBACK TO SAVEPOINT name returns to it, and the RELEASE SAVEPOINT name removes it. In PostgreSQL, a failed statement aborts the whole transaction, and the ROLLBACK TO SAVEPOINT is the recovery that keeps the transaction usable. The savepoints are nested, and the ROLLBACK TO SAVEPOINT destroys the savepoints created after the target.
Why savepoints exist
The partial-rollback problem. A transaction is all-or-nothing: the COMMIT writes all the changes, and the ROLLBACK undoes all of them. But a long transaction may have several logical steps, and the failure of one step should not undo the whole transaction. The order processing transaction inserts the order, inserts the items, and charges the payment. If the payment fails, the order and the items should be rolled back, but the transaction may have the unrelated work that should be kept. The savepoint before the payment block isolates the failure: the payment block is rolled back to the savepoint, and the earlier work is preserved.
The aborted-transaction problem. In PostgreSQL, a failed statement aborts the whole transaction. The subsequent statements are rejected with the “current transaction is aborted, commands ignored until end of transaction block” error, and the only way out is the ROLLBACK. The savepoint changes this: the ROLLBACK TO SAVEPOINT returns the transaction to the usable state, and the subsequent statements can run. The savepoint is the escape hatch from the aborted state, and it is the reason the expected errors can be handled without aborting the transaction.
The error-isolation problem. A transaction may perform several operations, and some of them may fail for the expected reasons: the duplicate key, the foreign key violation, the check constraint. The savepoint before the risky operation isolates the failure: the operation is rolled back to the savepoint, and the transaction handles the error and continues. The pattern is the “try the operation, and if it fails, handle the failure” within the transaction.
The batch-processing problem. A batch process handles many items, and the failure of one item should not undo the whole batch. The savepoint before each item allows the failed item to be rolled back while the successful items are kept. The pattern is the loop that sets the savepoint before each item, processes the item, and rolls back to the savepoint on the failure.
The nested-step problem. A transaction has the steps that are themselves composed of the sub-steps. The sub-step failure should undo the sub-step but keep the parent step. The nested savepoints provide the hierarchy: the outer savepoint for the parent step, and the inner savepoints for the sub-steps. The ROLLBACK TO SAVEPOINT at the outer level undoes the whole parent step, and at the inner level undoes only the sub-step.
The trade-off. The savepoint has the cost. The database tracks the state at the savepoint, and the ROLLBACK TO SAVEPOINT undoes the changes since the point. The excessive savepoints consume the resources and slow the transaction. The savepoint also does not release the locks acquired since the savepoint; the locks are held until the transaction ends. The trade-off is between the granularity of the recovery and the cost of the tracking.
a. The SAVEPOINT statement
The SAVEPOINT sets a named marker inside the transaction. The name must be unique within the transaction, and the savepoint must be inside a transaction block.
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted.
COMMIT;The after_order savepoint marks the state after the order is inserted. The failed order_items insert is rolled back to the savepoint, and the order is preserved. The transaction continues, and the COMMIT writes the order.
The SAVEPOINT outside a transaction is an error:
SAVEPOINT sp1;
-- ERROR: SAVEPOINT can only be used in transaction blocksThe error is the reminder that the savepoint is part of the transaction, not the session.
The savepoint name can be reused after the RELEASE or the ROLLBACK TO SAVEPOINT, because the old savepoint is destroyed:
BEGIN;
SAVEPOINT sp1;
-- the statements
RELEASE SAVEPOINT sp1;
SAVEPOINT sp1; -- the same name is allowed
COMMIT;b. The ROLLBACK TO SAVEPOINT
The ROLLBACK TO SAVEPOINT returns the transaction to the marker, undoing the changes since the point. The transaction stays open and usable.
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted, the order_items is undone.
-- The transaction is usable again.
COMMIT;The ROLLBACK TO SAVEPOINT undoes the data changes since the savepoint. The locks acquired since the savepoint are not released; they are held until the transaction ends. The savepoint itself is not destroyed; the transaction is still positioned at the savepoint, and the ROLLBACK TO SAVEPOINT can be repeated.
The nested savepoints. The savepoints can be nested, and the ROLLBACK TO SAVEPOINT destroys the savepoints created after the target.
BEGIN;
SAVEPOINT sp1;
INSERT INTO accounts VALUES (1, 'Alice', 100);
SAVEPOINT sp2;
INSERT INTO accounts VALUES (2, 'Bob', 200);
SAVEPOINT sp3;
INSERT INTO accounts VALUES (3, 'Carol', 300);
ROLLBACK TO SAVEPOINT sp1;
-- The sp2 and the sp3 are destroyed.
-- The Bob and the Carol inserts are undone.
-- The Alice insert is preserved.
-- The sp1 is the current savepoint.
COMMIT;The ROLLBACK TO SAVEPOINT sp1 undoes the changes since the sp1 and destroys the sp2 and the sp3. The sp1 remains, and the transaction is positioned at it. The subsequent ROLLBACK TO SAVEPOINT sp1 would be an error because the sp1 is the current savepoint.
The error after the rollback. The ROLLBACK TO SAVEPOINT clears the aborted state. After the rollback, the transaction is usable, and the subsequent statements can run.
BEGIN;
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (1, 'Alice', 100);
INSERT INTO accounts VALUES (1, 'Bob', 200); -- the duplicate key error
ROLLBACK TO SAVEPOINT before_insert;
-- The transaction is usable again.
INSERT INTO accounts VALUES (2, 'Bob', 200);
COMMIT;The duplicate key error aborts the transaction, and the ROLLBACK TO SAVEPOINT before_insert returns it to the usable state. The subsequent insert succeeds, and the COMMIT writes it.
c. The RELEASE SAVEPOINT
The RELEASE SAVEPOINT removes the savepoint. The changes since the savepoint become part of the transaction, and the savepoint can no longer be rolled back to.
BEGIN;
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
RELEASE SAVEPOINT after_order;
-- The savepoint is removed.
-- The order_items insert is now part of the transaction.
COMMIT;The RELEASE SAVEPOINT is the way to clean up the savepoints that are no longer needed. The resources consumed by the savepoint are released, and the transaction is lighter.
The release of the nested savepoints. The RELEASE SAVEPOINT name removes the named savepoint and all the savepoints created after it.
BEGIN;
SAVEPOINT sp1;
SAVEPOINT sp2;
SAVEPOINT sp3;
RELEASE SAVEPOINT sp2;
-- The sp2 and the sp3 are removed.
-- The sp1 remains.
COMMIT;The RELEASE SAVEPOINT sp2 removes the sp2 and the sp3, and the sp1 remains. The subsequent ROLLBACK TO SAVEPOINT sp2 would be an error, and the ROLLBACK TO SAVEPOINT sp1 would still work.
The release and the commit. The COMMIT implicitly releases all the savepoints, because the transaction ends. The ROLLBACK also implicitly releases all the savepoints, because the transaction is undone.
Complete Example Session
-- ============================================
-- PART 1: THE BASIC SAVEPOINT
-- ============================================
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 1);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
-- The error.
ROLLBACK TO SAVEPOINT after_order;
-- The order is still inserted.
COMMIT;
-- ============================================
-- PART 2: THE ERROR RECOVERY
-- ============================================
BEGIN;
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (1, 'Alice', 100);
INSERT INTO accounts VALUES (1, 'Bob', 200);
-- ERROR: the duplicate key value violates the unique constraint
ROLLBACK TO SAVEPOINT before_insert;
-- The transaction is usable again.
INSERT INTO accounts VALUES (2, 'Bob', 200);
COMMIT;
SELECT * FROM accounts;
-- ============================================
-- PART 3: THE SAVEPOINT OUTSIDE THE TRANSACTION
-- ============================================
SAVEPOINT sp1;
-- ERROR: SAVEPOINT can only be used in transaction blocks
-- ============================================
-- PART 4: THE NESTED SAVEPOINTS
-- ============================================
BEGIN;
SAVEPOINT sp1;
INSERT INTO accounts VALUES (3, 'Carol', 300);
SAVEPOINT sp2;
INSERT INTO accounts VALUES (4, 'Dave', 400);
SAVEPOINT sp3;
INSERT INTO accounts VALUES (5, 'Eve', 500);
ROLLBACK TO SAVEPOINT sp1;
-- The sp2 and the sp3 are destroyed.
-- The Dave and the Eve inserts are undone.
-- The Carol insert is preserved.
COMMIT;
SELECT * FROM accounts;
-- ============================================
-- PART 5: THE RELEASE SAVEPOINT
-- ============================================
BEGIN;
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 1);
RELEASE SAVEPOINT after_order;
-- The savepoint is removed.
COMMIT;
-- ============================================
-- PART 6: THE RELEASE OF THE NESTED SAVEPOINTS
-- ============================================
BEGIN;
SAVEPOINT sp1;
SAVEPOINT sp2;
SAVEPOINT sp3;
RELEASE SAVEPOINT sp2;
-- The sp2 and the sp3 are removed.
-- The sp1 remains.
ROLLBACK TO SAVEPOINT sp1;
-- The sp1 is the current savepoint.
COMMIT;
-- ============================================
-- PART 7: THE BATCH PROCESSING
-- ============================================
BEGIN;
FOR i IN 1..10 LOOP
SAVEPOINT before_item;
BEGIN
INSERT INTO items (id, name) VALUES (i, 'Item ' || i);
EXCEPTION
WHEN unique_violation THEN
ROLLBACK TO SAVEPOINT before_item;
-- The item is skipped, the transaction continues.
END;
END LOOP;
COMMIT;
-- ============================================
-- PART 8: THE SAVEPOINT IN THE FUNCTION
-- ============================================
CREATE OR REPLACE FUNCTION safe_insert(p_id INT, p_name TEXT)
RETURNS BOOLEAN AS $$
BEGIN
INSERT INTO items (id, name) VALUES (p_id, p_name);
RETURN true;
EXCEPTION
WHEN unique_violation THEN
RETURN false;
END;
$$ LANGUAGE plpgsql;
-- The function uses the implicit savepoint.
-- ============================================
-- PART 9: THE SAVEPOINT AND THE LOCKS
-- ============================================
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SAVEPOINT after_lock;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- The error.
ROLLBACK TO SAVEPOINT after_lock;
-- The lock is STILL held, even after the rollback to the savepoint.
COMMIT;
-- The rollback to the savepoint does NOT release the locks.
-- ============================================
-- PART 10: THE COMPLETE ORDER PROCESSING
-- ============================================
BEGIN;
-- The order.
INSERT INTO orders (id, customer_id, total) VALUES (1, 1, 100);
SAVEPOINT after_order;
-- The items.
BEGIN
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 1);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT after_order;
RAISE;
END;
-- The inventory.
SAVEPOINT after_inventory;
UPDATE products SET stock = stock - 1 WHERE id IN (1, 2);
-- The payment.
SAVEPOINT before_payment;
BEGIN
-- The payment processing.
PERFORM process_payment(1, 100);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT before_payment;
-- The payment failed, but the order and the inventory are kept.
END;
COMMIT;The ten parts show the basic savepoint, the error recovery, the savepoint outside the transaction, the nested savepoints, the release savepoint, the release of the nested savepoints, the batch processing, the savepoint in the function, the savepoint and the locks, and the complete order processing.
Quick Reference
The Savepoint Commands
| Command | Purpose |
|---|---|
SAVEPOINT name | Set the marker |
ROLLBACK TO SAVEPOINT name | Return to the marker |
RELEASE SAVEPOINT name | Remove the marker |
ROLLBACK TO name | The shorthand for the ROLLBACK TO SAVEPOINT |
RELEASE name | The shorthand for the RELEASE SAVEPOINT |
The Rules
| Rule | Meaning |
|---|---|
| The savepoint must be in a transaction | The SAVEPOINT outside the BEGIN is an error |
| The name is unique | The duplicate name in the same transaction |
| The nested savepoints | The inner savepoints are destroyed by the outer rollback |
| The release removes the savepoint and the after | The named savepoint and the subsequent |
| The rollback keeps the savepoint | The transaction is positioned at it |
| The lock is not released | The locks are held until the transaction ends |
The Savepoint vs the Transaction
| Aspect | The Savepoint | The Transaction |
|---|---|---|
| The scope | The inside | The whole |
| The commit | Part of the transaction | The permanent write |
| The rollback | The partial | The whole |
| The lock release | No | Yes |
| The state | The in-transaction | The idle |
The Error Handling
| Situation | Without the savepoint | With the savepoint |
|---|---|---|
| The failed statement | The transaction is aborted | The transaction continues |
| The subsequent statement | The rejected | The runs |
| The recovery | The ROLLBACK | The ROLLBACK TO SAVEPOINT |
| The earlier changes | The undone | The preserved |
Best Practices
✅ Do This:
-- Use the savepoint before the risky operation
SAVEPOINT before_insert;
INSERT INTO accounts VALUES (...);
ROLLBACK TO SAVEPOINT before_insert; -- ✅
-- Use the savepoint for the batch processing
FOR i IN 1..10 LOOP
SAVEPOINT before_item;
BEGIN
INSERT INTO items VALUES (i);
EXCEPTION WHEN unique_violation THEN
ROLLBACK TO SAVEPOINT before_item;
END;
END LOOP; -- ✅
-- Use the RELEASE to clean up the savepoints
RELEASE SAVEPOINT after_order; -- ✅
-- Use the savepoint names that describe the point
SAVEPOINT before_payment; -- ✅
-- Use the savepoint for the expected errors
-- The duplicate key, the foreign key -- ✅❌ Don’t Do This:
-- Don't use the SAVEPOINT outside the transaction
SAVEPOINT sp1; -- the error // ❌
-- Don't forget the ROLLBACK TO after the error
SAVEPOINT sp1;
INSERT INTO accounts VALUES (...);
-- The error, and the subsequent statements are rejected // ❌
-- Don't use the savepoint for the routine flow
-- The overhead // ❌
-- Don't expect the lock release
ROLLBACK TO SAVEPOINT sp1;
-- The locks are still held // ❌
-- Don't use the duplicate savepoint names
SAVEPOINT sp1;
SAVEPOINT sp1; -- the shadowing // ❌
-- Don't rely on the savepoint for the serialization
-- The serialization failure aborts the whole transaction // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| The savepoint error | The outside the transaction | Use the BEGIN first |
| The aborted transaction | No savepoint | Use the ROLLBACK TO SAVEPOINT |
| The savepoint is destroyed | The outer rollback | Track the savepoints |
| The lock is held | The rollback to the savepoint | Commit to release |
| The savepoint overhead | The excessive savepoints | Use them for the errors only |
| The duplicate name | The reused name | Use the unique names |
| The release error | The wrong name | Check the name |
Real-World Examples
1. The Basic Savepoint
SAVEPOINT after_order;
ROLLBACK TO SAVEPOINT after_order;2. The Error Recovery
SAVEPOINT before_insert;
-- The error
ROLLBACK TO SAVEPOINT before_insert;
-- The transaction continues3. The Nested Savepoints
SAVEPOINT sp1;
SAVEPOINT sp2;
ROLLBACK TO SAVEPOINT sp1;4. The Release
RELEASE SAVEPOINT after_order;5. The Batch Processing
FOR i IN 1..10 LOOP
SAVEPOINT before_item;
BEGIN
INSERT INTO items VALUES (i);
EXCEPTION WHEN unique_violation THEN
ROLLBACK TO SAVEPOINT before_item;
END;
END LOOP;6. The Savepoint in the Function
BEGIN
INSERT INTO items VALUES (p_id);
EXCEPTION
WHEN unique_violation THEN
RETURN false;
END;7. The Savepoint and the Lock
SELECT * FROM accounts FOR UPDATE;
SAVEPOINT sp1;
-- The lock is held until the commit.8. The Complete Order
BEGIN;
INSERT INTO orders VALUES (...);
SAVEPOINT after_order;
INSERT INTO order_items VALUES (...);
ROLLBACK TO SAVEPOINT after_order;
COMMIT;9. The Release of the Nested
SAVEPOINT sp1;
SAVEPOINT sp2;
RELEASE SAVEPOINT sp2;
-- The sp2 is removed.10. The Savepoint Outside
SAVEPOINT sp1; -- the errorVisual
The Savepoint Mechanics
┌──────────────────────────────────────────────────────────────┐
│ THE SAVEPOINT MECHANICS │
│ │
│ BEGIN; │
│ INSERT INTO orders VALUES (1, 1); │
│ SAVEPOINT after_order; ← the marker │
│ INSERT INTO order_items VALUES (1, 1); │
│ -- The error. │
│ ROLLBACK TO SAVEPOINT after_order; │
│ -- The order is kept, the order_items is undone. │
│ COMMIT; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The timeline: │ │
│ │ │ │
│ │ BEGIN ──► order ──► SAVEPOINT ──► order_items │ │
│ │ │ │ │ │
│ │ │ ▼ │ │
│ │ │ ERROR │ │
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ ROLLBACK TO ◄───────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ the transaction continues │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ COMMIT │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The changes before the savepoint are kept. │
│ The changes after the savepoint are undone. │
│ │
└──────────────────────────────────────────────────────────────┘The Nested Savepoints
┌──────────────────────────────────────────────────────────────┐
│ THE NESTED SAVEPOINTS │
│ │
│ BEGIN; │
│ SAVEPOINT sp1; │
│ INSERT INTO accounts VALUES (1, 'Alice', 100); │
│ SAVEPOINT sp2; │
│ INSERT INTO accounts VALUES (2, 'Bob', 200); │
│ SAVEPOINT sp3; │
│ INSERT INTO accounts VALUES (3, 'Carol', 300); │
│ ROLLBACK TO SAVEPOINT sp1; │
│ COMMIT; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The timeline: │ │
│ │ │ │
│ │ sp1 ──► Alice ──► sp2 ──► Bob ──► sp3 ──► Carol │ │
│ │ │ │ │ │
│ │ │ ▼ │ │
│ │ │ ROLLBACK TO │ │
│ │ │ SAVEPOINT sp1│ │
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ The sp1 remains. The sp2 │ │
│ │ The Alice is kept. and the │ │
│ │ The Bob and the Carol are undone. sp3 are │ │
│ │ destroyed│ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The ROLLBACK TO SAVEPOINT destroys the savepoints created │
│ after the target. │
│ │
└──────────────────────────────────────────────────────────────┘The Aborted Transaction Recovery
┌──────────────────────────────────────────────────────────────┐
│ THE ABORTED TRANSACTION RECOVERY │
│ │
│ WITHOUT the savepoint: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN; │ │
│ │ INSERT INTO accounts VALUES (1, 'Alice', 100); │ │
│ │ INSERT INTO accounts VALUES (1, 'Bob', 200); │ │
│ │ -- ERROR: the duplicate key │ │
│ │ SELECT * FROM accounts; │ │
│ │ -- ERROR: the current transaction is aborted │ │
│ │ ROLLBACK; │ │
│ │ -- The whole transaction is undone. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ WITH the savepoint: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ BEGIN; │ │
│ │ SAVEPOINT before_insert; │ │
│ │ INSERT INTO accounts VALUES (1, 'Alice', 100); │ │
│ │ INSERT INTO accounts VALUES (1, 'Bob', 200); │ │
│ │ -- ERROR: the duplicate key │ │
│ │ ROLLBACK TO SAVEPOINT before_insert; │ │
│ │ -- The transaction is usable again. │ │
│ │ INSERT INTO accounts VALUES (2, 'Bob', 200); │ │
│ │ COMMIT; │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The savepoint recovers the aborted transaction. │
│ │
└──────────────────────────────────────────────────────────────┘The Savepoint and the Lock
┌──────────────────────────────────────────────────────────────┐
│ THE SAVEPOINT AND THE LOCK │
│ │
│ BEGIN; │
│ SELECT * FROM accounts WHERE id = 1 FOR UPDATE; │
│ -- The lock is acquired. │
│ SAVEPOINT after_lock; │
│ UPDATE accounts SET balance = balance - 100 WHERE id = 1; │
│ -- The error. │
│ ROLLBACK TO SAVEPOINT after_lock; │
│ -- The UPDATE is undone, but the lock is STILL held. │
│ COMMIT; │
│ -- The lock is released. │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ The rollback to the savepoint undoes the DATA │ │
│ │ changes, but it does NOT release the LOCKS. │ │
│ │ │ │
│ │ The locks are released only when the transaction │ │
│ │ ends (the COMMIT or the ROLLBACK). │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
SAVEPOINT name | Set the marker |
ROLLBACK TO SAVEPOINT name | Return to the marker |
RELEASE SAVEPOINT name | Remove the marker |
| The transaction | Required for the savepoint |
| The nested savepoints | The inner destroyed by the outer rollback |
| The error recovery | The ROLLBACK TO SAVEPOINT clears the abort |
| The lock release | Not released by the savepoint rollback |
| The batch processing | The savepoint per item |
Key takeaways:
- A savepoint is a named marker inside a transaction. The
SAVEPOINT namesets the marker, theROLLBACK TO SAVEPOINT namereturns to it, and theRELEASE SAVEPOINT nameremoves it. The savepoint must be inside a transaction block, and theSAVEPOINToutside theBEGINis an error . - The
ROLLBACK TO SAVEPOINTundoes the changes since the marker. The changes before the marker are kept, and the transaction stays open and usable. The savepoint itself is not destroyed, and the rollback can be repeated . - The savepoint is the recovery from the aborted transaction. In PostgreSQL, a failed statement aborts the whole transaction, and the subsequent statements are rejected. The
ROLLBACK TO SAVEPOINTreturns the transaction to the usable state, and the subsequent statements can run . - The savepoints are nested. The
ROLLBACK TO SAVEPOINTdestroys the savepoints created after the target. TheRELEASE SAVEPOINT nameremoves the named savepoint and all the savepoints created after it. - The savepoint does not release the locks. The
ROLLBACK TO SAVEPOINTundoes the data changes, but the locks acquired since the savepoint are held until the transaction ends. The lock release happens only at theCOMMITor theROLLBACK. - The savepoint is the pattern for the batch processing. The loop sets the savepoint before each item, processes the item, and rolls back to the savepoint on the failure. The failed items are skipped, and the successful items are kept.
- The savepoint has the overhead. The database tracks the state at the savepoint, and the excessive savepoints slow the transaction. The
RELEASE SAVEPOINTremoves the savepoint and the resources it consumed. The savepoints are for the expected errors, not the routine flow. - The savepoint does not help with the serialization failures. The serialization failure aborts the whole transaction, and the
ROLLBACK TO SAVEPOINTcannot recover it. The application must retry the whole transaction.
Remember: A savepoint is the named marker inside the transaction, and the ROLLBACK TO SAVEPOINT is the partial rollback. The savepoint recovers the aborted transaction, isolates the errors, and enables the batch processing. The nested savepoints provide the hierarchy, and the RELEASE cleans up the markers. The savepoint does not release the locks, and the excessive savepoints have the cost. The result is the fine-grained control over the transaction’s internal recovery, which is the foundation of the robust error handling.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!