按顺序更新（UPDATE）

Question

需要将 UPDATE 与 ORDER BY "绑定"。我正在尝试使用游标，但是出现了错误：

cursor "cursupd" doesn't specify a line,
SQL state: 24000

代码：

BEGIN;
    DECLARE cursUpd CURSOR FOR SELECT * FROM "table" WHERE "field" = 5760 AND "sequence" >= 0 AND "sequence" < 9 ORDER BY "sequence" DESC;
    UPDATE "table" SET "sequence" = "sequence" + 2 WHERE CURRENT OF cursUpd;
    CLOSE cursUpd;
COMMIT;

如何正确执行？

更新 1

没有光标时，我这样做：

UPDATE "CableLinePoint" AS "t"
SET "sequence" = t."sequence" + 2
from (
    select max("sequence") "sequence", "id"
    from "CableLinePoint"
    where
        "CableLine" = 5760
    group by "id"
    ORDER BY "sequence" DESC
) "s"
where "t"."id" = "s"."id" and "t"."sequence" = "s"."sequence"

我遇到了一个独特的错误。因此，需要从结尾而不是开头进行更新。

更新2

表格：

id|CableLine|sequence
10|    2    |    1
11|    2    |    2
12|    2    |    3
13|    2    |    4
14|    2    |    5

需要更新（增加）字段“sequence”。 “sequence”具有“index”类型，因此无法完成：

UPDATE "table" SET "sequence" = "sequence" + 1 WHERE "CableLine" = 2

当将具有id = 10的行中的“sequence”增加1时，我会收到一个错误，指出另一行已经存在具有"sequence" = 2的值。

- dedoki

---

请解释一下您想要实现什么。id是主键吗？ - Erwin Brandstetter

是的，id 是主键。已更新第一篇帖子。 - dedoki

这大大简化了查询（更新后的答案，解决方法 不是 单个事务！）。非可延迟约束的核心问题仍然存在。 - Erwin Brandstetter

Answer 1

按顺序更新（ORDER BY）:

UPDATE thetable 
  SET columntoupdate=yourvalue 
 FROM (SELECT rowid, 'thevalue' AS yourvalue 
         FROM thetable 
        ORDER BY rowid
      ) AS t1 
WHERE thetable.rowid=t1.rowid;

UPDATE命令的表现仍然是随机的（我猜），但是提供给UPDATE命令的值与thetable.rowid=t1.rowid条件匹配。所以我所做的是，首先在内存中选择“已更新”的表格，在上面的代码中它被命名为t1，然后使我的物理表格看起来与t1相同。这样更新顺序就不再重要。

至于真正有序的UPDATE，我认为对任何人都没用。

- alexkovelsky

---

这个在有序（排序）的方式下真的有效吗？能否通过某种日志或执行计划来证明呢？ - ALZ

当在不同的事务中并行执行相同或非常相似的“更新”语句时，访问行的顺序会不同，可能会导致死锁问题。 - ALZ

也许你可以尝试：CREATE thetable_copy AS TABLE thetable; UPDATE thetable SET columntoupdate=yourvalue FROM (SELECT rowid, 'thevalue' AS yourvalue FROM thetable_copy ORDER BY rowid) AS t1 WHERE thetable.rowid=t1.rowid; - alexkovelsky

Answer 2

UPDATE语句中的ORDER BY

针对标题提出的问题：SQL UPDATE命令中没有ORDER BY。Postgres以任意顺序更新行。但您可以（有限地）选择在每行，每个语句或事务结束后检查约束条件。您可以使用DEFERRABLE约束避免中间状态的重复键冲突。

我引用了我们在这个问题下工作出的结果：

• 约束定义为DEFERRABLE INITIALLY IMMEDIATE仍然是DEFERRED吗？

NOT DEFERRED约束在每行后进行检查。
设置为IMMEDIATE（默认为INITIALLY IMMEDIATE或通过SET CONSTRAINTS）的DEFERRABLE约束会在每个语句之后进行检查。

但是也有一些限制。外键约束需要目标列上的不可延迟约束。

所引用的列必须是所引用表中的非延迟唯一或主键约束的列。

解决方法

^{在问题更新后更新。}
假设"sequence"在正常操作中永远不会是负数，您可以避免类似于这样的唯一错误：

UPDATE tbl SET "sequence" = ("sequence" + 1) * -1
WHERE  "CableLine" = 2;

UPDATE tbl SET "sequence" = "sequence" * -1
WHERE  "CableLine" = 2
AND    "sequence" < 0;

对于非延迟约束（默认情况下），您需要运行两个单独的命令才能使其工作。请快速连续运行这些命令，以避免并发问题。很明显，这种解决方案不适用于高并发负载。

附注：
可以跳过表别名的关键字AS，但不建议对列别名做相同的操作。

我建议不要使用SQL关键字作为标识符，即使允许这样做。

避免问题

在更大规模或拥有高并发负载的数据库中，更明智的做法是使用serial列进行行的相对排序。您可以在视图或查询中使用窗口函数row_number()生成从1开始且没有间隔的数字。考虑以下相关答案:

• Is it possible to use a PG sequence on a per record label?

- Erwin Brandstetter

---

就此而言，在UPDATE ...中缺乏ORDER BY是一个真正的烦恼，并且使得在同时更新相同表格的查询之间避免死锁变得更加困难。能够为UPDATE指定一致的锁定顺序将会对某些工作负载的性能产生巨大的提升——排序成本会增加，但远不及所有查询重试所花费的。 - Craig Ringer

Answer 3

Update with Order By

Declare 
v number;
cursor c1 is 
    Select col2 from table1 order by col2;
    begin
    v:=0;
     for c in c1
     loop
    update table1 
    set col1 =v+1
    where col2 = c.col2;
    end loop;
    commit;
    END;

- Syed Najam ul Hassan

---

感谢您的想法。但是您的代码无法运行。任何人都可以从这里尝试：https://gist.github.com/JoshCheek/e19f83f271dc16d7825e2e4079538ba8 - ssi-anik

并相应地更新。以上代码的另一个问题是v+1，我不认为它会起作用。我尝试了v = v + 1。 - ssi-anik

Answer 4

“懒人方法”（也称为不是最快或最好的方法）

CREATE OR REPLACE FUNCTION row_number(table_name text, update_column text, start_value integer, offset_value integer, order_by_column text, order_by_descending boolean)
  RETURNS void AS
$BODY$
DECLARE
    total_value integer;
    my_id text;
    command text;
BEGIN
total_value = start_value;
    command = 'SELECT ' || order_by_column || ' FROM ' || table_name || ' ORDER BY '  || order_by_column;

    if (order_by_descending) THEN
        command = command || ' desc';
    END IF;

    FOR  my_id in  EXECUTE command LOOP
        command = 'UPDATE ' || table_name || ' SET  ' || update_column || ' = ' || total_value || ' WHERE ' || order_by_column || ' = ' ||  my_id|| ';';

        EXECUTE command;
        total_value = total_value + offset_value;
    END LOOP;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

示例

选择行号（'regispro_spatial_2010.ags_states_spatial'，'order_id'，10，1，'ogc_fid'，true）

- user3605589

Answer 5

如果有人像我一样因为重新排列postgresql表_id_seq从1开始并按id排序的问题而来到这里。我尝试的解决方案部分参考了@Syd Nazam Ul Hasan（以上）和https://gist.github.com/JoshCheek/e19f83f271dc16d7825e2e4079538ba8。

CREATE OR REPLACE FUNCTION update_sequence()
RETURNS SETOF varchar AS $$
DECLARE
  curs CURSOR FOR SELECT * FROM table ORDER BY id ASC;
  row  RECORD;
  v INTEGER := 0;
BEGIN
  open curs;
  LOOP
    FETCH FROM curs INTO row;
    update table 
    set id = v+1
    where id = row.id;
    v = v+1;
    EXIT WHEN NOT FOUND;
    return next row.id;
  END LOOP;
END; $$ LANGUAGE plpgsql;

SELECT update_sequence();

- ssi-anik

Answer 6

这个方法对我很有效:

[更新语句] OPTION (MAXDOP 1) -- 防止行大小导致使用急切的暂存区，从而破坏记录更新顺序。

我在按顺序使用聚集索引（如果需要则生成一个）时一直没有问题，直到最近出现了小行集上（违反直觉地）查询计划优化器决定使用惰性暂存区的情况。

理论上我可以使用新选项禁止使用暂存区，但我发现 maxdop 更简单实用。

我的情况比较特殊，因为这些计算是隔离的（单用户）。不同的情况可能需要使用替代方案来避免争用，而非使用 maxdop 限制。

- cmore