Received: from finch-post-12.mail.demon.net (finch-post-12.mail.demon.net [194.217.242.41]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5PAXJa45343 for ; Mon, 25 Jun 2001 06:33:19 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by finch-post-12.mail.demon.net with esmtp (Exim 2.12 #1) id 15ETgA-0003jB-0C; Mon, 25 Jun 2001 10:33:14 +0000 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id A305D143E7; Mon, 25 Jun 2001 11:23:22 +0100 (BST) Message-ID: <005601c0fd60$db576740$1001a8c0@archonet.com> From: "Richard Huxton" To: "David M. Richter" , References: <3B36FB6E.1039F0D4@DKFZ-heidelberg.de> Subject: Re: Plsql Function with error: No space left on device. Date: Mon, 25 Jun 2001 11:23:18 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/397 X-Sequence-Number: 3694 From: "David M. Richter" > 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. [snip] > 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. > the table relseries_image000 has ca. 3 Millions of rows. Every row has 3 > columns. You're probably taking up all the space because PG is trying to keep track of 3 million separate operations inside the transaction. You can replace the function with a single query using something like: UPDATE image SET seriesoid = r.parentoid FROM image i JOIN relseries r ON i.childoid=r.childoid; This isn't standard SQL mind you. - Richard Huxton