Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have been running a query that (I assume) generates a large amount of temporary information, as I receive the following error:

ERROR:  could not write block 16451641 of temporary file: No space left on device
HINT:  Perhaps out of disk space?

********** Error **********

ERROR: could not write block 16451641 of temporary file: No space left on device
SQL state: 53100
Hint: Perhaps out of disk space?

I made a folder tmp_tablespace on my G: drive and then edited postgresql.conf and restarted the database.

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public'     # schema names
#default_tablespace = ''        # a tablespace name, '' uses the default
temp_tablespaces = 'G:/tmp_tablespace/'         # a list of tablespace names, '' uses

As far as I can tell, the temporary tablespace is not being used, as no files are written there (at least none that I can see when refreshing). In the Windows Options menu, Show Hidden Files is checked, and I've refreshed the folder a few times while this processing was running, as well as checking the folder size which stayed at 0.

In my SQL code, I also tried using the lines SET temp_tablespaces('G:\tmp_tablespace'); and temp_tablespaces('G:\tmp_tablespace'); but both lines returned error messages.

I'm using PostGreSQL 9.1 32 bit and Windows 7. Are there any other settings I should be checking? My PostGreSQL installation is on the C:\ drive and the associated tablespace is on the G:\ drive.

share|improve this question

2 Answers 2

You need to create the new tablespace with CREATE TABLESPACE and then specify the name of the tablespace in your configuration, not the name of the underlying directory.

share|improve this answer
    
Is it a similar command to creating a normal tablespace? I just want the temporary files there, rather than all files and if I use CREATE TABLESPACE dbspace LOCATION '/data/dbs'; I assume it moves everything. –  celenius Feb 21 '12 at 15:49
    
Why do you assume that? –  Milen A. Radev Feb 21 '12 at 16:21
    
From the docs, CREATE TABLESPACE registers a new cluster-wide tablespace. The tablespace name must be distinct from the name of any existing tablespace in the database cluster.` postgresql.org/docs/9.1/static/sql-createtablespace.html –  celenius Feb 21 '12 at 17:28
    
Nothing will move when you create a new tablespace - it will be empty until you tell postgres to put something in it. Such as by telling it to put temporary tables there. –  TomH Feb 21 '12 at 21:02
    
@TomH Sorry, I'm still not clear on how to do this. So if I run CREATE TABLESPACE dbspace LOCATION 'J:\data' I create a tablespace, but how do I tell it to store the temporary tables there? Is it similar to temp_tablespaces('J:\data') ? –  celenius Feb 22 '12 at 0:57

After making the folder tmp on drive J, the approach that I took was as follows:

CREATE TABLESPACE dbspace_tmp LOCATION 'J:\tmp';
CREATE DATABASE dbspace_tmp tablespace dbspace_tmp;
ALTER DATABASE my_db SET temp_tablespaces = dbspace_tmp;

This appears to be working as it created a file in this location and it seems to be storing temporary data in there.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.