Re: BUG #6785: Memory Leak in plpgsql

Hi,

2012/7/30 Andres Freund <andres(at)2ndquadrant(dot)com>

> Hi,
>
> On Monday, July 30, 2012 03:15:37 PM andervalbh(at)gmail(dot)com wrote:
> > we recently had a memory exhaustion in the PostgreSQL server of the
> > company, after a scan found a likely memory leak when using a plpgsql
> > function.
> > The problem occurred on an IBM x3400 server with 12G, CentOS 5.5 and
> > PostgreSQL 9.1.4. The leak occurs when a variable declared type of a
> table
> > column and when the
> > column or the table ceases to exist.
> > Follow the steps for the simulation:
> >
> > create table tbl_test
> > (cod integer);
> >
> > CREATE OR REPLACE FUNCTION citgis.fct_test()
> > RETURNS void AS
> > $body$
> > DECLARE
> > v_cod tbl_test.cod%type;
> > BEGIN
> > return;
> > END;
> > $body$
> > LANGUAGE 'plpgsql';
> >
> > drop table tbl_test;
> >
> >
> > test=# select pg_backend_pid();
> > pg_backend_pid
> > ----------------
> > 6465
> >
> > Initial memory
> >
> ---------------------------------------------------------------------------
> > --------------------------------------------- PID USER PR NI VIRT
> > SWAP RES CODE DATA SHR S P %CPU %MEM TIME+ COMMAND
> > 6465 postgres 15 0 1194m 1.2g 14m 4684 12m 3748 S 1 0.0 0.1
> > 0:00.08 postgres: postgres test [local] idle
> > 6465 postgres 15 0 1203m 1.2g 23m 4684 21m 3748 S 1 0.0 0.2
> > 0:00.17 postgres: postgres test [local] idle
> > 6465 postgres 15 0 1212m 1.2g 32m 4684 30m 3748 S 1 3.0 0.3
> > 0:00.26 postgres: postgres test [local] idle
> > 6465 postgres 16 0 1221m 1.2g 41m 4684 39m 3756 S 1 0.0 0.3
> > 0:00.36 postgres: postgres test [local] idle
> >
> ---------------------------------------------------------------------------
> > ---------------------------------------------
> I don't think youve found a memory leak here. I tested several thousand
> iterations of this and the memory usage tops out a 93MB. What you see is
> probably some memory fragmentation and that some copy-on-write page
> mappings
> are only gradually brought into place.
>
> Greetings,
>
> Andres
> --
> Andres Freund http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

I understand, but the memory should not be returned after the execution of
the function?
Below is the result of running with more than 55,000 calls.

PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEM TIME+
COMMAND
618 postgres 15 0 1687m 1.2g 507m 4684 505m 3796 S 1 0.0 4.2 0:04.90
postgres: postgres test [local] idle

Greetings,

Anderson