pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Large data sets and FOR LOOP's
7+ messages / 3 participants
[nested] [flat]

* Large data sets and FOR LOOP's
@ 2002-03-13 09:37  Bo Lorentsen <bl@netgroup.dk>
  0 siblings, 2 replies; 7+ messages in thread

From: Bo Lorentsen @ 2002-03-13 09:37 UTC (permalink / raw)
  To: pgsql-sql

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 






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Large data sets and FOR LOOP's
@ 2002-03-13 15:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bo Lorentsen <bl@netgroup.dk>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2002-03-13 15:08 UTC (permalink / raw)
  To: Bo Lorentsen <bl@netgroup.dk>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Large data sets and FOR LOOP's
@ 2002-03-13 15:35  Bo Lorentsen <bl@netgroup.dk>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Bo Lorentsen @ 2002-03-13 15:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Large data sets and FOR LOOP's
@ 2002-03-13 16:29  Jan Wieck <janwieck@yahoo.com>
  parent: Bo Lorentsen <bl@netgroup.dk>
  1 sibling, 1 reply; 7+ messages in thread

From: Jan Wieck @ 2002-03-13 16:29 UTC (permalink / raw)
  To: Bo Lorentsen <bl@netgroup.dk>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Large data sets and FOR LOOP's
@ 2002-03-14 07:57  Bo Lorentsen <bl@netgroup.dk>
  parent: Jan Wieck <janwieck@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Bo Lorentsen @ 2002-03-14 07:57 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Large data sets and FOR LOOP's
@ 2002-03-14 15:21  Jan Wieck <janwieck@yahoo.com>
  parent: Bo Lorentsen <bl@netgroup.dk>
  0 siblings, 1 reply; 7+ messages in thread

From: Jan Wieck @ 2002-03-14 15:21 UTC (permalink / raw)
  To: Bo Lorentsen <bl@netgroup.dk>; +Cc: Jan Wieck <janwieck@yahoo.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Large data sets and FOR LOOP's
@ 2002-03-14 15:56  Bo Lorentsen <bl@netgroup.dk>
  parent: Jan Wieck <janwieck@yahoo.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Bo Lorentsen @ 2002-03-14 15:56 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2002-03-14 15:56 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-13 09:37 Large data sets and FOR LOOP's Bo Lorentsen <bl@netgroup.dk>
2002-03-13 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-13 15:35   ` Bo Lorentsen <bl@netgroup.dk>
2002-03-13 16:29 ` Jan Wieck <janwieck@yahoo.com>
2002-03-14 07:57   ` Bo Lorentsen <bl@netgroup.dk>
2002-03-14 15:21     ` Jan Wieck <janwieck@yahoo.com>
2002-03-14 15:56       ` Bo Lorentsen <bl@netgroup.dk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox