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.

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?

share|improve this question
    
Sure, now that the query's stopped and the temporary files it was using are gone, the drive isn't as full anymore. What did df look like while the query was in progress? –  DerfK Mar 18 '11 at 22:15
    
am re-running and watching now. will take 2+ hrs to get to the fail point i suspect. –  pstanton Mar 18 '11 at 22:18
    
@DerfK,if it is that the query was paging that much data, would breaking the insert into multiple inserts by adding date range help? - currently it does 6 years at once, maybe do a year at a time? –  pstanton Mar 18 '11 at 23:30
    
@pstanton That should help. –  DerfK Mar 18 '11 at 23:46
    
@DerfK How would a query generate more than 5.9 GB of temp files? That's a ton of temp files –  TheLQ Mar 18 '11 at 23:57

2 Answers 2

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.

share|improve this answer
    
I'm going to admit I missed the point in the the OP's error output where it says "could not write block 31840050". As DerfK pointed out, postgres uses 8KiB blocks by default, so that query really is trying to write out 31840050 * 8KiB / (1024*1024) = 242.92 GiB. It might still be inode related, but that figure is close enough to the free space on / that it smells like a more likely culprit –  Daniel Lawson Mar 20 '11 at 21:10
up vote 0 down vote accepted

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.

share|improve this answer
    
Instead of letting people know the resolution of your issue here, just edit your original question. –  sciurus Mar 20 '11 at 22:32
1  
@sciurus i have to close it off somehow .. otherwise it will never be correctly answered. –  pstanton Mar 21 '11 at 2:03
    
Any idea why it was generating that out of space error? –  nilbus Mar 5 '12 at 16:47

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.