PostgreSQL存储过程中的COMMIT [英] COMMIT in PostgreSQL stored procedure

问题描述

我有一个PostgreSQL存储过程，该过程在一个很大的列表上循环，并使用 UPDATE 对其某些成员进行更改。

I have a PostgreSQL stored procedure which loops over a very large list, and makes changes to some of its members using UPDATE.

是否可以在每次迭代中而不是在函数执行结束时提交这些更改？它将使我能够在短时间内运行该函数，并在每次运行时进行较小的更改。

Is there a way to commit these changes per iteration, not at the end of the function execution? It would allow me to run the function for shorts periods of time, making small changes at each run.

谢谢，

Adam

推荐答案

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

No, it's currently not supported to open or close transactions inside a stored procedure, no.

如果这样做的话，顺便说一句，在每次迭代之后进行提交会使事情变慢很多。您至少必须批量提交10,000或100,000个更新。正如评论中所说，真正的胜利当然不是运行这种ISAM样式，而是想出某种方式将其作为单个查询编写。

If it did, btw, committing after each iteration would make things a lot slower. You'd have to at least commit in batches of 10,000 or 100,000 updates. And as was said in the comments, the real win is of course not to run this ISAM style but to figure out some way to write it as a single query.

这篇关于PostgreSQL存储过程中的COMMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！