Large selects handled inefficiently?

From: Jules Bean <jules(at)jellybean(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Large selects handled inefficiently?
Date: 2000-08-30 11:30:40
Message-ID: 20000830123039.A15150@grommit.office.vi.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hiya,

I am running a very large SELECT - it selects every row from a ~10 000
000 row table. I'm running this in Perl using DBD:Pg, with the general
sequence:

$sth = $dbh->prepare("SELECT $fields FROM $from") || return 0;
$fh = new FileHandle(">$file") || die "Can't open $file : $!";
$sth->execute() || return 0;
$sth->bind_columns(undef,\(@fields));
while ($sth->fetch){
print $fh join("\t",@fields),"\n";
}
if ($sth->err) { return 0; }
close $fh;

...which works fine in most circumstance, but on this really big
select:

* the client process's memory gradually climbs to around 1 gigabyte
(ouch!)
* eventually the stdout of the process shows over and over again:

Backend sent D message without prior T

It seems as if the client is fetching the /whole/ query result, or
trying to, all at once. I expected it to only actually fetch result
rows as I called fetch().

Is this:

* A deficiency in DBD::Pg?
* A deficiency in the postgresql client libraries?
* A silly bug in my code?

I believe I can work around this problem using cursors (although I
don't know how well DBD::Pg copes with cursors). However, that
doesn't seem right -- cursors should be needed to fetch a large query
without having it all in memory at once...

Jules

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Trurl McByte 2000-08-30 11:35:37 SSL support autoconfiguration troubles
Previous Message Sander Steffann 2000-08-30 09:57:35 Re: 7.1 Release Date