从大型 PostgreSQL 表中选择数据时 Perl 脚本失败

Question

我正在尝试在 PostgreSQL 数据库上运行 SELECT 语句并将其结果保存到文件中。

代码在我的环境中运行，但一旦我在轻量级服务器上运行它就会失败。

我监控了一下，几秒钟后失败的原因是内存不足(机器只有 512MB RAM)。我没想到这会成为一个问题，因为我只想将整个结果集作为 JSON 文件保存在磁盘上。

我计划使用 fetchrow_array 或 fetchrow_arrayref 函数，希望一次只获取和处理一行。

不幸的是，我发现在使用 DBD::Pg 时，上述两者与 fetchall_arrayref 之间的真正提取操作没有区别。我的脚本在 $sth->execute() 调用时失败，甚至在它有机会调用任何 fetch... 函数之前。

这向我表明 DBD::Pg 中的 execute 的实现实际上将所有行提取到内存中，只留下返回给 的实际格式>获取... 函数。

快速浏览 DBI documentation给出提示:

If the driver supports a local row cache for SELECT statements, then this attribute holds the number of un-fetched rows in the cache. If the driver doesn't, then it returns undef. Note that some drivers pre-fetch rows on execute, whereas others wait till the first fetch.

所以理论上我只需要设置 RowCacheSize parameter .我试过但是这个功能doesn't seem to be implemented by DBD::Pg

Not used by DBD::Pg

我发现这个限制是一个巨大的一般性问题(execute() 调用预取 所有 行？)并且更倾向于相信我在这里遗漏了一些东西，这实际上是使用 Perl 与 PostgreSQL 数据库交互的真正限制。

---

更新 (2014-03-09):由于使用了我对 Borodin 的回答的评论中描述的变通方法，我的脚本现在可以运行了。 DBD::Pg 库的维护者就这个问题回复了我，实际上说根本原因更深，在 libpq postgresql 内部库(由 DBD 使用)::Pg).此外，我认为与此处描述的问题非常相似的问题会影响 pgAdmin。作为 postgresql native 工具，它仍然没有在 Options 中提供定义结果集行大小的默认限制的机会。这可能就是为什么它使 Query tool 有时会等待很长时间才能显示大量查询的结果，在某些情况下也可能破坏应用程序。

Answer 1

在section Cursors ，数据库驱动程序的文档是这样说的

Therefore the "execute" method fetches all data at once into data structures located in the front-end application. This fact must to be considered when selecting large amounts of data!

所以你的假设是正确的。然而，同一部分继续描述您可以如何在 Perl 应用程序中使用游标来读取 block 中的数据。我相信这会解决您的问题。

另一种方法是在 SELECT 语句中使用 OFFSET 和 LIMIT 子句来模拟游标功能。如果你写

my $select = $dbh->prepare('SELECT * FROM table OFFSET ? LIMIT 1');

然后你可以这样说(所有这些都未经测试)

my $i = 0;
while ($select->execute($i++)) {
  my @data = $select->fetchrow_array;
  # Process data
}

一次一行地阅读您的表格。

您可能会发现需要增加 block 大小才能获得可接受的效率水平。