SAVEPOINT
SAVEPOINT creates a partial checkpoint within a transaction. ROLLBACK TO SAVEPOINT undoes only the work done after the savepoint - not the entire transaction. This lets you implement nested error recovery within complex multi-step operations.
SAVEPOINT Use Cases
- Complex multi-step operations where only part may fail
- Batch processing: rollback one batch item, continue with others
- Nested error handling: rollback attempt A, try approach B instead
- RELEASE SAVEPOINT removes it when no longer needed (reduces overhead)
- Nested savepoints are supported in PostgreSQL
SAVEPOINT Examples
-- SAVEPOINT for multi-step order with fallback
BEGIN;
-- Step 1: deduct stock
UPDATE products SET stock = stock - 1 WHERE id = 1;
SAVEPOINT after_stock; -- checkpoint here
-- Step 2: try primary payment method
INSERT INTO payments (order_id, amount, payment_method)
VALUES (1, 999.99, 'credit_card');
-- If this fails (card declined):
-- ROLLBACK TO SAVEPOINT after_stock; -- undo just the payment
-- (stock update preserved)
-- Step 3: try backup payment method
-- INSERT INTO payments (order_id, amount, payment_method)
-- VALUES (1, 999.99, 'paypal');
-- If all good:
COMMIT;
-- Practical: batch insert with per-item rollback
BEGIN;
SAVEPOINT before_item_1;
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 1, 2, 999.99);
-- success: keep it, move to next
SAVEPOINT before_item_2;
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (1, 99, 1, 0);
-- failure: product 99 doesn't exist (FK violation)
ROLLBACK TO SAVEPOINT before_item_2; -- only undo this item
-- continue processing other items
COMMIT; -- commit successful itemsQuick Quiz
Tip
Tip
Practice SAVEPOINT in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with SAVEPOINT is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of SAVEPOINT from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- SAVEPOINT creates a partial checkpoint within a transaction.
- Complex multi-step operations where only part may fail
- Batch processing: rollback one batch item, continue with others
- Nested error handling: rollback attempt A, try approach B instead