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:
[EMAIL PROTECTED]: 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
