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 = 0
while 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

Search Discussions

  • Tom Lane at Oct 19, 2002 at 4:59 pm

    "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 in
    plan = 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
  • Nigel J. Andrews at Oct 19, 2002 at 5:27 pm

    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
  • Tom Lane at Oct 19, 2002 at 5:38 pm

    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 19, '02 at 4:27p
activeOct 19, '02 at 5:38p
posts4
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Nigel J. Andrews: 2 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase