Welcome to Transactions and Error Handling in PostgreSQL
Transactions and Error Handling in PostgreSQL
Jason Myers
Principal Engineer
Learning objectives and datasets
- Maintain data integrity and credibility with transaction
- Concurrency's effects on transactions
- Error Handling with transactions
Motivation for using transactions
patient_intake
| name | priority |
|---|---|
| Oscar Parker | 1 |
| Prisha Ahmed | 2 |
| Rhea Taylor | 3 |
UPDATE patient_intake SET priority=1
WHERE name='Prisha Ahmed';
UPDATE patient_intake SET priority=2
WHERE name='Oscar Parker';
Errored table results
patient_intake
| name | priority |
|---|---|
| Oscar Parker | 1 |
| Prisha Ahmed | 1 |
| Rhea Taylor | 3 |
Using a transaction
BEGIN;
UPDATE patient_intake SET priority=1
WHERE name='Prisha Ahmed';
UPDATE patient_intake SET priority=2
WHERE name='Oscar Parker';
COMMIT;
Another reason to use transactions
- Multiple statements that should succeed or fail as a group
- Handling how statements are affected by concurrent operations
Transaction blocks
BEGIN TRANSACTION;
UPDATE patient_intake SET priority=1
WHERE name='Prisha Ahmed';
UPDATE patient_intake SET priority=2
WHERE name='Oscar Parker';
COMMIT;
Any number of statements
BEGIN;
UPDATE patient_intake SET priority=2
WHERE name='Oscar Parker';
UPDATE patient_intake SET priority=1
WHERE name='Rhea Taylor';
UPDATE patient_intake SET priority=3
WHERE name='Prisha Ahmed';
COMMIT;
Let's practice!
Transactions and Error Handling in PostgreSQL
