Re: out of memory for query result

[Allen <dba@xxxxxxxxxxx>]

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;
}


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster