Обсуждение: BUG #16739: Temporary files not deleting from data folder on disk
The following bug has been logged on the website: Bug reference: 16739 Logged by: Manoj Kumar Email address: manojkumar@ameyo.com PostgreSQL version: 10.8 Operating system: Centos OS 7.6 Version 0 Description: There is a problem with Postgres that temporary tables being created are not cleaning up by Postgres itself. We have an AutoVacuum job going on for the cleanup as well. However, when we restart the Postgres service this data is cleaned up by itself We are facing huge issues with this problem, as our customer database size is tremendously increasing from 80GB (actual Size) to 7TB. Hampering the live production. need a solution for this ASAP. Database size on Disk- weblogin_setup7 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7338 GB | pg_default | 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 ); count ------- 95263 (1 row) The Actual size of the database - 38 GB
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
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.
Вложения
Manoj Kumar <manojkumar@ameyo.com> writes: > Adding more data to support my point in form of attachment. Well, those t-something files are certainly temporary tables, so what you've got there is that some session has created a bunch of quite large temporary tables (the "t20_" files) and another has created a bunch of rather smaller ones (the "t62_" group). However, this is not direct evidence of PG misbehavior. You've offered no reason to think that the sessions that made those files have exited. Even if you're sure they have, we can't do anything to help you on the basis of the zero facts you've supplied so far about what the sessions were doing. If you can provide a reproducible test case that leaks temp tables, we could get to the bottom of it pretty quickly, I'm sure. But we cannot help you on the basis of what you've shown us. regards, tom lane
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.
Senior Postgresql DBA
https://dataegret.com/
Phone RU: +7 985 433 0000
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
"Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
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
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.
Вложения
Hello, I am experiencing the same issue on an up-to-date Postgres (13.11). I have many files corresponding to temporary tables (for example t7_4191281453.90). These files are not cleaned until we manually restart Postgres (all client session are closed). When temporary table were created, we reached disk full (Disk quota exceeded). Then the instance crashed and restarted in recovery mode. But files related to temporary tables still exist. It seems there is something missing to clean these files after a recovery ? Regards, -- Adrien NAYRAT
On 6/5/23 11:54, Adrien Nayrat wrote: > Hello, > > > I am experiencing the same issue on an up-to-date Postgres (13.11). > I have many files corresponding to temporary tables (for example > t7_4191281453.90). These files are not cleaned until we manually restart > Postgres (all client session are closed). > > When temporary table were created, we reached disk full (Disk quota > exceeded). Then the instance crashed and restarted in recovery mode. But > files related to temporary tables still exist. > > It seems there is something missing to clean these files after a recovery ? > > Regards, > Replying to myself when reading this item in PG 14 release notes: Remove temporary files after backend crashes (Euler Taveira) Previously, such files were retained for debugging purposes. If necessary, deletion can be disabled with the new server parameter remove_temp_files_after_crash. I understand, it is expected to keep these files for debugging purpose after a recovery. And since 14, we can ask Postgres to clean them thanks to remove_temp_files_after_crash
On Mon, Jun 5, 2023 at 3:29 PM Adrien Nayrat <adrien.nayrat@anayrat.info> wrote: > > > > I am experiencing the same issue on an up-to-date Postgres (13.11). > > I have many files corresponding to temporary tables (for example > > t7_4191281453.90). These files are not cleaned until we manually restart > > Postgres (all client session are closed). > > > > Replying to myself when reading this item in PG 14 release notes: > > Remove temporary files after backend crashes (Euler Taveira) > > Previously, such files were retained for debugging purposes. If > necessary, deletion can be disabled with the new server parameter > remove_temp_files_after_crash. > > > I understand, it is expected to keep these files for debugging purpose > after a recovery. And since 14, we can ask Postgres to clean them thanks > to remove_temp_files_after_crash IIUC, this flag is for the temp files created for query level operation so this will clean up the files like "pgsql_tmp" but in this thread, we are talking about the files related to temp tables starting with "t_" -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com
On 6/5/23 12:14, Dilip Kumar wrote: > IIUC, this flag is for the temp files created for query level > operation so this will clean up the files like "pgsql_tmp" but in > this thread, we are talking about the files related to temp tables > starting with "t_" I am not sure per comment and code in fd.c : /* * Remove temporary and temporary relation files left over from a prior * postmaster session * * This should be called during postmaster startup. It will forcibly * remove any leftover files created by OpenTemporaryFile and any leftover * temporary relation files created by mdcreate. * * During post-backend-crash restart cycle, this routine is called when * remove_temp_files_after_crash GUC is enabled. Multiple crashes while * queries are using temp files could result in useless storage usage that can * only be reclaimed by a service restart. The argument against enabling it is * that someone might want to examine the temporary files for debugging * purposes. This does however mean that OpenTemporaryFile had better allow for * collision with an existing temp file name. The function RemovePgTempFiles is called, which call RemovePgTempRelationFiles, then RemovePgTempRelationFilesInDbspace and looks_like_temp_rel_name which check: /* t<digits>_<digits>, or t<digits>_<digits>_<forkname> */ -- Adrien NAYRAT
On Mon, Jun 5, 2023 at 4:10 PM Adrien Nayrat <adrien.nayrat@anayrat.info> wrote: > > I am not sure per comment and code in fd.c : > Looking at the code, I think you are right, I was just confused thanks for pointing that out. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com