postgresql – 如何调整postgres以避免此错误？

发布时间：2020-12-13 16:24:21 所属栏目：百科来源：网络整理

导读：我收到了错误 ERROR: could not write block 3478284 of temporary file: No space left on device 运行以下查询时： 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

我收到了错误

ERROR: could not write block 3478284
of temporary file: No space left on
device

运行以下查询时：

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

表t1非常大,表t2非常大

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)

任何提示赞赏.

在尝试完成您的请求时,Postgres的临时转储空间中的空间不足 – 修复此问题的方法是运行更简单的查询(可能没有用)或者释放更多空间PGD??ATA / base / pgsql_tmp /(如果你现在还没有完成一个VACUUM现在可能是个好时机:-)

你也可以将pgsql_tmp放在它自己的分区上(注意权限,因为Postgres往往会对这些事情有所了解)

Note that I believe pgsql_tmp is per-tablespace these days,so if this isn’t the main (base) tablespace substitute appropriately
（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!