Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5P91ra09486 for ; Mon, 25 Jun 2001 05:01:53 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f5P8qjc86392 for pgsql-sql@postgresql.org; Mon, 25 Jun 2001 04:52:45 -0400 (EDT) (envelope-from news) From: "David M. Richter" X-Newsgroups: comp.databases.postgresql.sql Subject: Plsql Function with error: No space left on device. Date: Mon, 25 Jun 2001 10:50:54 +0200 Organization: German Cancer Research Center Lines: 97 Message-ID: <3B36FB6E.1039F0D4@DKFZ-heidelberg.de> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------D62A2267A88AA2A98795D09B" X-Complaints-To: scrappy@hub.org 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/395 X-Sequence-Number: 3692 This is a multi-part message in MIME format. --------------D62A2267A88AA2A98795D09B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit HAllo! I have wrote a function. If I call this function the following output appears: psql:restructure.sql:139: ERROR: cannot extend image: No space left on device. Check free disk space. My Postgres version is 7.1.2 My Server has the following data: Main memory size: 510 Mbytes 1 GenuineIntel Pentium III (Coppermine) processor 1 vga+ graphics device 1 keyboard 1 parallel port: lp0: base 0x378, no irq, no dma, modes SPP,ECP,ECPEPP,ECPPS2 PCI bus devices: Host bridge: Intel 440BX - 82443BX Host (rev 3). PCI bridge: Intel 440BX - 82443BX AGP (rev 3). ISA bridge: Intel 82371AB PIIX4 ISA (rev 2). IDE interface: Intel 82371AB PIIX4 IDE (rev 1). USB Controller: Intel 82371AB PIIX4 USB (rev 1). Bridge: Intel 82371AB PIIX4 ACPI (rev 2). SCSI storage controller: Adaptec AIC-7890/1 (rev 0). PCI bridge: DEC DC21152 (rev 3). Ethernet controller: Intel 82557 (rev 8). Multimedia audio controller: Ensoniq ES1371 (rev 8). Multimedia video controller: Zoran ZR36057 (rev 2). VGA compatible controller: Matrox Matrox G200 AGP (rev 3). Display controller: Matrox Matrox G200 AGP (rev 3). This is the function: 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'; I saw during the execute of the function that the Ram was fully used and also the swap space was also fully used. How could I optimize the function above with new constructs of 7.1? the table relseries_image000 has ca. 3 Millions of rows. Every row has 3 columns. Thank you in advance for your help David --------------D62A2267A88AA2A98795D09B Content-Type: text/x-vcard; charset=us-ascii; name="D.Richter.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for David M. 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 --------------D62A2267A88AA2A98795D09B--