Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Wed, Feb 07, 2018 at 12:37:50AM +0000, Tsunakawa, Takayuki wrote:
> From: Michael Paquier [mailto:michael.paquier@gmail.com]
>> I am not sure that we would like to give up that easily the property that
>> we have now to clean up past temporary files only at postmaster startup
>> and only when not in recovery.  If you implement that, there is a risk that
>> the backend you are starting is eating the connection slot and by consequence
>> its temporary schema and its set of temporary tables on which one may want
>> to look into after a crash.
> 
> postmaster deletes temporary relation files at startup by calling
> RemovePgTempFiles() regardless of whether it's in recovery.  It
> doesn't call that function during auto restart after a crash when
> restart_after_crash is on.

The comment on top of RemovePgTempFiles() states the following:
 * 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.

>> > 2. Teach autovacuum to remove any leftover tables in a pg_temp_%d
>> > schema if the backend is active but in some other database (rather
>> > than only when the backend is not active at all).
>> 
>> Yeah.  Here we can do something.  This does not sound much difficult to
>> me.
> 
> I did that in my patch.

Nice to hear that.  Please note that I did not check your patch, so I
cannot conclude on its correctness in details.
--
Michael