[HACKERS] Memory leak in PL/pgSQL function which CREATE/SELECT/DROP a temporary table

[MauMau]

Hello,





$ psql -d postgres -f myfunc.sql
$ ecpg myfunc.pgc

$ ./myfunc










Q1: Is this a bug of PostgreSQL?

Q2: If yes, is it planned to be included in the upcoming minor release?



Regards
MauMau

I've encountered a memory leak problem when I use a PL/pgsql function which creates and drops a temporary table. I couldn't find any similar problem in the mailing list. I'd like to ask you whether this is a PostgreSQL's bug. Maybe I should post this to pgsql-bugs or pgsql-general, but the discussion is likely to involve the internal behavior of PostgreSQL, so let me post here. The steps to reproduce the problem is as follows. Please find attached two files to use for this. $ cc -I<pgsql_inst_dir>/include myfunc.c -o myfunc -L<pg_inst_dir>/lib -lecpg As the program myfunc runs longer, the values of VSZ and RSS get bigger, even after 50,000 transactions. The cause of the memory increase appears to be CacheMemoryContext. When I attached to postgres with gdb and ran "call MemoryContextStats(TopMemoryContext)" several times, the size of CacheMemoryContext kept increasing. By the way, when I replace "SELECT COUNT(*) INTO cnt FROM mytable" in the PL/pgSQL function with "INSERT INTO mytable VALUES(1)", the memory stops increasing. So, the memory leak seems to occur when SELECT is used. I know the solution -- add "IF NOT EXISTS" to the CREATE TEMPORARY TABLE. That prevents memory increase. But why? What's wrong with my program? I'd like to know: Q3: If this is not a bug and a reasonable behavior, is it described anywhere?

myfunc.pgc
Description: Binary data

myfunc.sql
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers