Multiple savepoints and rollback

Transactions and Error Handling in PostgreSQL

Jason Myers

Principal Engineer

Multiple savepoints

BEGIN TRANSACTION;

UPDATE inventory SET quantity = quantity - 1 WHERE name in ('flour', 'sugar');

SAVEPOINT inventory_step;

INSERT baking_list SET quantity=12 WHERE name='Torun';

SAVEPOINT queuing_step;

UPDATE cookies SET quantity = 12 WHERE name = 'Torun';

Duplicating savepoint names

A new SAVEPOINT with the same name as a prior one in the same transaction shadows it instead of overwritting or releasing it!
Generally avoid reusing names within a transaction.

Duplicate savepoint name example

BEGIN TRANSACTION;

UPDATE inventory SET quantity = quantity - 1 WHERE name in ('flour', 'sugar');

SAVEPOINT oops;

INSERT baking_list SET quantity=12 WHERE name='Torun';

SAVEPOINT oops;

UPDATE cookies SET quantity = 12 WHERE name = 'Torun';

Let's practice!

Transactions and Error Handling in PostgreSQL