how can i tune postgres to avoid this error?

Question

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.

Answer 1

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 :-)

Answer 2

Add space on the disk it's writing to?

Answer 3

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.