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

BUG #16427: pgsql_tmp - temp files not released

From
PG Bug reporting form
Date:
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?

Thanks,
Murali


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

From
Tomas Vondra
Date:
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.

regards

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



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

From
Euler Taveira
Date:
On Sun, 10 May 2020 at 09:32, Tomas Vondra <tomas.vondra@2ndquadrant.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.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).


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

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

From
Michael Paquier
Date:
On Sun, May 10, 2020 at 10:48:58AM -0300, Euler Taveira wrote:
> 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).

Sounds like a fair concern to me.  It is annoying to have to do some
manual intervention for some environments under disk pressure.
--
Michael

Attachment

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

From
Murali Natti
Date:
It again happened today and this time it happened in PG11.4 DB. We had to bounce the instance to clear the space.

On Sun, May 10, 2020 at 11:10 PM Michael Paquier <michael@paquier.xyz> wrote:
On Sun, May 10, 2020 at 10:48:58AM -0300, Euler Taveira wrote:
> 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).

Sounds like a fair concern to me.  It is annoying to have to do some
manual intervention for some environments under disk pressure.
--
Michael


--
Thanks,
Murali