BUG #1739: memory leak in pl/perl with spi_exec_query - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1739
Logged by:          Jean-Max Reymond
Email address:      jmreymond@ckr-solutions.com
PostgreSQL version: 8.0.3
Operating system:   Linux Ubuntu
Description:        memory leak in pl/perl with spi_exec_query
Details:

whith this code 1, postmaster is growing and growing and eat a lot of
memory

CREATE FUNCTION jmax() RETURNS integer
   AS $_$use strict;

for (my $i=0; $i<10000000;$i++) {
       spi_exec_query("select 'foo'");
}
my $j=1;$_$
   LANGUAGE plperlu SECURITY DEFINER

With code 2, we can see that perl does correctly the job to unreference
unused memory

CREATE FUNCTION jmax() RETURNS integer
   AS $_$use strict;

for (my $i=0; $i<10000000;$i++) {
       my $ch = "0123456789"x100000;
}
my $j=1;$_$
   LANGUAGE plperlu SECURITY DEFINER

With code 3, we try to help pl/perl to clean memory allocation

CREATE FUNCTION jmax() RETURNS integer
   AS $_$use strict;

for (my $i=0; $i<10000000;$i++) {
       my ch=spi_exec_query("select 'foo'");
}
my $j=1;$_$
   LANGUAGE plperlu SECURITY DEFINER


So, spi_exec_query allocates memory but this memory is never released until
the end of the stored procedure.
For my application, the need is to call millions of spy_exec and we use 1.1
Gb of not necessary memory.

A workaround could be to provide a routine to free the memory allocated by
spi_exec_query