Skip site navigation (1) Skip section navigation (2)

Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop

From: "Bill Rugolsky Jr(dot)" <brugolsky(at)telemetry-investments(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Out-of-Memory with ROWTYPE assignment in PL/pgSQL FOR loop
Date: 2005-06-20 21:23:05
Message-ID: 20050620212305.GF11674@ti64.telemetry-investments.com (view raw or flat)
Thread:
Lists: 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(at)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

Responses

pgsql-bugs by date

Next:From: Tatsuo IshiiDate: 2005-06-20 22:37:12
Subject: Re: BUG #1721: mutiple bytes character string comaprison
Previous:From: Magnus HaganderDate: 2005-06-20 20:01:04
Subject: Re: BUG #1721: mutiple bytes character string comaprison error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group