pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Barrett <jbarrett@familynetwork.com>
To: pgsql-sql@postgresql.org
Subject: Memory exhausted in AllocSetAlloc(269039)
Date: Tue, 4 Dec 2001 16:36:27 -0500
Message-ID: <9ujft0$2o2b$1@news.tht.net> (raw)

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





view thread (4+ messages)  latest in thread

Message-ID: <9ujft0$2o2b$1@news.tht.net>
Permalink:  ../9ujft0$2o2b$1@news.tht.net/
Also on:    postgresql.org/message-id/9ujft0$2o2b$1@news.tht.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jbarrett@familynetwork.com
  Subject: Re: Memory exhausted in AllocSetAlloc(269039)
  In-Reply-To: <9ujft0$2o2b$1@news.tht.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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