All we need is an easy explanation of the problem, so here it is.
Let’s assume a scenario with the entities
Company and a table
PersonCompanyStocks that models how many stocks a persons owns of a certain company (N:M cardinality). For example:
person | company | num_stocks ----------------------------- Alice | foo | 300 Bob | foo | 100 Bob | bar | 200
This table uses
(person, company) as a primary key to guarantee unique entries, and foreign keys to the respective person/company table (I’m only using string IDs for simplicity).
Now let’s assume company
bar buys company
foo. We want to update the table in a way that it becomes:
person | company | num_stocks ----------------------------- Alice | bar | 300 Bob | bar | 300
Looking only at
Alice‘s record suggests to use a naive approach like:
UPDATE PersonCompanyStocks SET company = "bar" WHERE company = "foo"
However this update fails with
duplicate key value violates unique constraint ... because for
Bob there already is a row with the key
("Bob", "bar"). For
INSERT‘s Postgres supports
ON CONFLICT DO ..., but it looks like there is no equivalent for
UPDATE. And clearly we also have to deal with properly merging the
num_stock value of the two rows.
What is the best strategy to approach this problem? I only see a relatively ugly solution:
- One query to determine the duplicates.
UPDATEto merge the duplicates into the final row.
DELETEto remove the offending duplicates.
- The above
UPDATEto do the renaming in rows without duplicates.
This feels complex and is probably prone to race conditions. Does Postgres offer any trick to solve this more elegantly?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
We need move stocks between companies, right? What means we need add stocks to existing users and change company for new users. Or, what the same, we could delete all stocks of company "foo" and
insert .. on conflict new rows for company "bar"
with rows as ( delete from PersonCompanyStocks where company = 'foo' returning person, num_stocks ) insert into PersonCompanyStocks (person, company, num_stocks) select person, 'bar', num_stocks from rows on conflict(person,company) do update set num_stocks = PersonCompanyStocks.num_stocks + excluded.num_stocks;
Transactional, no race conditions here thanks to row locking during delete.
Your procedure is good. To avoid race conditions, do it all in a single transaction and
SELECT ... FOR UPDATE all the rows that you intend to modify (pessimistic locking).
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂