BUG #6785: Memory Leak in plpgsql - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      6785
Logged by:          Anderson Valadares
Email address:      andervalbh@gmail.com
PostgreSQL version: 9.1.4
Operating system:   Linux CentOS 5.5
Description:=20=20=20=20=20=20=20=20

Hello,
=C2=A0 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=3D# 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+=
=20
COMMAND
6465 postgres  16   0 1183m 1.2g 4308 4684 2896 2900 S 1  0.0  0.0   0:00.00
postgres: postgres test [local] idle
---------------------------------------------------------------------------=
---------------------------------------------


1st Execution
---------------------------------------------------------------------------=
---------------------------------------------

\o processa.sql
\t

select E'select fct_test();'
from generate_series(1, 1000);

\o
\t

\i processa.sql

PID USER      PR  NI  VIRT SWAP  RES CODE DATA  SHR S P %CPU %MEM    TIME+=
=20
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
---------------------------------------------------------------------------=
---------------------------------------------



2st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t

select E'select fct_test();'
from generate_series(1, 1000);

\o
\t

\i processa.sql

PID USER      PR  NI  VIRT SWAP  RES CODE DATA  SHR S P %CPU %MEM    TIME+=
=20
COMMAND
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
---------------------------------------------------------------------------=
---------------------------------------------


3st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t

select E'select fct_test();'
from generate_series(1, 1000);

\o
\t

\i processa.sql

PID USER      PR  NI  VIRT SWAP  RES CODE DATA  SHR S P %CPU %MEM    TIME+=
=20
COMMAND
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
---------------------------------------------------------------------------=
---------------------------------------------


4st Execution
---------------------------------------------------------------------------=
---------------------------------------------
\o processa.sql
\t

select E'select fct_test();'
from generate_series(1, 1000);

\o
\t

\i processa.sql

PID USER      PR  NI  VIRT SWAP  RES CODE DATA  SHR S P %CPU %MEM    TIME+=
=20
COMMAND
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
---------------------------------------------------------------------------=
---------------------------------------------