Re:PostgreSQL anti-patterns: read-modify-write cycles (2014)
by glogla on Wednesday December 21, 2016 @04:08AM
(#13227784)
That's the optimistic way - do the transaction, return result and if the result doesn't pass the validation, rollback.
In postgres, you don't really need to do CTE - UPDATE can return the new row if you add RETURNING * so you can just do the update and look at result.
In Oracle (and what we did in actual bank) was pl/sql procedure that did
- select for update
- do the validation
- apply the transfer, or return error.
- let the application rollback or commit
This is hard to do without procedural language - you can work with CTEs and functions, but SQL is great at dataflow and bad at control flow, and you either drop to procedural language in database, or have the application do the actual logic.
In postgres, you don't really need to do CTE - UPDATE can return the new row if you add RETURNING * so you can just do the update and look at result.
In Oracle (and what we did in actual bank) was pl/sql procedure that did
- select for update - do the validation - apply the transfer, or return error. - let the application rollback or commit
This is hard to do without procedural language - you can work with CTEs and functions, but SQL is great at dataflow and bad at control flow, and you either drop to procedural language in database, or have the application do the actual logic.