Thread: Plsql Function with error: No space left on device.
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
Attachment
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
Hallo Richard! Thanks a lot for Your hint. It works. Yeah. But only in a little database of 1000 rows. In my case with 4 milions of rows I was waiting 17 hours without any success by using the processor 97%. So I canceled the Job. What could i optimize in this query? Do You perhaps have another great hint? Thanks a lot David
Attachment
Hallo!
The query works, but not right.(apart from the timeaspect)
UPDATE image SET seriesoid = r.parentoid
FROM image i JOIN relseries_image000 r ON i.chilioid=r.childoid;
it does not same than this 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';
Only always the same one oid is written in seriesoid(e.g. 0x0)
But the part of the query
Select * FROM image i JOIN relseries_image000 r ON
i.chilioid=r.childoid;
works right.
Here the Table descriptions:
Table "relseries_image000"
Attribute | Type | Modifier
-----------+-----------------------+----------
chilioid | character varying(80) |
parentoid | character varying(80) |
childoid | character varying(80) |
Indices: indseries_image_child,
indseries_image_parent
Table "series"
Attribute | Type | Modifier
---------------------+------------------------+----------
chilioid | character varying(80) |
instanceuid | character varying(64) |
number | integer |
acquisition | integer |
echonumber | integer |
temporalposition | integer |
seriesdate | date |
seriestime | time |
description | character varying(128) |
contrast | character varying(128) |
bodypartexamined | character varying(16) |
scanningsequence | character varying(16) |
frameofreferenceuid | character varying(64) |
Indices: indseries_oid,
indseries_uid
Table "image"
Attribute | Type | Modifier
---------------+------------------------+----------
chilioid | character varying(80) |
instanceuid | character varying(64) |
imagetype | character varying(64) |
number | integer |
imagedate | date |
imagetime | time |
slicelocation | double precision |
rows | integer |
columns | integer |
bitsallocated | integer |
path | character varying(256) |
relevance | integer |
thickness | double precision |
spacing_x | double precision |
spacing_y | double precision |
spacing_z | double precision |
tilt | double precision |
tableheight | double precision |
position_x | double precision |
position_y | double precision |
position_z | double precision |
window_center | integer |
window_width | integer |
seriesoid | character varying(80) |
Indices: indimage_oid,
indimage_uid
What do I have to change in the Query?
Thanks a lot
David
Attachment
From: "David M. Richter" <D.Richter@DKFZ-heidelberg.de> > Hallo Richard! > > Thanks a lot for Your hint. It works. Yeah. > But only in a little database of 1000 rows. > In my case with 4 milions of rows I was waiting 17 hours without any > success by using the processor 97%. > So I canceled the Job. Patience is a virtue, but nobody is *that* virtuous. > What could i optimize in this query? Not much as it stands, it couldn't be much simpler. > Do You perhaps have another great hint? Well - if you've been drinking all night, have a glass of water before going to bed - always served me well ;-) We're going to need some more information. Try EXPLAIN <update query> and post the results along with the \d for the two tables and how many records are in each. Oh - and how many distinct values for each field there are. I'm assuming for any childoid there is only one corresponding parentoid in relseries_image? If so, and values of childoid are repeated many times, try selecting unique pairs into a temporary table. Join against that for the update. It could be that PG is ending up with a zillion rows in the join. - Richard Huxton
"David M. Richter" <D.Richter@DKFZ-heidelberg.de> writes:
> The query works, but not right.(apart from the timeaspect)
> UPDATE image SET seriesoid = r.parentoid
> FROM image i JOIN relseries_image000 r ON i.chilioid=r.childoid;
You shouldn't mention "image i" in the FROM clause --- since image is
already implicitly part of the FROM list, you are getting a self-join,
and an unconstrained one at that.
Try just
UPDATE image SET seriesoid = r.parentoid
FROM relseries_image000 r WHERE image.chilioid = r.childoid;
regards, tom lane
Hello!
It works right. And the speed is acceptable. Great!!!!
I used that Query on several table with a little smaller size. It
worked.
Thank You very much!
But in the Query at the big table the following error occured:
UPDATE image SET seriesoid = r.parentoid
FROM relseries_image000 r WHERE image.chilioid = r.childoid;
psql:restructure_join.sql:16: FATAL 2:
ZeroFill(/usr/local/pgsql/data/pg_xl
og/xlogtemp.22940) failed: No such file or directory
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
psql:restructure_join.sql:16: connection to server was lost
The file /usr/local/pgsql/data/pg_xl
og/xlogtemp.22940 doesn't exist on the disk.
The same query on the other table works well.
Why not at the bigger table?
What wants the error announcment say?
Do You know that kind of error?
I did not found any hint in the Archiv of the mailing lists?
I would be very gratful if anybody could give me a hint to clear the
obstacle.!
Thanks a lot
David
Attachment
Hallo!
Another try -- another kind of error
pacs=> UPDATE image SET seriesoid = r.parentoid
pacs-> FROM relseries_image000 r WHERE image.chilioid = r.childoid;
FATAL 2: ZeroFill(/usr/local/pgsql/data/pg_xlog/xlogtemp.30164) failed:
No such file or directory
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: NOTICE:
Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died
abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to
terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Failed.
?????????
???????
I will try ipcclean. And look for postmaster options!
Any ideas?
Thanks a lot
David
Attachment
"David M. Richter" <D.Richter@DKFZ-heidelberg.de> writes: > FATAL 2: ZeroFill(/usr/local/pgsql/data/pg_xlog/xlogtemp.30164) failed: > No such file or directory You ran out of disk space. Yes, the error message is misleading. That's fixed in current sources. See also http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html regards, tom lane