Thread: plperl: spi_query_prepared/spi_fetchrow versus spi_exec_prepared: memory?

hi all,

having some memory leak issues with my app and spi_exec_prepared.
checking the docs:

"Normally, spi_fetchrow should be repeated until it returns undef,
indicating that there are no more rows to read. The cursor is
automatically freed when spi_fetchrow returns undef. If you do not
wish to read all the rows, instead call spi_cursor_close to free the
cursor. Failure to do so will result in memory leaks."

... which is all well and good, but what about the non-cursor-creating
version spi_exec_prepared which fetches all rows into memory right off
the bat? i would think that doing e.g.:

my $test = spi_exec_prepared(spi_prepare('select something from table
where uid=$1', 'int'), $ref->{uid})->{rows};

... would free the ram of those rows when $test goes out of scope but
this is not the case for me on 8.3 (debian) tonight. even explicitly
setting $test = '' also leaves that data in ram. however this:

my $test = spi_query_prepared(spi_prepare('select something from table
where uid=$1', 'int'), $ref->{uid})->{rows};
while (defined (my $row = spi_fetchrow($test))) {}

... DOES free the ram as the docs suggest. so what's the recommended
way to use spi_exec_prepared() while being able to free the ram that
it allocates for its result set? sometimes i just want everything and
don't want to bother with a cursor.


thanks,

nathan

On Nov 17, 2009, at 8:34 AM, Nathan Jahnke wrote:

> hi all,
>
> having some memory leak issues with my app and spi_exec_prepared.
> checking the docs:
>
> "Normally, spi_fetchrow should be repeated until it returns undef,
> indicating that there are no more rows to read. The cursor is
> automatically freed when spi_fetchrow returns undef. If you do not
> wish to read all the rows, instead call spi_cursor_close to free the
> cursor. Failure to do so will result in memory leaks."
>
> ... which is all well and good, but what about the non-cursor-creating
> version spi_exec_prepared which fetches all rows into memory right off
> the bat?

Well, the documentation specifically warns against using calling this functions when the result set is large.

> i would think that doing e.g.:
>
> my $test = spi_exec_prepared(spi_prepare('select something from table
> where uid=$1', 'int'), $ref->{uid})->{rows};
>
> ... would free the ram of those rows when $test goes out of scope but
> this is not the case for me on 8.3 (debian) tonight. even explicitly
> setting $test = '' also leaves that data in ram. however this:

I wonder if sv_2mortal should be called for the return value from plperl_spi_exec and plperl_spi_exec_prepared in
plperl.c.toto avoid a reference leak until the memory context is destroyed.  


--
Alexey Klyukin                    http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc