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

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.to to avoid a reference leak until the memory context is destroyed.

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