Re: BUG #16427: pgsql_tmp - temp files not released

On Sun, 10 May 2020 at 09:32, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

> 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(dot)natti(at)gmail(dot)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).

--
Euler Taveira http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services