Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop - Mailing list pgsql-bugs

From Bill Rugolsky Jr.
Subject Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop
Date
Msg-id 20050620212305.GF11674@ti64.telemetry-investments.com
Whole thread Raw
Responses Re: Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop
List pgsql-bugs
Hello,

The PL/pgSQL FOR loop in the function consume_memory() defined below
will consume VM on each iteration until the process hits its ulimit.
The problem occurs with variables of ROWTYPE; there is no unbounded
allocation when using simple types such as integer or varchar.  Before I
delve into the SPI code, perhaps someone familiar with PostgreSQL internals
can spot the resource leak.

Tested with 8.0.1 and CVS head as of 2005-06-20:

-- Start of test code

-- create a table with ten million rows
CREATE TEMPORARY TABLE ten ( n integer DEFAULT 0 ) ;
INSERT INTO ten VALUES (0);
INSERT INTO ten VALUES (1);
INSERT INTO ten VALUES (2);
INSERT INTO ten VALUES (3);
INSERT INTO ten VALUES (4);
INSERT INTO ten VALUES (5);
INSERT INTO ten VALUES (6);
INSERT INTO ten VALUES (7);
INSERT INTO ten VALUES (8);
INSERT INTO ten VALUES (9);
CREATE TEMPORARY TABLE thousand AS
   SELECT 100*i.n + 10*j.n + k.n AS n FROM ten AS i, ten as j, ten as k ;
CREATE TEMPORARY TABLE tenmillion AS
   SELECT 10000*i.n + 10*j.n + k.n AS n FROM thousand AS i, thousand as j, ten as k ;

-- a function to consume memory
CREATE OR REPLACE FUNCTION consume_memory()
RETURNS void AS $PROC$
DECLARE
   rec tenmillion%ROWTYPE ;
   prev tenmillion%ROWTYPE ;
BEGIN
   FOR rec IN SELECT * FROM tenmillion LOOP
      prev := rec ;
   END LOOP ;
   RETURN ;
END
$PROC$ LANGUAGE plpgsql;

-- Until this point, memory usage is approximately constant.
-- Evaluating the above function will rapidly consume VM.

SELECT consume_memory() ;

-- End of test code

Here's a record of Committed_AS from /proc/meminfo on a Linux 2.6 system,
over the course of the test:

rugolsky@ti64: while : ; do grep Committed_AS /proc/meminfo ; sleep 1 ; done
Committed_AS:   225592 kB
Committed_AS:   225592 kB
Committed_AS:   233692 kB <- Started
Committed_AS:   258280 kB
Committed_AS:   282868 kB
Committed_AS:   299260 kB
Committed_AS:   323848 kB
Committed_AS:   340232 kB
Committed_AS:   348436 kB
Committed_AS:   356632 kB
Committed_AS:   381220 kB
Committed_AS:   397612 kB
Committed_AS:   414004 kB
Committed_AS:   422200 kB
Committed_AS:   438592 kB
Committed_AS:   463180 kB
Committed_AS:   487768 kB
Committed_AS:   504160 kB
Committed_AS:   504160 kB
Committed_AS:   520552 kB
Committed_AS:   545140 kB
Committed_AS:   569728 kB
Committed_AS:   586120 kB
Committed_AS:   586120 kB
Committed_AS:   602512 kB
Committed_AS:   225640 kB <- Cancelled

Regards,

    Bill Rugolsky

pgsql-bugs by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Re: BUG #1721: mutiple bytes character string comaprison error
Next
From: Tatsuo Ishii
Date:
Subject: Re: BUG #1721: mutiple bytes character string comaprison