A transaction is atomic: the database keeps all of its changes, or none of them. You saw it with ROLLBACK. It is also true when a statement fails.
A statement fails
Sofia has 50. She sends 80 to Ava:
| Step | Statement | Result |
|---|---|---|
| 1 | BEGIN; | transaction starts |
| 2 | add 80 to Ava | works: Ava has 580 |
| 3 | take 80 from Sofia | fails: the balance would be -30, and the CHECK refuses it |
| 4 | any statement | fails: the transaction is aborted |
After the error in step 3, PostgreSQL marks the transaction as aborted. It refuses every statement that follows:
text
ERROR: current transaction is aborted, commands ignored until end of transaction blockThe only thing you can do is end the transaction:
ROLLBACK;undoes the transaction.COMMIT;also ends it, but PostgreSQL cannot commit an aborted transaction. It undoes everything, the same asROLLBACK.
Ava does not keep the 80. The transfer happened completely or not at all: here, not at all.
Ava + 80works
Sofia - 80fails
end
Ava 500, Sofia 50no change kept
In the workbench
A run stops at the first statement that fails (Chapter 1). While the transaction is aborted, the table tabs keep the last picture they could read. End the transaction to see the rows again.