主页 / dba / 问题 / 291767
Accepted
bluenote10
Asked: 2021-05-19 02:24:35 +0800 CST

如何在 Postgres 的主键上处理 UPDATE 中的重复项？

  • 772

让我们假设一个场景，其中包含实体Person和Company一个表格，该表格PersonCompanyStocks模拟了一个人拥有某家公司的股票数量（N:M 基数）。例如：

person | company | num_stocks
-----------------------------
Alice  | foo     | 300
Bob    | foo     | 100
Bob    | bar     | 200

该表用(person, company)作主键来保证唯一的条目，以及各个个人/公司表的外键（为简单起见，我只使用字符串 ID）。

现在让我们假设 companybar收购 company foo。我们希望以如下方式更新表：

person | company | num_stocks
-----------------------------
Alice  | bar     | 300
Bob    | bar     | 300

仅查看Alice's 记录建议使用天真的方法，例如：

UPDATE
  PersonCompanyStocks
SET
  company = "bar"
WHERE
  company = "foo"

但是，此更新失败，duplicate key value violates unique constraint ...因为Bob已经有一行带有 key ("Bob", "bar")。ForINSERT的 Postgres 支持ON CONFLICT DO ...，但似乎没有对应的 for UPDATE。显然，我们还必须处理正确合并num_stock两行的值。

解决这个问题的最佳策略是什么？我只看到一个相对难看的解决方案：

  • 一个查询来确定重复项。
  • 一个UPDATE将重复项合并到最后一行。
  • 一个DELETE删除有问题的重复项。
  • 以上UPDATE是在没有重复的行中进行重命名。

这感觉很复杂，并且可能容易出现竞争条件。Postgres 是否提供任何技巧来更优雅地解决这个问题？

postgresql primary-key

2 个回答

  1. Best Answer

    我们需要在公司之间转移股票，对吧？这意味着我们需要向现有用户添加股票并为新用户更换公司。或者，同样的，我们可以删除公司“foo”的所有股票和公司“ insert .. on conflictbar”的新行

    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;

    事务性的，由于删除期间的行锁定，这里没有竞争条件。

    • 4

  2. 你的程序很好。为避免竞争条件，请在单个事务和SELECT ... FOR UPDATE您打算修改的所有行中完成所有操作（悲观锁定）。

    • 1

相关问题