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'm getting the error

ERROR: could not write block 3478284 of temporary file: No space left on device

when running the following query:

INSERT INTO summary SELECT t1.a, t1.b, SUM(t1.p) AS p, COUNT(t1.*) AS c,
    t1.d, t1.r, DATE_TRUNC('month', t1.start) AS month, t2.t AS t, t2.h, t2.x
FROM raw1 t1, raw2 t2
WHERE t1.t2_id=t2.id AND (t2.t<>'a' OR t2.y) GROUP BY month, t, a, b, d, r, h, x

table t1 is very large, and table t2 is pretty large

Caused by: org.postgresql.util.PSQLException: ERROR: could not write block 3478284 of temporary file: No space left on device
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:304)

any hints appreciated.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

Postgres is running out of space in its temporary dumping ground while trying to complete your request -- The way to fix this is to either run a simpler query (which probably isn't helpful) or to free up more space on the drive that holds PGDATA/base/pgsql_tmp/ (If you haven't done a VACUUM FULL in a while now may be a good time :-)

You can also put pgsql_tmp on its own partition (mind the permissions as Postgres tends to get snippy about those things)

Note that I believe pgsql_tmp is per-tablespace these days, so if this isn't the main (base) tablespace substitute appropriately :-)

share|improve this answer

My bet is that postgres is using /tmp for temporary storage and your root partition is smaller than partitions in your system. If you change postgres temporary storage to a place where you have more space you can fix this. Or as the above post says add more space. To confirm this is writing in /tmp look in the configuration file or while running the query run ls -lart /tmp over and over and if files are growing then that is where it is writing. I am not a postgres user but for the most part you should be able to figure it out with what I said above.

share|improve this answer
    
PostgreSQL will normally never use /tmp for temp space. –  Magnus Hagander Feb 8 '10 at 11:13

Add space on the disk it's writing to?

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.