更新数据库行，而不锁定PostgreSQL 9.2中的表 [英] Updating database rows without locking the table in PostgreSQL 9.2

问题描述

尝试使用PostgreSQL 9.2在表格上运行此类更新语句：

Trying to run an update statement like this on a table, using PostgreSQL 9.2:

UPDATE table
    SET a_col = array[col];

我们需要能够在〜10M行表上运行它，该表（所以正常操作仍可能发生，而更新正在运行）。我相信使用游标可能是正确的解决方案，但我真的不知道它是否是如何使用光标实现它。

We need to be able to run this on a ~10M row table, and not have it lock up the table (so normal operations can still happen while the update is running). I believe using a cursor will probably be the right solution, but I really have no idea if it is or how I should implement it using a cursor.

编辑：添加光标功能 b

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模型将自动处理它。 UPDATE 不会阻止 SELECT ，反之亦然。 SELECT 只能看到提交的数据（或者在同一事务中做了什么），所以大 UPDATE

MVCC

First off, if "normal operations" consist of SELECT queries, the MVCC model will take care of it automatically. UPDATE does not block SELECT and vice versa. SELECT only sees committed data (or what's been done in the same transaction), so the result of the big UPDATE remains invisible to other transactions until it's done (committed).

如果

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
其他类型的约束将不会被复制。

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.

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

Make sure, the new table is ready. Then:

DROP tbl_org;
ALTER TABLE tbl_new RENAME TO tbl_org;

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

Results in an very short time window, where the table is locked exclusively.

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

This is really only about performance. It creates a new table without any bloat rather quickly. If you have foreign keys or views, you can still go that route, but you have to prepare a script to drop and recreate these objects, potentially creating additional exclusive locks.

对于并发写操作，真正可以做的是，以块为单位分割更新。

With concurrent write operations, really all you can do, is split your update in chunks. You can't do that in a single transaction, since locks are only released at the end of a transaction.

您可以 雇用 dblink ，可以在另一个数据库上启动独立事务，包括自身。这样，你可以在一个单独的 DO 语句或带循环的plpgsql函数中执行此操作。这是一个松散的相关答案，有关dblink的更多信息：

You could employ dblink, which can launch independent transactions on another database, including itself. This way you could do it all in a single DO statement or a plpgsql function with a loop. Here is a loosely related answer with more information on dblink:

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

• Drop or create database from stored procedure in PostgreSQL

函数内的光标不会向您购买任何 。任何函数都自动包含在事务中，所有锁仅在事务结束时释放。
即使您使用 CLOSE游标 （您不需要），它只会释放一些资源，但不会释放表上获取的锁。我引用手册：

A cursor inside the function will not buy you anything. Any function is enclosed in a transaction automatically, and all locks are only released at the end of the transaction. Even if you used CLOSE cursor (which you don't) it would only free some resources, but not release acquired locks on the table. I quote the manual:

关闭关闭打开的光标下面的门户。这可以用于
早于事务结束释放资源，或释放
光标变量以再次打开。

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 。

这篇关于更新数据库行，而不锁定PostgreSQL 9.2中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！