postgresql No space left on device

Postgres is reporting that it is out of disk space while performing a rather large aggregation query:

Caused by: org.postgresql.util.PSQLException: ERROR: could not write block 31840050 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)
    at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:189)
    ... 8 more

However the disk has quite a lot of space:

Filesystem            Size Used Avail Use% Mounted on
/dev/sda1             386G  123G  243G  34% /
udev                  5.9G  172K  5.9G   1% /dev
none                  5.9G     0  5.9G   0% /dev/shm
none                  5.9G  628K  5.9G   1% /var/run
none                  5.9G     0  5.9G   0% /var/lock
none                  5.9G     0  5.9G   0% /lib/init/rw

The query is doing the following:

INSERT INTO summary_table SELECT t.a, t.b, SUM(t.c) AS c, COUNT(t.*) AS count,
    t.d, t.e, DATE_TRUNC('month', t.start) AS month, tt.type AS type, FALSE, tt.duration
FROM detail_table_1 t, detail_table_2 tt
WHERE t.trid=tt.id  AND tt.type='a'
    AND DATE_PART('hour', t.start AT TIME ZONE 'Australia/Sydney' AT TIME ZONE 'America/New_York')>=23
        OR DATE_PART('hour', t.start AT TIME ZONE 'Australia/Sydney' AT TIME ZONE 'America/New_York')<13
GROUP BY month, type, t.a, t.b, t.d, t.e, FALSE, tt.duration

any tips?

Also check the output of "df -i", which reports the number of inodes in use. Running out of inodes will also tend to generate "no free space" type errors, but you still have plenty of free space.

thanks for all the tips, a learn a few things from this mistake but ultimately my query was wrong!!

i missed parenthesis around an or clause!

of course i feel like an idiot.

postgresql No space left on device

Related

Recent Posts