pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Memory exhausted in AllocSetAlloc(269039)
4+ messages / 3 participants
[nested] [flat]

* Memory exhausted in AllocSetAlloc(269039)
@ 2001-12-04 21:36 Jeff Barrett <jbarrett@familynetwork.com>
  2001-12-04 22:43 ` Re: Memory exhausted in AllocSetAlloc(269039) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Jeff Barrett @ 2001-12-04 21:36 UTC (permalink / raw)
  To: pgsql-sql

I have a function that takes a comma seperated string from another table and
parses it into another table. When I run it on a string with 130 ids it
works fine, when I run it on a string with 50,000 ids (each about 6
characters long) I get the Error: Memory exhausted in AllocSetAlloc(269039).

The system is a dual cpu intel with 1gig of ram and 1gig of swap space.
Before I run the function I start top and it shows about 800mb of ram in
use. This quickly excellerates until all ram is full and then fills up the
swap space then postgres pukes the error message. It is running postgres
7.1.

The questions I have are:
  Is my script that inefficent that I am using up all of my ram? (I know the
string of ids in another table is horribly inefficent, but it is a
historical nessesity I would rather avoid changing.)
  What is taking up all of this ram, how can I even trace the problem down?

CREATE FUNCTION membidsintotable(INTEGER) RETURNS boolean AS '
DECLARE
  string_rec RECORD;
  resdataid ALIAS FOR $1;
BEGIN

  SELECT INTO string_rec membids FROM listresultmembids WHERE listresdataid
= resdataid;

  IF NOT FOUND THEN
    RETURN FALSE;
  END IF;

  WHILE char_length(string_rec.membids) > 0 LOOP

    INSERT INTO listmembids VALUES (resdataid, substring(string_rec.membids
FROM 0 FOR position('','' in string_rec.membids)));

    string_rec.membids := trim(leading substring(string_rec.membids FROM 0
FOR position('','' in string_rec.membids)) FROM string_rec.membids);
    string_rec.membids := trim(leading '','' FROM string_rec.membids);
  END LOOP;

  RETURN TRUE;
END;
' LANGUAGE 'plpgsql';


Thanks for the help,

Jeff Barrett





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

* Re: Memory exhausted in AllocSetAlloc(269039)
  2001-12-04 21:36 Memory exhausted in AllocSetAlloc(269039) Jeff Barrett <jbarrett@familynetwork.com>
@ 2001-12-04 22:43 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-05 15:55   ` Re: Memory exhausted in AllocSetAlloc(269039) Roberto Mello <rmello@cc.usu.edu>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-12-04 22:43 UTC (permalink / raw)
  To: Jeff Barrett <jbarrett@familynetwork.com>; +Cc: pgsql-sql

"Jeff Barrett" <jbarrett@familynetwork.com> writes:
> I have a function that takes a comma seperated string from another table and
> parses it into another table. When I run it on a string with 130 ids it
> works fine, when I run it on a string with 50,000 ids (each about 6
> characters long) I get the Error: Memory exhausted in AllocSetAlloc(269039).

Seems to run fine in 7.2beta.  Prior versions of plpgsql tended to leak
memory within a function call, but that's been worked on in 7.2.

If you don't care to run beta code, you could possibly work around the
problem in 7.1 by subdividing your function.  Look in the archives for
a prior discussion of this issue (last summer, I think).

			regards, tom lane



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

* Re: Memory exhausted in AllocSetAlloc(269039)
  2001-12-04 21:36 Memory exhausted in AllocSetAlloc(269039) Jeff Barrett <jbarrett@familynetwork.com>
  2001-12-04 22:43 ` Re: Memory exhausted in AllocSetAlloc(269039) Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-12-05 15:55   ` Roberto Mello <rmello@cc.usu.edu>
  2001-12-05 16:48     ` Re: Memory exhausted in AllocSetAlloc(269039) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Roberto Mello @ 2001-12-05 15:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Barrett <jbarrett@familynetwork.com>; pgsql-sql

On Tue, Dec 04, 2001 at 05:43:17PM -0500, Tom Lane wrote:
> 
> Seems to run fine in 7.2beta.  Prior versions of plpgsql tended to leak
> memory within a function call, but that's been worked on in 7.2.

Tom,

Would you please ellaborate on this leak? We are using PL/pgSQL heavily
on OpenACS and these details would help to make a decision to support 7.1
or push for 7.2.

Thanks,

-Roberto

-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
       http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
ASCII stupid question, get stupid ANSI.



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

* Re: Memory exhausted in AllocSetAlloc(269039)
  2001-12-04 21:36 Memory exhausted in AllocSetAlloc(269039) Jeff Barrett <jbarrett@familynetwork.com>
  2001-12-04 22:43 ` Re: Memory exhausted in AllocSetAlloc(269039) Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-05 15:55   ` Re: Memory exhausted in AllocSetAlloc(269039) Roberto Mello <rmello@cc.usu.edu>
@ 2001-12-05 16:48     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-12-05 16:48 UTC (permalink / raw)
  To: Roberto Mello <rmello@cc.usu.edu>; +Cc: Jeff Barrett <jbarrett@familynetwork.com>; pgsql-sql

Roberto Mello <rmello@cc.usu.edu> writes:
> On Tue, Dec 04, 2001 at 05:43:17PM -0500, Tom Lane wrote:
>> Seems to run fine in 7.2beta.  Prior versions of plpgsql tended to leak
>> memory within a function call, but that's been worked on in 7.2.

> Would you please ellaborate on this leak? We are using PL/pgSQL heavily
> on OpenACS and these details would help to make a decision to support 7.1
> or push for 7.2.

I don't recall all the details, but some plpgsql constructs would use
memory that wouldn't be reclaimed until function exit.  So if you
had a long-running loop inside a function, you could see memory
problems.

I'm not convinced that 7.2 is *entirely* free of such problems, but it's
a lot better than 7.1 was.

			regards, tom lane



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


end of thread, other threads:[~2001-12-05 16:48 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-04 21:36 Memory exhausted in AllocSetAlloc(269039) Jeff Barrett <jbarrett@familynetwork.com>
2001-12-04 22:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-05 15:55   ` Roberto Mello <rmello@cc.usu.edu>
2001-12-05 16:48     ` Tom Lane <tgl@sss.pgh.pa.us>

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