PostgreSQL - 循环似乎已经异步了一点

Question

我的表 1 已过时，正在被表 2 取代。我的目标是将 table1 中的所有记录移动到 table2。为了避免在移动数据时发生 id 冲突，我运行了这个函数，如果匹配的 table2.id 已经存在，它会更新 table1.ids:

DO
$do$
DECLARE
    row RECORD;
BEGIN
  FOR row IN
    SELECT t1.id, t1.name, t2.id, t2.name
    FROM sch.table1 AS t1
    JOIN sch.table2 AS t2
    ON t1.id = t2.id
  LOOP
    UPDATE sch.table1
      SET id = (select greatest(
        (select max(id) from sch.table1),
        (select max(id) from sch.table2)) + 1)
      WHERE id = row.id;
    -- more code here that updates tables with table1.id as foreign key
  END LOOP;
END
$do$;

在大多数情况下，该功能按预期工作，正确整理了 100 多条记录。奇怪的是，它似乎只异步运行了一小段时间。在函数运行之前，我在 table1 中有这 8 条记录(我已经编辑了名称):

id  |name
----|-----------------------------
450 |Number Zero
451 |Number One
452 |Number Two
453 |Number Three
454 |Number Four
455 |Number Five
456 |Number Six
457 |Number Seven

函数运行后，这 8 条记录是:

id  |name
----|-----------------------------
1138|Number Zero
1139|Number One
1139|Number Two
1140|Number Three
1140|Number Four
1141|Number Five
1141|Number Six
1142|Number Seven

“一号”之前和“六号”之后的每条记录都相应更新。奇怪的是，6条记录被配对了。两条记录被分配了 ID 1139、1140 和 1141。我不知道这是怎么发生的，我需要知道才能找到避免这种情况的方法!

有什么想法吗？

附言。我在 DataGrip 上运行 SQL 代码，而 PostgreSQL 数据库位于 AWS 上。只是说明这一点，以防其中任何一个很重要。

Answer 1

您正在从正在更新的表中提取数据，并且提取(连接)基于您正在更新的列，该列可能是主键。 Postgres 在进入循环之前不得选择所有行。

您的代码中还有一些其他问题:

1. row.id 的值应该是多少； t1.id 还是 t2.id？您应该为 id 和 name 列使用明确的别名。
2. 在关系模型和 SQL 中，关系(表)没有特定的顺序。您应该使用明确的 ORDER BY 子句。如果这些行按照您希望的方式编号，那纯属巧合。
3. t2 的id 的最大值是循环的不变量。因此，您可以将循环外的最大值计算到一个变量中，并在循环内增加该变量的值。
4. 我看不出有任何理由加入循环，因为您似乎没有使用 table2 的值。所以你可以省略连接。

综合起来，您应该尝试类似的方法:

DO
$do$
DECLARE
    row RECORD;
    i INTEGER;
BEGIN
  i := greatest(
        (select max(id) from sch.table1),
        (select max(id) from sch.table2)) + 1;
  FOR row IN
    SELECT t1.id, t1.name FROM sch.table1 AS t1 ORDER BY ...
  LOOP
    UPDATE sch.table1 SET id = i WHERE id = row.id;
    i := i + 1;
  END LOOP;
END
$do$;

但是总是有问题，就是更新table1的主键。如果此代码不起作用，您应该将主键保存在临时列中:

ALTER TABLE table1 ADD old_id INTEGER;
UPDATE table1 SET old_id = id;
DO
$do$
DECLARE
    row RECORD;
    i INTEGER;
BEGIN
  i := greatest(
        (select max(id) from sch.table1),
        (select max(id) from sch.table2)) + 1;
  FOR row IN
    SELECT t1.old_id, t1.name FROM sch.table1 AS t1 ORDER BY ...
    -- if you still need the join here it should use t1.old_id instead of t1.id
  LOOP
    UPDATE sch.table1 SET id = i WHERE id = row.old_id;
    i := i + 1;
  END LOOP;
END
$do$;
ALTER TABLE table1 DROP COLUMN old_id;