如何防止在pl/SQL循环中创建数百万个表时出现“内存不足”异常？

Question

在PostgreSQL中，我有一个相当复杂的任务要解决。本质上，我需要对一个10的数组执行一个pl/pgsql循环，并对一个大约一百万的数组执行一个嵌套循环。根据每个数据条目的条件，我必须创建大约5-8个表。这导致一个事务中的表的总数约为5,000-8,000万个表。我需要创建表，以便为其他SELECT计算创建索引，如果没有它，性能就不会很好。然而，所有这一切的结果实际上是只有一个id表，这就是为什么在循环中i CREATE的所有表在每次迭代结束时也会被DROP。

问题是我得到了一个out of memory错误。我不知道如何重写我的代码，也许可以将它拆分成独立的事务或类似的东西。我有Postgres版本10.14，目前无法升级。

有人知道如何解决这个问题吗？

Answer 1

我不知道如何重写我的代码，也许可以将它拆分成单独的事务

您可以使用存储过程而不是函数。过程可以使用事务，只需在代码中使用begin和commit即可。所以在你的循环中，你可以这样做：

BEGIN
CREATE TEMPORARY TABLE (...) ON COMMIT DROP
...do your thing with the temp table...
INSERT the result into a more permanent table
COMMIT

确保使用临时表，这样会更快。

但是，如果您想在临时表上使用索引，那么您可能还需要预先分析它们，在这种情况下，您可以使用ANALYZE，但不能使用VACUUM ANALYZE，因为这将提交您的事务，并且如果表是用on commit DROP创建的，那么在分析之前它将被删除...

现在，老实说，创建数百万个表是非常可疑的，所以如果你问一个问题来解释你到底想要做什么，也许会有一个更简单的解决方案。