Handling mistakes

Transactions and Error Handling in PostgreSQL

Jason Myers

Principal Engineer

Mistakes happen

Mistakenly updated Biscuits

UPDATE cookies SET quantity = 13 WHERE name = 'Biscuits';

Corrected to update Biscotti

UPDATE cookies SET quantity = 1 WHERE name = 'Biscuits';

UPDATE cookies SET quantity = 13 WHERE name = 'Biscotti';
Transactions and Error Handling in PostgreSQL

Rolling back mistakes

BEGIN TRANSACTION

UPDATE cookies SET quantity = 13 WHERE name = 'Biscuits';
ROLLBACK;
SELECT quantity FROM cookies where name = 'Biscuits';
13
Transactions and Error Handling in PostgreSQL

Rollback multiple statements

BEGIN TRANSACTION;

UPDATE cookies SET deliciousness = 111 where name = 'Cats Tongue';

UPDATE cookies SET deliciousness = 8 where name = 'Gingerbread';

ROLLBACK;
SELECT name, deliciousness FROM cookies where name in ('Cats Tongue', 'Gingerbread');
'Cats Tongue'  10
'Gingerbread'   9
Transactions and Error Handling in PostgreSQL

Let's practice!

Transactions and Error Handling in PostgreSQL

Preparing Video For Download...