Why does postgresql prompt error 'perhaps out of disk space' while there is enough disk space?

Question

I running this query in postgresql on ubuntu 12.0.4 :

SELECT t2.p AS prop, t2.o AS obj, COUNT(t2.o) AS num 
FROM "class_Event" AS t1, 
  ((SELECT s,p,o FROM "prop_sliceHasAnomaly")  
   UNION (SELECT s,p,o FROM "prop_eventHasDuration")  
   UNION (SELECT s,p,o FROM "prop_sliceHasEndEvent")  
   UNION (SELECT s,p,o FROM "prop_eventPrecedeInCPU")  
   UNION (SELECT s,p,o FROM "prop_eventFollowInTask")  
   UNION (SELECT s,p,o FROM "prop_topObjectProperty")  
   UNION (SELECT s,p,o FROM "prop_eventDetails")  
   UNION (SELECT s,p,o FROM "prop_switchTo")  
   UNION (SELECT s,p,o FROM "prop_eventIsExecutedOn")  
   UNION (SELECT s,p,o FROM "prop_runningAction")  
   UNION (SELECT s,p,o FROM "prop_anomalyHasSlice")  
   UNION (SELECT s,p,o FROM "prop_eventPrecedeInTrace")  
   UNION (SELECT s,p,o FROM "prop_sliceHasStartEvent")  
   UNION (SELECT s,p,o FROM "prop_eventHasActiveDuration")  
   UNION (SELECT s,p,o FROM "prop_eventFollowInTrace")  
   UNION (SELECT s,p,o FROM "prop_runningTask")  
   UNION (SELECT s,p,o FROM "prop_eventOrdering")  
   UNION (SELECT s,p,o FROM "prop_domain")  
   UNION (SELECT s,p,o FROM "prop_sliceHasFunctionality")  
   UNION (SELECT s,p,o FROM "prop_traceContainsEvent")  
   UNION (SELECT s,p,o FROM "prop_eventHasDurationFromPreviousOccurrence")  
   UNION (SELECT s,p,o FROM "prop_eventPrecedeOccurrence")  
   UNION (SELECT s,p,o FROM "prop_eventPrecedeinTask")  
   UNION (SELECT s,p,o FROM "prop_switchFrom")  
   UNION (SELECT s,p,o FROM "prop_eventEndAt")  
   UNION (SELECT s,p,o FROM "prop_subPropertyOf")  
   UNION (SELECT s,p,o FROM "prop_eventFollowOccurrence")  
   UNION (SELECT s,p,o FROM "prop_eventFollowInCPU")  
   UNION (SELECT s,p,o FROM "prop_subClassOf")  
   UNION (SELECT s,p,o FROM "prop_eventHasDurationToNextOccurrence")  
   UNION (SELECT s,p,o FROM "prop_requestComponent")  
   UNION (SELECT s,p,o FROM "prop_eventStartAt")  
   UNION (SELECT s,p,o FROM "prop_range")  
   UNION (SELECT s,p,o FROM "prop_functionalityHasSlice") ) AS t2 
WHERE t1.s = t2.s 
GROUP BY t2.p, t2.o 
HAVING (COUNT(t2.o) > 1) 

But I came out with this error from posgresql :

ERROR:  could not write block 713 of temporary file: Aucun espace disponible sur le périphérique
HINT:  Perhaps out of disk space?

********** Erreur **********

ERROR: could not write block 713 of temporary file: Aucun espace disponible sur le périphérique
État SQL :53100
Astuce : Perhaps out of disk space?

I've checked that there is enough of disk space with the command df -h

df -h
df: «/var/lib/lightdm/.gvfs»: Permission non accordée
Sys. de fichiers Taille Utilisé Dispo Uti% Monté sur
/dev/sda5           37G     35G   46M 100% /
udev                16G    4,0K   16G   1% /dev
tmpfs              6,3G    984K  6,3G   1% /run
none               5,0M       0  5,0M   0% /run/lock
none                16G    216K   16G   1% /run/shm
/dev/sda6           37G    411M   35G   2% /opt
/dev/sda9          499G    435G   40G  92% /home
/dev/sda7           37G    4,7G   30G  14% /usr
/dev/sda8           37G    5,1G   30G  15% /usr/local

and it appears that there is 40GB free space disk on the /home. So I wonder what make this error to raise? is there a disk-space limitation parameter that can be set in postgresql configuration?

someone have an idea about this ?

Answer 1

It's writing a temporary file. They're written to temp_tablespaces, for which which:

The default value is an empty string, which results in all temporary objects being created in the default tablespace of the current database.

meaning it's going to be writing tempfiles into your data_directory. To see where that is, run SHOW data_directory. I expect you'll get a result like /var/lib/pgsql/9.3/data/, indicating that PostgreSQL's data is in /var.

(Make sure to check this; if your temp tablespace is on a tempfs, then it's basically a ramdisk, and you should move it elsewhere because that's bad for performance as well as being a problem for big tempfiles.)

On your system, /var is part of the / filesystem, which is full. So the partition containing your PostgreSQL data is full, and PostgreSQL is correctly reporting this as an error.

Options to free space include:

• Making sure old tempfiles in /tmp are being removed

• Removing old log files from /var/log

• Removing old PostgreSQL logs. Their location is somewhat variable based on OS/distro, which you didn't mention; they'll usually be in /var/log/postgresql/ or /var/lib/pgsql/9.3/data/pg_log. Important: do not remove anything else in the PostgreSQL data dir; in particular, pg_xlog and pg_clog are vital parts of the database system and must not be removed, moved, or altered in any way.

• Dropping or TRUNCATEing tables in PostgreSQL (destroys data permanently)

• DROPping unwanted indexes in PostgreSQL

• Uninstalling programs

• ...

but the best option is:

• Get a bigger disk.