COMMIT in PostgreSQL stored procedure

Question

我有一个 PostgreSQL 存储过程，它循环遍历一个非常大的列表，并使用 UPDATE 更改其中一些成员。

是否有一种方法在每次迭代时提交这些更改，而不是在函数执行结束时进行提交？这将允许我短时间运行函数，在每次运行时进行小的更改。

谢谢,

Adam

- Adam Matan

---

真正的性能优化是使用单个“update”语句，而不是在“cursor”或通过其他方法循环整个列表。在您的情况下是否不可能实现？ - yfeldblum

可能是这样，但我仍然对这个问题很好奇。 - Adam Matan

从PostgreSQL 11开始，可以使用PROCEDURE代替FUNCTION，并且允许在存储过程中进行提交。 - Daniel Vérité

Answer 1

不，目前不支持在存储过程中打开或关闭事务。

如果支持的话，在每次迭代后提交会使速度变慢很多。您至少需要以10,000或100,000个更新为一批进行提交。正如评论所说，真正的胜利当然不是以ISAM样式运行，而是想出某种方法将其编写为单个查询。

- Magnus Hagander

---

非常感谢，我可能会改变我的方法。 - Adam Matan

Answer 2

每个语句都有成本，因此如果您可以编写能够执行更少语句的函数，那么您会更加优秀...

FOR all IN (select * from TABLE1)
LOOP
    FOR some IN (select * from)
    LOOP
        INSERT INTO TABLE2 VALUES (all.id, some.id)
    END LOOP
END LOOP

使用单个INSERT语句替换整个循环：

INSERT INTO TABLE2 SELECT all.id, some.id FROM all, some WHERE...

但要注意您将要更新的列表大小。 我们曾经遇到过类似的问题，需要动态创建许多表并在其中插入大量数据。首先，我们创建了一个存储过程，并循环遍历月份和年份列表，为每个月创建一个单独的表，但它在一个存储过程中崩溃了。 因此，我们创建了存储过程，但不在其中循环，而是在存储过程外部循环，这样就可以正常工作。

- mpccolorado