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

I know this thread is a bit old but thought this could help someone as I was having a similar issue and had no option of extending the storage on the remote host.

Another way I managed to resolve it was to run a script to select records in chunks and iterate through the records until the last result returns no records. For example, something like the below should work for you.

Note the order by, this is very important, or else, you may be selecting records that already been selected and end up with duplicates (i have had that).

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) 
order by t1.a, t1.b
limit 100 offset 0
GROUP BY month, t, a, b, d, r, h, x

In next run execution, you adjust offset to 100 and so on.

Hope it helps someone.:)

Answer 4

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.