Thread: a bug that might be related to BUG #1739

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

Tzahi Fadida <tzahi_ml@myrealbox.com> writes:
> 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.

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

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.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, July 04, 2005 12:54 AM
> To: Tzahi Fadida
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] a bug that might be related to BUG #1739
>
>
> Tzahi Fadida <tzahi_ml@myrealbox.com> writes:
> > 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.
>
> 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
>
>