在 PostgreSQL 9.2 中更新数据库行而不锁定表

Question

尝试使用PostgreSQL 9.2在表上运行这样的更新语句：

UPDATE table
    SET a_col = array[col];

我们需要能够在 ~10M 行表上运行它，而不是让它锁定表（因此在更新运行时仍然可以进行正常操作）。我相信使用游标可能是正确的解决方案，但我真的不知道它是否是正确的，或者我应该如何使用游标实现它。

我想出了这个光标代码，我认为这可能很好。

编辑：添加光标功能

CREATE OR REPLACE FUNCTION update_fields() RETURNS VOID AS $$
DECLARE
        cursor CURSOR FOR SELECT * FROM table ORDER BY id FOR UPDATE;
BEGIN
        FOR row IN cursor LOOP
                UPDATE table SET
                        a_col = array[col],
                        a_col2= array[col2]
                WHERE CURRENT OF cursor;
        END LOOP;
END;
$$ LANGUAGE plpgsql;

Answer 1

首先，如果“正常操作”由 SELECT 查询组成，MVCC 模型将自动处理它。UPDATE 不会阻止 SELECT，反之亦然。SELECT 只看到已提交的数据（或在同一事务中执行的操作），因此大 UPDATE 的结果在完成（提交）之前对其他事务不可见。

如果没有其他对象引用该表，
并且没有并发写入操作（这将丢失！）
而且您可以在表上提供一个非常短的独占锁，
并且您当然有额外的磁盘空间：
您可以通过在后台创建表的更新版本来将锁定保持在最小。确保它有所有可以替换的东西，然后删除原始文件并重命名重复文件。

CREATE TABLE tbl_new (LIKE tbl_org INCLUDING CONSTRAINTS);

INSERT INTO tbl_new 
SELECT col_a, col_b, array[col] aS col_c
FROM   tbl_org;

我正在使用创建表（如..包括约束），因为（在此处引用手册）：

非空约束总是被复制到新表中。只有在指定了< code > INCLUDING CONSTRAINTS 时，才会复制< code>CHECK约束；其他类型的约束永远不会被复制。

确保新桌子准备好了。然后:

DROP tbl_org;
ALTER TABLE tbl_new RENAME TO tbl_org;

导致非常短的时间窗口，其中表以独占方式锁定。

这实际上只是关于性能。它会很快地创建一个没有任何膨胀的新表。如果你有外键或视图，你仍然可以走这条路，但你必须准备一个脚本来删除和重新创建这些对象，可能会创建额外的独占锁。

对于并发写操作，您真正能做的就是将您的更新拆分成块。您不能在单个事务中做到这一点，因为锁只在事务结束时释放。

您可以使用dblink，它可以在另一个数据库（包括数据库本身）上启动独立的事务。这样，您可以在一个do语句或一个带循环的plpgsql函数中完成所有操作。下面是一个松散相关的答案，其中包含有关dblink的更多信息：

• 从PostgreSQL中的存储过程删除或创建数据库

函数中的光标不会给你带来任何东西。任何函数都自动包含在一个事务中，所有的锁只在事务结束时释放。即使您使用了< code>CLOSE cursor(实际上您并没有这样做)，它也只会释放一些资源，而不会释放表上获得的锁。我引用手册上的话:

< code>CLOSE关闭打开的光标下的门户。这可用于在事务结束之前释放资源，或者释放游标变量以便再次打开。

您需要运行单独的事务或（滥用）使用为您执行此操作的dblink。