3 ms·

Hmm. Am I the only one who would consider a CTE for this, selecting the initial balance and the proposed balance minus amount in the CTE, testing it in the que

by buro9 10y ago

Hmm. Am I the only one who would consider a CTE for this, selecting the initial balance and the proposed balance minus amount in the CTE, testing it in the query following the CTE and potentially applying it, and then returning the original and new balance to the application. If both are the same then the update was not applied (not enough balance), and if different the update was applied (enough balance for the withdrawal). This way it all occurs within a single transaction, even though it is several logical steps, and is simple to reason about.

glogla 10y ago
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.
eveningcoffee 10y ago
In principle if you want to do the (withdrawal) balance check then you can incorporate it into a single query UPDATE balance SET balance = balance - 100 WHERE user_id = 1 and balance >= 100; Only problem is that you now actually do not know if the query did not update because of insufficient balance or because lack of user in the database.
- buro9 10y ago
  That is why I prefer the CTE approach. Query 1 (in CTE): Verify all is correct and the current balance (and whether you have enough balance) Query 2: Perform update and obtain new balance Then return a summary showing the starting balance and end balance for the transaction (proof of what, if anything, happened).
- deleted 10y ago
  [deleted]
bpicolo 10y ago
CTEs are still vulnerable to race conditions afaik, plus postgres does a poor job of optimizing them performance wise (that is, it doesn't) http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...

by buro9 10y ago

glogla 10y ago

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.

eveningcoffee 10y ago

In principle if you want to do the (withdrawal) balance check then you can incorporate it into a single query UPDATE balance SET balance = balance - 100 WHERE user_id = 1 and balance >= 100; Only problem is that you now actually do not know if the query did not update because of insufficient balance or because lack of user in the database.

buro9 10y ago
That is why I prefer the CTE approach. Query 1 (in CTE): Verify all is correct and the current balance (and whether you have enough balance) Query 2: Perform update and obtain new balance Then return a summary showing the starting balance and end balance for the transaction (proof of what, if anything, happened).
deleted 10y ago
[deleted]

bpicolo 10y ago

CTEs are still vulnerable to race conditions afaik, plus postgres does a poor job of optimizing them performance wise (that is, it doesn't) http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...