首页 文章

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

提问于
浏览 次
9

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

UPDATE table
    SET a_col = array[col];

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

我想出了这个游标代码，我认为这可能是好的 .

Edit: Added cursor function

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;
database postgresql cursor sql-update

1 回答

  • 15

    MVCC

    首先，如果"normal operations"包含 SELECT 个查询，MVCC model将自动处理它 . UPDATE 不会阻止 SELECT ，反之亦然 . SELECT 只能查看已提交的数据（或在同一事务中已执行的操作），因此大的 UPDATE 的结果在完成（提交）之前对其他事务保持不可见 .

    性能/膨胀

    如果您没有引用该表的其他对象，
    并且你没有并发写操作（会丢失！），
    你可以在 table 上买一个非常短的独家锁，
    而且你有额外的磁盘空间，当然：
    您可以通过在后台创建表的更新版本来将锁定保持在最低限度 . 确保它具备一切替代品，然后放下原件并重命名 .

    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) ，因为（quoting the manual here）：

    非空约束始终复制到新表 . 只有指定了INCLUDING CONSTRAINTS，才会复制CHECK约束;永远不会复制其他类型的约束 .

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

    DROP tbl_org;
ALTER TABLE tbl_new RENAME TO tbl_org;

    在非常短的时间窗口中生成结果，其中表格是专门锁定的 .

    这实际上只是关于性能 . 它创建了一个没有任何臃肿的新表 . 如果您有外键或视图，您仍然可以使用该路径，但您必须准备一个脚本来删除并重新创建这些对象，从而可能创建其他独占锁 .

    并发写入

    使用并发写入操作，您可以做的就是将更新分成块 . 您不能在单个事务中执行此操作，因为锁仅在事务结束时释放 .

    您可以使用dblink，它可以在另一个数据库上启动独立事务，包括它自己 . 这样你就可以在单个 DO 语句或带循环的plpgsql函数中完成所有操作 . 这是一个松散相关的答案，有关dblink的更多信息：

    您使用游标的方法

    函数内的光标不会给你带来任何东西 . 任何函数都自动包含在事务中，并且所有锁仅在事务结束时释放 . 即使您使用了CLOSE cursor（您没有使用），它也只会释放一些资源，但不会释放获取的锁定 . 我引用手册：

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

    您需要运行 separate transactions 或（ab）使用dblink为您执行此操作 .

    回复于

相关问题