Re: out of memory for query result - Mailing list pgsql-general

Thanks, everyone. I got it to work! Here is my solution hoping it is
useful to the next programmer.

PROBLEM: Perl DBI for Postgres does not implement cursors. All query
results are cached in memory. For very large result sets, this give the
"out of memory for query result" message.

The prepare(select_statement)/execute(@parms) did not open a cursor as I
had thought. It must be explicitly coded. This technique is only
applicable for processing large result sets that you do not want cached
completely in memory. It may not work for nested cursors?

SOLUTION: Run raw "DECLARE CURSOR" and "FETCH nnn FROM cursor" commands
to extract your data.
* Connect with AutoCommit=>0 to enable transactions
* prepare/execute DECLARE cursorname CURSOR FOR select...
   ? Parameters to the SQL are specified here.
* Loop
   * prepare/execute FETCH nnn FROM cursor_name,
     which buffers only the next 'nnn' rows from the cursor
     use a large enough number to decrease server/client overhead
     and small enough to co-exist with other apps/threads.
   * Loop
     * fetchrow_hashref until undef (end of current FETCH set)
     * do something wonderful with the row
* prepare/execute Close Cursor

EXAMPLE: This may not be the cleanest code, but works!

#!/usr/local/bin/perl -w
use strict;
use DBI;

my $dbName='allen';
my $host='localhost';
my $dbUser=$dbName;
my $dbPassword='';

my $dbh = DBI->connect("DBI:Pg:dbname=$dbName;host=$host", $dbUser,
$dbPassword,
         { RaiseError => 0, AutoCommit => 0, PrintError => 1 })
         or die "Can't connect to db\n";

loopCursor(
   sub
   {
     my ($row) = @_;
     print "$row->{name}\n";
   },
   "SELECT name from population where ssn>=?",1
);
$dbh->commit();
$dbh->disconnect();
exit;

my $csrnum=0;
sub loopCursor
{
   my ($proc, $sql, @parms) = @_;
   my ($sth, $row);
   ++$csrnum;
   my $count=0;
   eval {
     runSQL("declare csr_$csrnum cursor for $sql", @parms);
     for(;;) {
        $sth = $dbh->prepare("fetch 1000 from csr_$csrnum")
           or die "fetch 1000 from csr  $DBI::errstr\n";
        $sth->execute() or die "loopCursor fetch  $DBI::errstr\n";
        last if $sth->rows == 0;

        while ($row = $sth->fetchrow_hashref) {
         ++$count;
         &$proc($row);
        }
        $sth->finish();
     }
     runSQL("close csr_$csrnum");
     return $count;
   };
   die join(' ', "Error $@ during", $sql, @parms,
         $DBI::errstr||'',"\n") if $@;
}

sub runSQL
{
   my ($sql, @parms) = @_;
   my $sth;
   eval {
     $sth = $dbh->prepare($sql);
     $sth->execute(@parms) or die $DBI::errstr;
   };
   die "Error $@ during $sql @parms\n" if $@;
   $sth->finish();
   return $sth->rows;
}