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

尝试使用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;

解决方法

MVCC

首先,如果“正常操作”由SELECT查询组成,MVCC model将自动处理它. 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 ..包括CONSTRAINTS),因为(quoting the manual here)：

Not-null constraints are always copied to the new table. CHECK
constraints will only be copied if INCLUDING CONSTRAINTS is specified;
other types of constraints will never be copied.

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

DROP tbl_org;
ALTER TABLE tbl_new RENAME TO tbl_org;

结果在一个非常短的时间窗口中,表格被锁定.

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

并发写道

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

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

> Drop or create database from stored procedure in PostgreSQL

你使用游标的方法

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

CLOSE closes the portal underlying an open cursor. This can be used to
release resources earlier than end of transaction,or to free up the
cursor variable to be opened again.

您需要运行单独的事务或(ab)使用dblink为您执行此操作.