Re: Temp table handling after anti-wraparound shutdown (Was: BUG#15840)

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