I am writing C functions and I use SPI extensively.
Till now I used cursors and there was no memory leaks.
But now I do thousands of inserts using SPI_execp
and I also give it parameters.
Now, when I execute the insert about 50-100 times I loose
around 100kb memory and SPI_finish() does not release this
memory (although I really can't do SPI finish in normal executions
all the time since I have open cursors).
I tried spipush connect query finish pop. but the leak stays.
I really need this resolved since I can reach a million runs
in my functions and thus my whole memory can be depleted.

I use 8.0.3

Another clue. The relation I am adding to have a unique index.
If I do about 100 inserts and the relation have the index I loose around
100kb.
without the index on the relation I loose around 40kb.

Btw, execp with selects also looses memory. Only cursors seems immune to
this.
Please help,
10x.


Regards,
tzahi.

WARNING TO SPAMMERS: see at
http://members.lycos.co.uk/my2nis/spamwarning.html



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Tzahi Fadida <tzahi_ml (AT) myrealbox (DOT) com> writes:
If SPI_execp leaked memory then so would almost any plpgsql function,
so I'm inclined to think the leak is in your own code. Post a complete
example if you want it investigated.

regards, tom lane

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

Here it is. It could very well be in my code or a lack of understanding.
I started from scratch and have written a small example.
If it will also appear on your machine you should notice
that you are loosing a few megabites in memory
in a few secs. Maybe I am not freeing something or something like
that or it could be some kind of caching mechnism.
All I know is that when the function finishes the memory is not
released.
And when I exit psql, the memory is released.

p.s: I don't know if its normal but if I don't do spi_freetuptable after
the inserts,
I loose 10 times more memory.

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);
}

Regards,
tzahi.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match