Date: Fri, 8 Jul 2011 14:50:47 -0400
From: lists-pgsql@useunix.net
To: pgsql-sql@postgresql.org
Cc: viktor.bojovic@gmail.com
Subject: Re: overload
Message-ID: <20110708185047.GL13621@slacker.ja10629.home>
References: 
 <CAJu1cLYAbwDdKZpqncv4o-cOifKCipXtx2-3n9h-Pz4QBQuTwA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAJu1cLYAbwDdKZpqncv4o-cOifKCipXtx2-3n9h-Pz4QBQuTwA@mail.gmail.com>
User-Agent: Mutt/1.4.2.3i

I'm have the same situation with large tables.  Take a look at using a
cursor to fetch several thousand rows at a time.  I presume what's
happening is that perl is attempting to create a massive list/array in
memory.  If you use a cursor the list should only contain X number of
rows where X in the number specified at each fetch execution.  You'll
need to define the cursor inside a transaction block.

- begin transaction
- define the cursor
- fetch rows from cursor
- while row count from previous step > 0, execute previous step
- terminate transaction

Or you could use plpgsql instead of plperl, FOR loops over result sets in
plpgsql implicitly use cursors... it's just a little less code.

Hope that helps,
Wayne

On Tue, Jul 05, 2011 at 10:29:03PM +0200, Viktor Bojovi?? wrote:
> 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