From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | "David M(dot) Richter" <D(dot)Richter(at)DKFZ-heidelberg(dot)de>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Plsql Function with error: No space left on device. |
Date: | 2001-06-25 10:23:18 |
Message-ID: | 005601c0fd60$db576740$1001a8c0@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
From: "David M. Richter" <D(dot)Richter(at)DKFZ-heidelberg(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | DI Hasenöhrl | 2001-06-25 10:26:38 | Re: [SQL] Difference between insert a tuple in a table by function and by datasheet |
Previous Message | David M. Richter | 2001-06-25 08:50:54 | Plsql Function with error: No space left on device. |