Perl的DBD :: Pg Postgresql绑定将始终获取查询的整个结果集.因此,如果您使用简单的prepare执行来遍历大型表,那么只需运行$sth-> execute()就可以将整个表结束在内存中.准备好的语句和fetch_row之类的调用没有帮助.
如果您正在使用BIG表,以下将失败.
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=big_db","user","password",{
AutoCommit => 0,ReadOnly => 1,PrintError => 1,RaiseError => 1,});
my $sth = $dbh->prepare('SELECT * FROM big_table');
$sth->execute(); #prepare to run out of memory here
while (my $row = $sth->fetchrow_hashref('NAME_lc')){
# do something with the $row hash
}
$dbh->disconnect();
解决方法
要解决此问题,请声明游标.然后使用游标获取数据块. ReadOnly和AutoCommit设置对于此工作非常重要.由于Postgresql只会进行CURSORS阅读.
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=big_db",});
$dbh->do(<<'sql');
DECLARE mycursor CURSOR FOR
SELECT * FROM big_table
sql
my $sth = $dbh->prepare("FETCH 1000 FROM mycursor");
while (1) {
warn "* fetching 1000 rows\n";
$sth->execute();
last if $sth->rows == 0;
while (my $row = $sth->fetchrow_hashref('NAME_lc')){
# do something with the $row hash
}
}
$dbh->disconnect();