Postgres 报告称,在执行相当大的聚合查询时磁盘空间不足:
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
但是磁盘有相当多的空间:
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
该查询正在执行以下操作:
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
有小费吗?
答案1
还要检查“df -i”的输出,它报告了正在使用的 inode 数量。用完 inode 也会产生“无可用空间”类型的错误,但您仍然有足够的可用空间。
答案2
感谢您的所有提示,从这个错误中我吸取了一些东西,但最终我的查询是错误的!!
我漏掉了或子句周围的括号!
我当然感觉自己像个白痴。