Thread: BUG #16739: Temporary files not deleting from data folder on disk
Thanks for writing back.
I have checked the files, but these files are temporary and vanish once the Postgres service is restarted.
This has been seen many times.
Adding more data to support my point in form of attachment.
PG Bug reporting form <noreply@postgresql.org> writes:
> There is a problem with Postgres that temporary tables being created are not
> cleaning up by Postgres itself.
Can you produce any actual evidence of that, or even better a reproduction
sequence?
> Count of temporary files-
> select count(*) from pg_ls_dir ( '/var/lib/pgsql/10/data/base/16384' ) as
> file where file::text not in (select oid::text from pg_class );
This is not evidence, because the test is completely incorrect.
* OID is not the pg_class field to use. relfilenode is closer,
although that will still mislead you for certain system catalogs.
pg_relation_filenode(oid) is really the recommended way to get the
base file name for a pg_class entry.
* Even once you've got the right filename, it's only the *base* file
name. There might be additional segments (nnn.1, nnn.2, etc) if any
table exceeds 1GB. There are likely to also be "nnn_fsm" and
"nnn_vm" subsidiary files, and maybe "nnn_init" files. All of those
would have to be excluded before concluding that files are being
leaked.
See
https://www.postgresql.org/docs/current/storage.html
for more info about what you should expect to see in a database
directory.
regards, tom lane
Disclaimer: The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.
Attachment
I am struggling to find out the name of the table corresponding to these temporary files (files starting with t on, saved on disk).
If somehow I can discover these, it will be helpful to close down the problem.
 | Manoj Kumar      |
Maxim Boguk <maxim.boguk@gmail.com> writes:
> It seems that these temp files are the result of normal query execution
> (and will be cleared when query finishes).
> Have you tried enabling log_temp_files and check what query generates these
> temp files?
Judging by the file names, these are temp tables (created by CREATE TEMP
TABLE or the like), not temp files generated during query execution.
The latter would have names beginning in "pgsql_tmp", if I read the
code correctly.
regards, tom lane
Disclaimer: The information in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Drishti is neither liable for the improper, incomplete transmission of the information contained in this communication nor any delay in its receipt. The communication is not intended to operate as an electronic signature under any applicable law. Drishti assumes no responsibility for any loss or damage resulting from the use of e-mails.