Обсуждение: Bogus temp file reporting?

At Thu, 27 Jul 2023 10:30:41 +0300, kg.postgresql@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

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