Deviopath › PostgreSQL

Transfers With Savepoints

Hard · Transactions

Inside a transaction, SAVEPOINT name marks a point you can go back to. ROLLBACK TO SAVEPOINT name undoes everything after it (including later savepoints) but keeps the work before it and keeps the transaction open. COMMIT saves what is left.

A transfer means three statements: insert a row into transfers, subtract the amount from the sender's balance, and add it to the receiver's. Write one transaction that does exactly this, in order:

1. BEGIN, then transfer 100.00 from account 1 to account 2, then SAVEPOINT after_rent. 2. Transfer 25.00 from account 2 to account 3, then SAVEPOINT after_gift. 3. Transfer 40.00 from account 3 to account 1. 4. ROLLBACK TO SAVEPOINT after_gift. 5. Transfer 5.00 from account 1 to account 3. 6. ROLLBACK TO SAVEPOINT after_rent. 7. Transfer 10.00 from account 1 to account 3, then COMMIT.

The check shows the balances and the transfers rows with their ids. Note: a rolled-back INSERT still uses up its serial number, because sequences are never rolled back.

Next: Keep updated_at Current