我有一个简单的PL/PGSQL块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;字符串
我想直接对我的数据库执行此块（从我的应用程序，通过控制台等）。我不想创建FUNCTION()或存储过程来执行此操作。
问题
问题是CURSOR和LOOP在我的users表上创建了一个表级锁，因为外部BEGIN...END之间的所有内容都在一个事务中运行。这会阻止任何其他针对它的挂起查询。如果users足够大，这会锁定它几秒钟甚至几分钟。
What I tried
我尝试在每次UPDATE之后COMMIT，以便定期清除事务和锁。我很惊讶地看到这个错误消息：
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.型
我不太确定这是怎么做到的。它是不是要求我引发一个EXCEPTION来强制一个COMMIT？我试着阅读关于捕获错误的文档，但它只提到了ROLLBACK，所以我看不到任何方法来COMMIT。
1.我如何定期COMMIT一个交易里面的LOOP以上？
1.更一般地说，我的方法是正确的吗？有没有更好的方法来循环记录而不锁定表？
2条答案
按热度按时间uxhixvfz1#
如果你想避免长时间锁定行，你也可以定义一个游标
WITH HOLD，例如使用DECLARESQL语句。这样的游标可以跨事务边界使用，所以在一定数量的更新之后，您可以
COMMIT。您所付出的代价是游标必须在数据库服务器上实现。由于不能在函数中使用transaction语句，因此必须在应用程序代码中使用过程或提交。
dzhpxtsq2#
1.
你不能在PostgreSQL
FUNCTION中使用COMMIT，也不能在PostgreSQL 11**之前的DO命令中使用COMMIT（使用PL/pgSQL或任何其他PL）。这导致了你报告的错误（对于Postgres 9.5）：字符串
Postgres 11或更高版本中的
PROCEDURE或DO语句可以是COMMIT。请参阅：在旧版本中，实现“自治事务”的变通方法有限：
但是，对于所呈现的案例，您不需要任何这些。
2.
使用简单的
UPDATE代替：型
只锁定实际更新的行（有例外情况）。由于这比整个表的
CURSOR快得多，所以锁也非常简短。新增的
AND email IS DISTINCT FROM NULL避免了空更新。我很少在PL/pgSQL函数中使用显式游标。