perl的 DBD::Pg
postgresql绑定将始终获取查询的整个结果集。因此,如果您使用简单的准备执行来穿过一张大表格,则只需运行$sth->execute()
即可将整个桌子放在内存中。准备的陈述和诸如fetch_row
之类的电话无济于事。
,如果您正在使用大桌子,以下会失败。
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();
解决此问题,声明光标。然后使用光标获取数据块。阅读和自动兼容设置对此至关重要。由于PostgreSQL只能进行阅读的光标。
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=big_db","user","password",{
AutoCommit => 0,
ReadOnly => 1,
PrintError => 1,
RaiseError => 1,
});
$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 rowsn";
$sth->execute();
last if $sth->rows == 0;
while (my $row = $sth->fetchrow_hashref('NAME_lc')){
# do something with the $row hash
}
}
$dbh->disconnect();