From: | Viktor Bojović <viktor(dot)bojovic(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | overload |
Date: | 2011-07-05 20:29:03 |
Message-ID: | CAJu1cLYAbwDdKZpqncv4o-cOifKCipXtx2-3n9h-Pz4QBQuTwA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
while reading 20GB table through PL/PERL function , it constantly grows in
RAM.
I wanted to ask you which is the best way to read table inside that
function without such memory consumption.
Thanks in advance
Code is here:
CREATE FUNCTION pattern_counter("patLength" integer)
RETURNS varchar AS
$BODY$
my $rv = spi_exec_query("select sequence from entry");
my $rowCount = $rv->{processed};
my $patLen = $_[0];
my $patt = '';
my %patterns=();
foreach my $rn (0 .. $rowCount -1){
my $row = $rv->{rows}[$rn];
my $seq = $row->{sequence};
for (my $x = 1;$x<=length($seq) - $patLen;$x++){
$patt=substr($seq,$x,$patLen);
if (! defined $patterns{$patt}) {
$patterns{$patt}=1;
}else{
$patterns{$patt}++;
}
}
}
foreach $patt (keys %patterns){
my $sql="insert into patterns values('".$patt."',".$patterns{$patt}.")";
spi_exec_query($sql);
}
return '';
$BODY$
LANGUAGE plperl VOLATILE
COST 100;
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
From | Date | Subject | |
---|---|---|---|
Next Message | John Fabiani | 2011-07-05 21:42:29 | Re: interesting sequence |
Previous Message | Kevin Crain | 2011-07-05 20:13:32 | Re: interesting sequence |