postgresql – 用ORDER BY更新Postgres,怎么做？

发布时间：2020-12-13 16:18:55 所属栏目：百科来源：网络整理

导读：我需要对记录集合进行Postgres更新.我正在努力防止压力测试中出现的死锁. 对此的典型解决方案是按ID按特定顺序更新记录,例如 – 但似乎Postgres不允许ORDER BY进行更新. 假设我需要进行更新,例如： UPDATE BALANCES WHERE ID IN (SELECT ID FROM some_functi

我需要对记录集合进行Postgres更新.我正在努力防止压力测试中出现的死锁.

对此的典型解决方案是按ID按特定顺序更新记录,例如 – 但似乎Postgres不允许ORDER BY进行更新.

假设我需要进行更新,例如：

UPDATE BALANCES WHERE ID IN (SELECT ID FROM some_function() ORDER BY ID);

同时运行200个查询时会导致死锁.该怎么办？

我正在寻找一个通用的解决方案,而不是像UPDATE with ORDER BY那样的特定于案例的解决方法

感觉必须有比编写游标功能更好的解决方案.此外,如果没有更好的方法,那么光标的最佳效果如何？逐个记录更新

据我所知,没有办法通过UPDATE语句直接完成此操作;保证锁定顺序的唯一方法是使用SELECT … ORDER BY ID for UPDATE显式获取锁,例如：

UPDATE Balances
SET Balance = 0
WHERE ID IN (
  SELECT ID FROM Balances
  WHERE ID IN (SELECT ID FROM some_function())
  ORDER BY ID
  FOR UPDATE
)

这具有在Balances表上重复ID索引查找的缺点.在您的简单示例中,您可以通过在锁定查询期间获取物理行地址(由ctid system column表示)并使用它来驱动UPDATE来避免此开销：

UPDATE Balances
SET Balance = 0
WHERE ctid = ANY(ARRAY(
  SELECT ctid FROM Balances
  WHERE ID IN (SELECT ID FROM some_function())
  ORDER BY ID
  FOR UPDATE
))

(使用ctids时要小心,因为值是暂时的.我们在这里很安全,因为锁会阻止任何变化.)

不幸的是,规划器只会在一组狭窄的情况下使用ctid(您可以通过在EXPLAIN输出中查找“Tid Scan”节点来判断它是否正常工作).要在单个UPDATE语句中处理更复杂的查询,例如如果您的新余额由some_function()和ID一起返回,则您需要回退到基于ID的查找：

UPDATE Balances
SET Balance = Locks.NewBalance
FROM (
  SELECT Balances.ID,some_function.NewBalance
  FROM Balances
  JOIN some_function() ON some_function.ID = Balances.ID
  ORDER BY Balances.ID
  FOR UPDATE
) Locks
WHERE Balances.ID = Locks.ID

如果性能开销是个问题,则需要使用游标,如下所示：

DO $$
DECLARE
  c CURSOR FOR
    SELECT Balances.ID,some_function.NewBalance
    FROM Balances
    JOIN some_function() ON some_function.ID = Balances.ID
    ORDER BY Balances.ID
    FOR UPDATE;
BEGIN
  FOR row IN c LOOP
    UPDATE Balances
    SET Balance = row.NewBalance
    WHERE CURRENT OF c;
  END LOOP;
END
$$

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!