Thread: BUG #1755: SPI_execute_plan not releasing memory even after SPI_finish

The following bug has been logged online:

Bug reference:      1755
Logged by:          Tzahi Fadida
Email address:      tzahi_ml@myrealbox.com
PostgreSQL version: 8.0.3
Operating system:   Linux 2.6.3-7mdk
Description:        SPI_execute_plan not releasing memory even after
SPI_finish
Details:

I submit this bug orderly with all the details.
If you use C language SPI_execute_plan or SPI_execp it does not release its
memory usage even after SPI_finish. Usually this is not a problem, but if
you execute it in your function 100+ times you will feel the difference in
the form of a memory leak.
If you only need selects you can alternatively use cursors with no memory
leaks, however i need to use INSERTS.

Here is a code example. You will notice that at the last NOP loop, meant to
delay the code, the memory (a few megabytes) is not released.


http://rafb.net/paste/results/t2arbb22.html

#include "executor/spi.h"

PG_FUNCTION_INFO_V1(nis);

Datum
nis(PG_FUNCTION_ARGS)
{
   SPI_connect();
   void *plan;
   int ret;
   int i;
   Datum vals[2];
   vals[0]=-1;
   vals[1]=-1;
   char nuls[2];
   nuls[0]=' ';
   nuls[1]=' ';
   Oid oids[2];
   oids[0]=INT4OID;
   oids[1]=INT4OID;

   if ((plan = SPI_prepare("CREATE TEMPORARY TABLE NIS (a int, b int)", 0,
NULL)) == NULL)
        elog(ERROR, "SPI_prepare() returns NULL");
   if ((ret = SPI_execute_plan(plan, NULL
                     , NULL, false, 1)) != SPI_OK_UTILITY)
       elog(ERROR, "SPI_execute_plan() was no successfull(create)");

   if ((plan = SPI_prepare("insert into nis values ($1,$2)", 2,oids )) ==
NULL)
        elog(ERROR, "SPI_prepare() returns NULL");

   for (i=0;i<100*1024;i++){
      if ((ret = SPI_execute_plan(plan, vals
                     , nuls, false, 1)) != SPI_OK_INSERT)
          elog(ERROR, "SPI_execute_plan() was no successfull(insert)");
      SPI_freetuptable(SPI_tuptable);
    }
    SPI_finish();
   for (i=0;i<2000*1024*1024;i++);
    PG_RETURN_INT32(1);
}

"Tzahi Fadida" <tzahi_ml@myrealbox.com> writes:
> If you use C language SPI_execute_plan or SPI_execp it does not release its
> memory usage even after SPI_finish. Usually this is not a problem, but if
> you execute it in your function 100+ times you will feel the difference in
> the form of a memory leak.

I don't see any memory leak here.

In CVS tip, what is actually happening is that with the default setting
of temp_buffers = 1000, you can have up to 80MB worth of buffers for the
temporary table.  The process expands to that size and then stops
expanding.  If you set temp_buffers to 100 or so, it stops much sooner.
In 8.0.3 I don't see any "leak" at all --- the process size sits at
about 5MB for me.

It could be that 1000 is an overly large default setting for temp_buffers.

            regards, tom lane