Rolling back to a savepoint

Transactions and Error Handling in PostgreSQL

Jason Myers

Principal Engineer

Without savepoints

BEGIN TRANSACTION;

UPDATE cost = 2.33 WHERE name = 'Linga';

UPDATE cost = 500 WHERE name = 'Macaron';

ROLLBACK;

Using Savepoints and targeted rollbacks

BEGIN TRANSACTION;

UPDATE cost = 2.33 WHERE name = 'Linga';


SAVEPOINT oops;


UPDATE cost = 500 WHERE name = 'Macaron';


ROLLBACK TO oops;

Releasing a savepoint when done with it

BEGIN TRANSACTION;

UPDATE cost = 2.33 WHERE name = 'Linga';
UPDATE cost = 2.33 WHERE name = 'Petit-Beurre';
UPDATE cost = 2.33 WHERE name = 'Rosette';

SAVEPOINT oops;

UPDATE cost = 5.00 WHERE name = 'Macaron';
UPDATE cost = 3.50 WHERE name = 'Panellets';

RELEASE SAVEPOINT oops;

Two critical things about rollbacks and savepoints

ROLLBACK without a TO will rollback the whole transaction
ROLLBACK TO ___ where ___ is not a valid savepoint name will cause an error

Let's practice!

Transactions and Error Handling in PostgreSQL