From: Richard Huxton <dev@archonet.com>
To: David M. Richter <D.Richter@DKFZ-heidelberg.de>
To: pgsql-sql@postgresql.org
Subject: Re: Plsql Function with error: No space left on device.
Date: Mon, 25 Jun 2001 11:23:18 +0100
Message-ID: <005601c0fd60$db576740$1001a8c0@archonet.com> (raw)
References: <3B36FB6E.1039F0D4@DKFZ-heidelberg.de>
From: "David M. Richter" <D.Richter@DKFZ-heidelberg.de>
> 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
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: dev@archonet.com, D.Richter@DKFZ-heidelberg.de
Subject: Re: Plsql Function with error: No space left on device.
In-Reply-To: <005601c0fd60$db576740$1001a8c0@archonet.com>
* 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