Обсуждение: Freeing plan memory

I notice there's a leak of memory in SPI_prepare().

The full fix is nontrival and I don't want to submit a half solution so I
thought I'd check whether people think it's worth worrying about.

The leak is that memory is grabbed in SPI_prepare() for a plan within whatever
context is current when it does the palloc(). It may be the caller's or it may
be the relevent SPI one. The plan is then copied out of this memory [and
context] into a child of the procedure's context and forgotten about, or just
plain forgotten. Obviously the intention is that this memory is freed when the
context is deleted and is probably not a problem unless someone does something
like:

i = 100000;
while (i--)
{  plan = SPI_prepare("SELECT 1", 0, (Oid *)NULL);  SPI_freeplan(plan);  /* SPI_freeplan() is not just for
SPI_saveplan()*/
 
}

Is this worth worrying about?

Any busy person can stop reading now as the above defines the problem while the
below only shows an easily reproducable example.

FWIW, I found it while testing something like, which is a little less daft
than the above example:

create function atest1 ( ) returns int as 'a = 0while a < 10000: plan = plpy.prepare("SELECT " + repr(a)) a = a + 1
' language 'plpython';

Here the plpython code uses SPI_freeplan to release the context holding the
plan memory when each plan object returned by plpy.prepare() is garbage
collected. This seems sensibly to happen when the plan variable is
reassigned. However I was baffled why the process still had an obvious memory
leak so looked a little closer at SPI.


-- 
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> The leak is that memory is grabbed in SPI_prepare() for a plan within
> whatever context is current when it does the palloc(). It may be the
> caller's or it may be the relevent SPI one. The plan is then copied
> out of this memory [and context] into a child of the procedure's
> context and forgotten about, or just plain forgotten.

Au contraire: SPI_prepare builds the plan in its "execCxt", which is
reset before returning (look at _SPI_begin_call and _SPI_end_call).
So I see no leak there.

I'm not sure where the leak is in your plpython example, but I'd be
inclined to look to plpython itself, perhaps even just the string
concatenation expression inplan = plpy.prepare("SELECT " + repr(a))

plpgsql used to have terrible intra-function memory leaks, and only by
dint of much hard work has it been brought to the point where you can
expect a long loop in a plpgsql function not to chew up memory.  AFAIK,
no one has yet done similar work for the other PL languages.
        regards, tom lane

On Sat, 19 Oct 2002, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > The leak is that memory is grabbed in SPI_prepare() for a plan within
> > whatever context is current when it does the palloc(). It may be the
> > caller's or it may be the relevent SPI one. The plan is then copied
> > out of this memory [and context] into a child of the procedure's
> > context and forgotten about, or just plain forgotten.
> 
> Au contraire: SPI_prepare builds the plan in its "execCxt", which is
> reset before returning (look at _SPI_begin_call and _SPI_end_call).
> So I see no leak there.

Ah, yes, I see that now.

> I'm not sure where the leak is in your plpython example, but I'd be
> inclined to look to plpython itself, perhaps even just the string
> concatenation expression in
>     plan = plpy.prepare("SELECT " + repr(a))

Well it's not that string operation.

> plpgsql used to have terrible intra-function memory leaks, and only by
> dint of much hard work has it been brought to the point where you can
> expect a long loop in a plpgsql function not to chew up memory.  AFAIK,
> no one has yet done similar work for the other PL languages.

Hmmm...my test case should boil down to a fairly small number of other calls in
the SPI_prepare wrapper and a quick looks doesn't show anything
interesting. Not sure I've got the time to dedicate to investigating this but
I'll look at it as and when I can.

I'm sending a patch for plpython.c to -patches which fixes a mistake I made in
the previous patch.


-- 
Nigel J. Andrews

"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> On Sat, 19 Oct 2002, Tom Lane wrote:
>> I'm not sure where the leak is in your plpython example, but I'd be
>> inclined to look to plpython itself, perhaps even just the string
>> concatenation expression in
>> plan = plpy.prepare("SELECT " + repr(a))

> Well it's not that string operation.

Actually, I'll bet it's this code in PLy_spi_prepare:
plan->plan = SPI_prepare(query, plan->nargs, plan->types);// error check
plan->plan = SPI_saveplan(plan->plan);// error check

The copy of the plan that's returned by SPI_prepare is being blithely
lost --- and since it's in the procCxt, it won't go away until the
plpython function is exited.  Need a SPI_freeplan() here, I think.
Can you check it out and send a patch?
        regards, tom lane