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.to to avoid a reference leak until the memory context is destroyed.
--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc