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

From Thierry Husson
Subject Re: Temp table handling after anti-wraparound shutdown (Was: BUG#15840)
Date
Msg-id 20190610184538.Horde.H9-kaCvy1XBQQjPjV06Ee0y@webmail.iciel.com
Whole thread Raw
In response to Re: Temp table handling after anti-wraparound shutdown (Was: BUG#15840)  (Andres Freund <andres@anarazel.de>)
List pgsql-hackers
> 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





pgsql-hackers by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: Should we warn against using too many partitions?
Next
From: Masahiko Sawada
Date:
Subject: Re: Small review comment on pg_checksums