Received: from secure.webhotel.net (secure.webhotel.net [195.41.202.80]) by postgresql.org (Postfix) with SMTP id E3903475966 for ; Wed, 13 Mar 2002 04:37:20 -0500 (EST) Received: (qmail 68485364 invoked from network); 13 Mar 2002 09:41:56 -0000 Received: from unknown (HELO localhost.localdomain) (217.157.135.140) by mail.webhotel.net with SMTP; 13 Mar 2002 09:41:56 -0000 Subject: Large data sets and FOR LOOP's From: Bo Lorentsen To: pgsql-sql Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 13 Mar 2002 10:37:21 +0100 Message-Id: <1016012241.1094.90.camel@netgroup> Mime-Version: 1.0 X-Archive-Number: 200203/130 X-Sequence-Number: 7010 Hi ... I have been working on some server side scripts in Plpgsql and I'm quite happy about them (nice and fast way to work with large data sets) :-) But, if I, in my code, make "for loop" on a huge select result (about 1.7 mil. rows), my function begin to consume alot of memory. The code looks like this : FOR trec IN SELECT f1,f2 FROM large_tabel LOOP ... END LOOP; When selecting the result with a small limit (about 1000) the function still consumes much memory, but when the function is successfully done the memory are freed nicely again. When using the full select I run out of memory (I got 2G in this mashine + swap :-)). Are there a way to release this memory, or would a cursor be the answer. Does Plpgsql not use normal scope rules in FOR LOOP's ? Anyway, I fixed the problem by removing the topmost FOR loop, and then called the function from a SELECT, multibly times. Like this : SELECT myfunc( f1, f2 ) FROM large_table; Is this a known issue, or is it at bug, or are there something I have misunderstood ? Who maintain the part of PostgreSQL, anyway ? /BL