I like the approach proposed by Andres: A more aggressive approach
would be to teach vac_update_datfrozenxid() to ignore orphaned temp
tables... In fact, I suppose all temporary tables and their content
could be completly ignored by MVCC principles as they are not subject
to concurrency being unmodifiable/unreadable by other connections.
That would solve a major problem I have because I automaticaly create
an empty temporary table for each connection in each DB process to
manage users' activities/system resources. Even when everything goes
well, these tables take age as long as they exists, even if I
explicitly vacuum them, frozen or not. So any connection kept open for
a long time will finish by causing a anti-wraparound shutdown. For now
the only solution I have is to kill my deamons connections every day.
I suppose this could be tested by a simple PSQL left open after a
CREATE TEMP TABLE toto (a INT). Any vacuum can't reduce its age.
The separate situation, as noted by Michael, could be done at
connection time, when PG gives a temporay schema to it. When it create
a pg_temp_XXX schema, it could make sure it's completely empty and
otherwise remove everything in it. I already had a DB corruption
because system tables weren't in sync about these tables/schemas after
a badly closed connection, so it was impossible to make a drop table
on them. So it could be even safer to clear everything directly from
system tables instead of calling drop table for each leftover temp
table.
Thierry
Michael Paquier <michael@paquier.xyz> a écrit :
> On Fri, Jun 07, 2019 at 05:26:32PM -0700, Andres Freund wrote:
>> I was more thinking that we'd move the check for orphaned-ness into a
>> separate function (maybe IsOrphanedRelation()), and move the code to
>> drop orphan relations into a separate function (maybe
>> DropOrphanRelations()). That'd limit the amount of code duplication for
>> doing this both in autovacuum and all-database vacuums quite
>> considerably.
>
> A separation makes sense. At some point we should actually try to
> separate vacuum and orphan relation cleanup, so separate functions
> make sense. The only reason why we are doing it with autovacuum is
> that it is the only thing in-core spawning a worker connected to a
> database which does a full scan of pg_class.
> --
> Michael