PostgreSQL 存储过程中的 COMMIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2305431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COMMIT in PostgreSQL stored procedure
提问by Adam Matan
I have a PostgreSQL stored procedure which loops over a very large list, and makes changes to some of its members using UPDATE.
我有一个 PostgreSQL 存储过程，它循环遍历一个非常大的列表，并使用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.
有没有办法在每次迭代而不是在函数执行结束时提交这些更改？它可以让我在短时间内运行该功能，在每次运行时进行小的更改。
Thanks,
谢谢，
Adam
亚当
采纳答案by Magnus Hagander
No, it's currently not supported to open or close transactions inside a stored procedure, no.
不，目前不支持在存储过程中打开或关闭事务，不。
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.
如果确实如此，顺便说一句，在每次迭代后提交会使事情变慢。您必须至少分批提交 10,000 或 100,000 次更新。正如评论中所说，真正的胜利当然不是运行这种 ISAM 风格，而是想办法将其编写为单个查询。
回答by mpccolorado
There is a cost per statement, so if you can write your function to do less statements, you're better off...
每个语句都有成本，因此如果您可以编写函数以执行更少的语句，那么您最好...
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
Replace the whole loop with a single INSERT statement:
用一个 INSERT 语句替换整个循环：
INSERT INTO TABLE2 SELECT all.id, some.id FROM all, some WHERE...
But beware of the size of the list you are going to update. We had a similar problem, we have to create a lot of tables dynamically and insert a lot of data in them. Firstly we create a stored procedure and loops over a list of months and years and create a single table for every month, but it collapse in a single stored procedure. So, we create the stored procedure but we don't loops in there, instead we loops outside the stored procedure and it works.
但要注意您要更新的列表的大小。我们遇到了类似的问题，我们必须动态创建很多表并在其中插入大量数据。首先，我们创建一个存储过程并循环遍历月份和年份列表，并为每个月创建一个表，但它在单个存储过程中崩溃。所以，我们创建了存储过程，但我们不在那里循环，而是在存储过程之外循环，它可以工作。
