Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5BCSwE48352 for ; Mon, 11 Jun 2001 08:28:58 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f5BCRTU18401 for pgsql-sql@postgresql.org; Mon, 11 Jun 2001 08:27:29 -0400 (EDT) (envelope-from news) From: David Richter X-Newsgroups: comp.databases.postgresql.sql Subject: Memory exhausted Date: Mon, 11 Jun 2001 14:25:48 +0200 Organization: German Cancer Research Center Lines: 82 Message-ID: <3B24B8CC.15C55147@DKFZ-heidelberg.de> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------51E1632F31679D0354EBD39D" X-Complaints-To: scrappy@hub.org To: sg9@sg9.de X-Mailer: Mozilla 4.7 [en] (X11; I; IRIX64 6.4 IP27) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200106/151 X-Sequence-Number: 3372 This is a multi-part message in MIME format. --------------51E1632F31679D0354EBD39D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hello! I used a self written funtion in plpgsql with a database of 2 Gigabyte size. My server has 384 Megabytes of RAM. So I got this error by calling the following function: psql:restructure.sql:139: FATAL 1: Memory exhausted in AllocSetAlloc() pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. psql:restructure.sql:139: connection to server was lost In the memory usage program Its shown that the function needs all the memory. The function fetches all XXX rows of a table and writes a value to another table CREATE FUNCTION series_image () RETURNS integer AS ' DECLARE psr_rec record; i integer := 0; BEGIN FOR psr_rec IN SELECT * FROM relseries_image000 LOOP UPDATE image SET seriesoid = psr_rec.parentoid WHERE chilioid = psr_rec.childoid; i := i + 1; END LOOP; IF NOT FOUND THEN RETURN -1; ELSE RETURN i; END IF; END; ' LANGUAGE 'plpgsql'; What could I optimize in this function above? I tried the Select statement in the psql command and it has taken 20 minutes. I estimate that there are more than 400000 rows in the table. Then it breakes , the announcment appears: malloc: Resource temporarily unavailable and psql is crashed. Should I change the postmaster parameters? actually they are : ./postmaster -i -S -D/usr/local/pgsql/data -B 256 -o -e -o -F What can I do? Thanks in advance for any advice David --------------51E1632F31679D0354EBD39D Content-Type: text/x-vcard; charset=us-ascii; name="D.Richter.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for David Richter Content-Disposition: attachment; filename="D.Richter.vcf" begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:d.richter@dkfz-heidelberg.de adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard --------------51E1632F31679D0354EBD39D--