PostgreSQL anti-patterns: read-modify-write cycles (2014)

Re:PostgreSQL anti-patterns: read-modify-write cycles (2014) by eveningcoffee on Wednesday December 21, 2016 @09:11AM (#13229174)

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.

[ Parent | on HN ]

Re:PostgreSQL anti-patterns: read-modify-write cycles (2014) by buro9 on Wednesday December 21, 2016 @09:15AM (#13229211)

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).

[ Parent | on HN ]