需要很长时间的批量/多个查询在 Postgres 中永远不会完成
我在 postgres 中创建了一个将数据插入表中的函数。该函数接收一个字符串，并在处理后将值输入到表中。我想要的是能够在 pgAdmin 中多次调用该函数，而不是批量运行它们。
例子： 在 pgAdmin 中，我打开 sql 查询窗口并输入
select customfunction('string1');
select customfunction('string2');
^
.
.
.
.
v
select customfunction('string100000000');
当我尝试运行它时，它仅在我运行大约 6000 行（6000 个函数）时才有效。如果我在运行时取消它，它不会提交任何内容（甚至不提交第一行）。我必须不断地将它们分成批次才能完成整个查询。我怎样才能在不批量运行它们的情况下实现这一目标。
我想复制并粘贴整个查询并等待一切完成。现在，如果我批量运行超过 10000 行，查询就会无限期地运行，没有任何响应。 postgres 有没有办法在运行该行后提交该行？
我还想知道是否有人可以准确确定查询运行时正在处理哪一行。
I have created a function in postgres that inserts data into a table. The function receives a string and enters the value into a table after processing it. What I want is to be able to call the function numerous times in pgAdmin instead of running them in batches.
Example:
in pgAdmin i open up my sql query window and type
select customfunction('string1');
select customfunction('string2');
^
.
.
.
.
v
select customfunction('string100000000');
When I try to run this it only works when I run about 6000 lines (6000 functions). If i cancel it while it is running it doesnt commit anything (not even the first line). I have to keep cutting them into batches to complete the entire query. How can I achieve this without running them in batches.
I want to copy and paste the whole query and wait till everything is done. Right now the query just runs with no response indefinitely if i run over 10000 lines in a batch. Is there a way postgres can commit each line once it runs that line?
I would also like to know if anyone can determine exactly what line is being processed while the query is running.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果启用自动提交，它就不会这样做 - 这也是默认设置，所以...您是否在调用之前添加了 begin 语句？如果是这样，请在拨打电话之前提交...
另外，请考虑准备声明或按照杰克的建议进行。
It won't do that if auto-commit is enabled - that is the default, too, so... Are you preceding the calls with a begin statement? If so commit before making your calls...
Also, consider preparing the statement or doing what jack suggested.
您是否尝试过将字符串批处理到文件，将文件导入到表中（我将其称为 string_table，其中包含一个名为 string 的单列），然后尝试：
ETA：如果您想要某种进度指示器并且需要在以下位置运行一个选择有时，您可以使用脚本语言中的 DBI 从文件或表中读取字符串，在循环中迭代函数调用，然后在每次迭代时执行简单的控制台打印输出。
Have you tried batching your strings to file, importing the file into a table (which I'll call string_table with a single column called string), and trying:
ETA: If you want some kind of progress indicator and need to run one select at a time, you could use a DBI in a scripting language to read the strings from either a file or a table, iterate through the function calls in a loop, and then do a simple console printout with each iteration.