Horton

Re: removal of dangling temp tables

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2018-12-14T18:35:50Z
Lists: pgsql-hackers 
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Dec 14, 2018 at 12:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I seem to recall discussions about having crash recovery go around
>> and clean out temp tables.  That seems like a better plan than
>> penalizing every session start with this.

> Well, crash recovery already removes the files, but it can't really
> remove the catalog entries, can it?

Hm.  It *could*, if we wanted it to run some transactions after
finishing recovery.  But I guess I wonder why bother; if the disk
space is gone then there's not that much reason to be in a hurry
to get rid of the catalog entries.  The particular problem Alvaro
is complaining of might be better handled by ignoring temp tables
while computing max freeze age etc.  I have some recollection that
we'd intentionally included them, but I wonder why really; it's
not like autovacuum is going to be able to do anything about an
ancient temp table.

Alternatively, maybe we could have backends flag whether they've
taken ownership of their temp schemas or not, and let autovacuum
flush old temp tables if not?

			regards, tom lane

Commits

  1. Have DISCARD ALL/TEMP remove leftover temp tables

  2. Make autovacuum more selective about temp tables to keep

  3. Make autovacuum more aggressive to remove orphaned temp tables

  4. autovacuum: Drop orphan temp tables more quickly but with more caution.