Bogus temp file reporting?

[kg . postgresql]

Hello,

Is there a known issue with temp file reporting?  I have a production db that 
is reporting 231TB of temp space usage, which can’t be true.  temp_blks_written 
in pg_stat_statements sort of confirms this, as the reported queries show just 
a few MB of temp space usage.  I suspect a reboot will clear this (to do) and 
wanted to check with you to see if someone else has had similar experiences.

Regards,
Kiriakos Georgiou


select version();
  version   
  
---
PostgreSQL 14.8 (Ubuntu 14.8-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by 
gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
 
 
select   
datname,
temp_files as "# of temp files",
pg_size_pretty(temp_bytes) as "size of temp files"
from
pg_stat_database;

   datname | # of temp files | size of temp files
---+-+
   |   0 | 0 bytes
postgres   |   0 | 0 bytes
testdb |  269647 | 231 TB
template0  |   0 | 0 bytes
template1  |   0 | 0 bytes

Re: Bogus temp file reporting?

[kg . postgresql]

Thanks for the clarification.  Also, I was wrong about the temp_* columns in 
the pg_stat_database view in that they report the overall temp file usage since 
the last stats reset and not the “current” usage.

Regards,
Kiriakos Georgiou
 

> On Jul 27, 2023, at 11:31 AM, Kyotaro Horiguchi  
> wrote:
> 
> At Thu, 27 Jul 2023 10:30:41 +0300, kg.postgre...@olympiakos.com 
>  wrote in 
>> Hello,
>> 
> 
>> Is there a known issue with temp file reporting?  I have a
>> production db that is reporting 231TB of temp space usage, which
>> can’t be true.  temp_blks_written in pg_stat_statements sort of
>> confirms this, as the reported queries show just a few MB of temp
>> space usage.  I suspect a reboot will clear this (to do) and wanted
>> to check with you to see if someone else has had similar
>> experiences.
> 
> 
> Just to clarify, "temprary files" and "temp blocks" refer to different
> entities in this context. The "temprary files" are ephemeral storage
> that is created and alive only for tasks like tuple sorting, whereas
> "temp blocks" are referring to the storage for temprary tables.
> 
> If you're performing a massive insert into a temporary table, you'll
> find that pg_stat_database.temp_bytes doesn't increase at all.
> 
> regards.
> 
> -- 
> Kyotaro Horiguchi
> NTT Open Source Software Center