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

[Thierry Husson]

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  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

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

[Thierry Husson]

Hm, I'd like to know more about that corruption. Did you report it when
it occured?

Greetings,

Andres Freund


Thanks Andres for explanations, sorry for my previous mess. I didn't  
reported the corruption when it occured as it was my fault, not a PG  
bug, as the main cause was that I was using network drive, knowingly  
it's unreliable for DB but management didn't believe me.


I had these kind of errors:

pg_dump emet_istina -F c -n usr_...
pg_dump: schema with OID 308991 does not exist

\dt+ pg_temp*.*
ERROR:  catalog is missing 1 attribute(s) for relid 5733555

drop schema pg_temp_9;
ERROR:  cache lookup failed for relation 5733715

drop schema pg_temp_6;
ERROR:  cannot drop schema pg_temp_6 because other objects depend on it
DETAIL:  table pg_temp_6.cur_dde000_105577 depends on schema pg_temp_6
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

I had to manualy remove/edit records from pg_class, pg_type, pg_namespace,
pg_depend, pg_shdepend.

I finaly managed to make it works and could dump everything and  
rebuild the DB for more security. Server was down for 1 week, and that  
event gave me proven arguments to have local storage. That was with  
9.6 and I took the opportunity to upgrade to 10.3 at the same time.


Now it's more clear it's a PG9/10/12 problem (didn't tried 11)  with  
vacuum/autovacuum not changing xid on temp tables. So, as long a temp  
table exists, it take age and finish by causing a wraparound protection.


Thierry