sql - 在不锁定表的情况下循环遍历 PL/PGSQL 中的 CURSOR

Question

我有一个简单的 PL/PGSQL block Postgres 9.5，它循环遍历表中的记录并有条件地更新一些记录。

这是一个简化的例子:

DO $$
  DECLARE

    -- Define a cursor to loop through records
    my_cool_cursor CURSOR FOR
    SELECT
      u.id          AS user_id,
      u.name        AS user_name,
      u.email       AS user_email
    FROM users u
    ;

  BEGIN

    FOR record IN my_cool_cursor LOOP

      -- Simplified example: 
      -- If user's first name is 'Anjali', set email to NULL
      IF record.user_name = 'Anjali' THEN
        BEGIN
          UPDATE users SET email = NULL WHERE id = record.user_id;
        END;
      END IF;

    END LOOP;
  END;

$$ LANGUAGE plpgsql;

我想直接对我的数据库执行这个 block (从我的应用程序，通过控制台等...)。我不想创建FUNCTION() 或存储过程来执行此操作。

问题

问题是 CURSOR 和 LOOP 在我的 users 表上创建了表级锁，因为外部 之间的所有内容>BEGIN...END 在事务中运行。这会阻止任何其他针对它的未决查询。如果 users 足够大，这会将其锁定几秒钟甚至几分钟。

我尝试了什么

我尝试在每次UPDATE 之后COMMIT，以便定期清除事务和锁。我很惊讶地看到这条错误信息:

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.

我不太清楚这是怎么做到的。是否要求我引发 EXCEPTION 以强制执行 COMMIT？我试着阅读 the documentation on Trapping Errors但它只提到了ROLLBACK，所以我看不到任何方法可以COMMIT。

1. 如何在上面的 LOOP 中定期COMMIT 事务？
2. 更一般地说，我的方法是否正确？有没有更好的方法在不锁定表的情况下循环遍历记录？

Answer 1

1.

您不能在 PostgreSQL 中COMMIT function或 DO命令 at all(plpgsql 或任何其他 PL)。您报告的错误消息很重要(就 Postgres 9.5 而言):

ERROR:  cannot begin/end transactions in PL/pgSQL

A procedure可以在 Postgres 11 或更高版本中做到这一点。见:

• PostgreSQL cannot begin/end transactions in PL/pgSQL
• In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?

在旧版本中实现“自主事务”的解决方法有限:

• How do I do large non-blocking updates in PostgreSQL?
• Does Postgres support nested or autonomous transactions?
• Do stored procedures run in database transaction in Postgres?

但是对于所呈现的案例，您不需要任何这些。

2.

改用简单的UPDATE:

UPDATE users
SET    email = NULL
WHERE  user_name = 'Anjali'
AND    email IS DISTINCT FROM NULL;  -- optional improvement

只锁定实际更新的行(边角情况除外)。由于这比整个表上的 CURSOR 快多，因此锁定也非常短暂。

添加的 AND email IS DISTINCT FROM NULL 避免了空更新。相关:

• Update a column of a table with a column of another table in PostgreSQL
• How do I (or can I) SELECT DISTINCT on multiple columns?

很少有显式游标在 plpgsql 函数中有用。