The following bug has been logged online:

Bug reference: 1755
Logged by: Tzahi Fadida
Email address: tzahi_ml (AT) myrealbox (DOT) 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);
}

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Tzahi Fadida" <tzahi_ml (AT) myrealbox (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend