Re: plperl doesn't release memory

On Wed, 2005-03-23 at 18:25 -0500, Tom Lane wrote:
> Sven Willenberger <sven(at)dmv(dot)com> writes:
> > I have been experiencing an issue with plperl and PostgreSQL 8.0.1 in
> > that after calling a plperl function memory does not get released.
>
> AFAICT the result of spi_exec_query gets released fine, as soon as it's
> no longer referenced within perl. Perhaps your perl code is written in
> such a way that a reference to the hash result value remains live after
> the function exit?
>
> I tried this:
>
> create or replace function nrows(text) returns int as $$
> my ($tabname) = @_;
> my $rv = spi_exec_query("select * from $tabname");
> return $rv->{processed};
> $$ LANGUAGE plperl;
>
> and ran it repeatedly against a large table. The memory usage went
> up as expected, but back down again as soon as the function exited.
>
> If you think it's actually a plperl bug, please show a self-contained
> example.
>

The query in question that we used for testing is:
-------------------------------------
CREATE OR REPLACE FUNCTION f_dom_e_lcr() RETURNS text AS $$

my $on_shore = "select
root_decks.id,material_all.npanxx,material_all.carrier,material_all.inter
from root_decks, material_all, lookup
where lookup.state not in (select state from offshore)
and lookup.npanxx = material_all.npanxx
and root_decks.type = 'ie'
and root_decks.carrier = material_all.carrier;";

my $rv = spi_exec_query($on_shore);
my $nrows = $rv->{processed};

return $nrows ;
$$ LANGUAGE plperl;
-------------------------------------

Now thinking that perhaps the $nrows variable was getting stuck we tried
the following which resulted in the exact same memory issue:
-------------------------------------
CREATE OR REPLACE FUNCTION f_dom_e_lcr() RETURNS text AS $$

my $on_shore = "select
root_decks.id,material_all.npanxx,material_all.carrier,material_all.inter
from root_decks, material_all, lookup
where lookup.state not in (select state from offshore)
and lookup.npanxx = material_all.npanxx
and root_decks.type = 'ie'
and root_decks.carrier = material_all.carrier;";

my $rv = spi_exec_query($on_shore);

return $rv->{processed};
$$ LANGUAGE plperl;
-------------------------------------

The result set is just under 1 million rows and top shows postgres using
some 600MB of memory. After the 3rd run of this function on a 1GB RAM
box, the error mentioned in the original part of this thread occurs and
the database restarts.

Any suggestions on how to trace what is going on? Debug output methods?

Sven