<story><title>PostgreSQL anti-patterns: read-modify-write cycles (2014)</title><url>http://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/</url></story><parent_chain><item><author>rejschaap</author><text>Another solution is to not use update statements at all. Obviously this involves some changes to the tables. And it defers the balance calculation to query time.&lt;p&gt;Analogous to the example from the article:&lt;p&gt;&lt;pre&gt;&lt;code&gt;  create table transaction (user_id integer, amount integer);
  
  insert into transaction values (1, -100);
  
  select sum(amount) as balance from transaction;&lt;/code&gt;&lt;/pre&gt;</text></item></parent_chain><comment><author>eveningcoffee</author><text>This approach is good if you do not have to apply some check on the balance before inserting a new transaction. If you do (say verifying that balance does not go negative) then you have at least two options:&lt;p&gt;1) you make transactions serialized&lt;p&gt;&lt;pre&gt;&lt;code&gt;  BEGIN ISOLATION LEVEL SERIALIZABLE;
  select sum(amount) as balance from transaction where user_id = 1;
  -- do something with balance
  insert into transaction values (1, -100); 
  COMMIT;
&lt;/code&gt;&lt;/pre&gt;
2) or you use row level locking on an additional table (say user, while this conflicts with the system table user, but for the sake of the example we assume this is not the case) to serialize the access.&lt;p&gt;&lt;pre&gt;&lt;code&gt;  BEGIN; 
  select user_id from user where user_id = 1 for update; 
  select sum(amount) as balance from transaction where user_id = 1;
  -- do something with balance
  insert into transaction values (1, -100);
  COMMIT;
&lt;/code&gt;&lt;/pre&gt;
Now it may be tempting to change this to&lt;p&gt;&lt;pre&gt;&lt;code&gt;  BEGIN; 
  select balance from user where user_id = 1 for update; 
  -- do something with balance
  update user set balance = balance + (-100) where user_id = 1; 
  insert into transaction values (1, -100);
  COMMIT;
&lt;/code&gt;&lt;/pre&gt;
Just for clarity: in most cases you should register your transaction event anyway (with a time stamp) for later auditing, but you now will have a risk of introducing inconsistencies between calculated balance and transactions due to the programming errors (that is partially committed transactions).&lt;p&gt;Also you should obviously create proper primary keys and indexes.&lt;p&gt;Also you should include proper error&amp;#x2F;exception handling.&lt;p&gt;Also if you already do locking, then you could also consider different locking strategies described in the article.</text></comment>
<story><title>PostgreSQL anti-patterns: read-modify-write cycles (2014)</title><url>http://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/</url></story><parent_chain><item><author>rejschaap</author><text>Another solution is to not use update statements at all. Obviously this involves some changes to the tables. And it defers the balance calculation to query time.&lt;p&gt;Analogous to the example from the article:&lt;p&gt;&lt;pre&gt;&lt;code&gt;  create table transaction (user_id integer, amount integer);
  
  insert into transaction values (1, -100);
  
  select sum(amount) as balance from transaction;&lt;/code&gt;&lt;/pre&gt;</text></item></parent_chain><comment><author>yomly</author><text>I like this idea as I am pro-history keeping and prefer not to make irreversible changes like an update.&lt;p&gt;What are the space concerns for keeping data in this way? For something like a retail bank (audit issues aside which probably make this necessary anyway) does space suddenly become a big factor?</text></comment>