SAVEPOINT

-- Create a savepoint
SAVEPOINT savepoint_name;

-- Rollback to a savepoint (keeps changes before the savepoint)
ROLLBACK TO SAVEPOINT savepoint_name;

-- Release a savepoint (optional — removes the savepoint marker)
RELEASE SAVEPOINT savepoint_name;

-- Full example
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (42, 1500);

  SAVEPOINT after_order;  -- mark this point

  INSERT INTO payments (order_id, amount) VALUES (LASTVAL(), 1500);
  -- If payment fails:
  ROLLBACK TO SAVEPOINT after_order;  -- undo just the payment
  -- order is still in transaction!

  -- Try different payment method
  INSERT INTO payments (order_id, amount, method) VALUES (LASTVAL(), 1500, 'COD');

COMMIT;  -- commit both order and COD payment

-- Complex checkout: savepoints for each critical step
BEGIN;

-- Step 1: Create order
INSERT INTO orders (user_id, status, total)
VALUES (42, 'draft', 2999) RETURNING id;

SAVEPOINT order_created;

-- Step 2: Apply coupon discount
UPDATE orders SET
  discount_amount = 300,
  total = total - 300
WHERE id = :order_id;

-- Validate coupon is still valid
SELECT uses_remaining FROM coupons WHERE code = 'SAVE300';
-- If 0 remaining:
-- ROLLBACK TO SAVEPOINT order_created;  -- undo discount, keep order

SAVEPOINT discount_applied;

-- Step 3: Reserve inventory
UPDATE products SET reserved_stock = reserved_stock + 1
WHERE id IN (101, 205);

-- Check if stock is available
SELECT id FROM products
WHERE id IN (101, 205) AND stock - reserved_stock < 0;
-- If out of stock:
-- ROLLBACK TO SAVEPOINT discount_applied;  -- undo reservation

SAVEPOINT inventory_reserved;

-- Step 4: Process payment (external API call)
INSERT INTO payment_attempts (order_id, amount, gateway) VALUES (:order_id, 2699, 'razorpay');

-- If payment fails: ROLLBACK TO SAVEPOINT inventory_reserved
-- If payment succeeds:
COMMIT;