在PostgreSQL 9.2中无需锁定表格即可更新数据库行

Question

在PostgreSQL 9.2中无需锁定表格即可更新数据库行

9

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

UPDATE table
    SET a_col = array[col];

我们需要能够在大约10万行的表格上运行此代码，并且在更新过程中不会锁定表格（因此正常操作仍然可以进行）。我认为使用游标可能是正确的解决方案，但我真的不知道如何使用游标实现它。

我提供了以下游标代码，我认为这可能是好的。

编辑：添加了游标函数

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;

- Juan Carlos Coto

是否有其他引用该表的对象，例如视图的外键？ - Erwin Brandstetter

@ErwinBrandstetter 没有外键。 - Juan Carlos Coto

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

MVCC

首先，如果“正常操作”包括 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;

我正在使用CREATE TABLE (LIKE .. INCLUDING CONSTRAINTS)，因为（引用手册）：

非空约束始终会复制到新表中。CHECK约束只有在指定INCLUDING CONSTRAINTS时才会被复制；其他类型的约束永远不会被复制。

确保新表已准备就绪。然后：

DROP tbl_org;
ALTER TABLE tbl_new RENAME TO tbl_org;

在一个非常短的时间窗口内显示结果，其中表被独占地锁定。

这实际上只涉及性能。它可以快速创建一个没有任何膨胀的新表。如果您有外键或视图，您仍然可以选择这种方法，但是您需要准备一个脚本来删除并重新创建这些对象，可能会创建额外的独占锁。

并发写入

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

你可以使用dblink在另一个数据库上启动独立的事务，包括本身。这样，你可以在一个单独的DO语句或一个带有循环的plpgsql函数中完成所有操作。这里是一个不太相关的答案，提供了有关dblink的更多信息:

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

使用游标的方法

函数内的游标不会为你带来任何收益。任何函数都会自动封装在一个事务中，而所有锁都只在事务结束时释放。即使您使用CLOSE cursor(实际上没有这么做)，它也只会释放一些资源，但是不会释放对表获得的锁定。我引用一下手册:

CLOSE关闭底层打开的游标。这可以用于在事务结束之前释放资源，或者释放游标变量以再次打开。

您需要运行单独的事务或滥用dblink来完成操作。