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
