On Sun, May 10, 2020 at 12:44:18AM +0000, PG Bug reporting form wrote: >The following bug has been logged on the website: > >Bug reference: 16427 >Logged by: Murali Natti >Email address: murali.natti@gmail.com >PostgreSQL version: 10.5 >Operating system: Red Hat Enterprise Linux Server release 6.10 (Sant >Description: > >Hello Team, > >We have observed a bug ( I guess ) that temporary files were not deleted >automatically even thought the respective PIDs are not running anymore. > >We couldn't take a chance to delete files manually so ended up with >restarting database to clear temp files. > >Is this know bug, what could be the reason for this? Can anyone please let >us know? >
I don't think we're aware of any live bug in this area (although you're a couple of minor releases behind, and I haven't checked release notes between 10.5 and 10.12).
I think you need to investigate what happened to the backends/commands that created the temporary files. Presumably it was interrupted or terminated in a way that prevented proper cleanup.
I bet OP has crash(es) while executing a query that uses temporary files. Crash does not cleanup temporary files until you restart the service. RemovePgTempFiles says:
* NOTE: we could, but don't, call this during a post-backend-crash restart * cycle. The argument for not doing it is that someone might want to examine * the temp files for debugging purposes. This does however mean that * OpenTemporaryFile had better allow for collision with an existing temp * file name.
I wonder if a GUC (cleanup_temp_files_after_crash) is useful for an environment that has storage restrictions. This is the second time this month that I stumbled on this issue. Of course, I don't expect a postgres crash once a while but we know that, due to extensibility, some extensions could crash and the only workaround to this issue is to restart service (which means a human intervention and an additional downtime).
