1

Let's assume a scenario with the entities Person and 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.
  • One UPDATE to merge the duplicates into the final row.
  • One DELETE to remove the offending duplicates.
  • The above UPDATE to 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?

2

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.

2
  • This is very elegant, indeed. Nitpick: I had to insert an SET after the DO UPDATE. It is also worth noting that excluded is a built-in magic specifically for referring to the conflicting row as documented here. I've also found this pattern in the docs under Data-Modifying Statements in WITH.
    – bluenote10
    May 18 at 11:58
  • oops, my error. I noticed another one and just updated query. Correct, excluded is special name for the conflicting row.
    – Melkij
    May 18 at 12:13
1

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

2
  • Thanks for the confirmation! Do you perhaps have a rough outline of how the first two steps actually look like in practice? Not having written any meaningful SQL in years, I'm struggling a bit, e.g. whether I can/should avoid loading all the offending IDs into the host programming language or whether I can do it all in SQL.
    – bluenote10
    May 18 at 10:58
  • You run something like SELECT * FROM personcompanystocks WHERE company IN ('foo', 'bar') FOR UPDATE;. You need those rows anyway, so that's no wasted effort. It puts row lock on the selected rows, so that no concurrent transaction can modify them. May 18 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.