How to walk through a large result set with perl and postgresql

user_235990 0
Viewed 630

The DBD::Pg PostgreSQL bindings for Perl will always fetch the entire result set of your query. So if you are using a simple prepare execute to walk through a large table you will end up with the whole table in memory just by running $sth->execute(). Prepared statements and calls like fetch_row don't help.

The following will fail miserably if you are working with a BIG table.

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();
large-data dbi database-cursor perl postgresql
1 Answers

To work around this problem, declare a cursor. And then fetch blocks of data using the cursor. The ReadOnly and AutoCommit settings are important for this to work. Since PostgreSQL will only do CURSORS for reading.

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 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();
user_2359900
Related