Thread: Large data sets and FOR LOOP's
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
Bo Lorentsen <bl@netgroup.dk> writes:
> 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.
What PG version?
We fixed quite a few transient-memory-leak problems in plpgsql in 7.2.
regards, tom lane
On Wed, 2002-03-13 at 16:08, Tom Lane wrote: > > What PG version? 7.1.3 (Debian woody) > We fixed quite a few transient-memory-leak problems in plpgsql in 7.2. Are there any changelog, that only regards the plpqsql ? It is hard to find any information about the development of this part of PG, like problems related to the "set of" problem (lake of functionality), where it is not possible to work (like append) with a "set of" something. /BL
Bo Lorentsen wrote: > 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; You probably want to upgrade to 7.2, because 7.2's PL/pgSQL uses the newly available SPI cursors silently in FOR loops. Releases before 7.2 loaded the entire result set into memory (ahem, swap space) before entering the FOR loop. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Wed, 2002-03-13 at 17:29, Jan Wieck wrote: > You probably want to upgrade to 7.2, because 7.2's PL/pgSQL > uses the newly available SPI cursors silently in FOR loops. This sounds good, I like to upgrade anyway, also to take advantage of the new vaccum. > Releases before 7.2 loaded the entire result set into memory > (ahem, swap space) before entering the FOR loop. This was what I guested, too :-) Would a cursor have helped any ? /BL
Bo Lorentsen wrote: > On Wed, 2002-03-13 at 17:29, Jan Wieck wrote: > > > You probably want to upgrade to 7.2, because 7.2's PL/pgSQL > > uses the newly available SPI cursors silently in FOR loops. > This sounds good, I like to upgrade anyway, also to take advantage of > the new vaccum. > > > Releases before 7.2 loaded the entire result set into memory > > (ahem, swap space) before entering the FOR loop. > This was what I guested, too :-) > > Would a cursor have helped any ? Sure, if they whould've been available in PL/pgSQL. This all was a combined feature enhancement for 7.2. 1st cursor support over SPI (an internal query interface inside the backend), 2nd support for cursors and reference cursors in PL/pgSQL, 3rd automatic usage of internal cursors in FOR loops. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
On Thu, 2002-03-14 at 16:21, Jan Wieck wrote: > > Would a cursor have helped any ? > > Sure, if they whould've been available in PL/pgSQL. This all > was a combined feature enhancement for 7.2. 1st cursor > support over SPI (an internal query interface inside the > backend), 2nd support for cursors and reference cursors in > PL/pgSQL, 3rd automatic usage of internal cursors in FOR > loops. I think I will update wery soon now (just need to woody package) :-) /BL