[ADMIN] Orphan TOAST object

Hi all,

 

I wonder whether any of you can help me out with this problem.  We were performed a routine "lazy" VACUUM in order to reassign frozen XIDs and prevent data-loss. 

 

After the VACUUM completed successfully, the command "SELECT datname, age(datfrozenxid) FROM pg_database" still showed an excess of 1,800,000,000 transactions from the cutoff XID of some table meaning that either the VACUUM somehow failed or else missed out a table.

 

Just to make sure, we re-ran the VACUUM but the result in the end was the same.  We then decided to find out which table was causing this problem by running the following query:

 

select relname from pg_class where relfrozenxid = (select datfrozenxid from pg_database where datname = 'CDR')

 

Since datfrozenxid in pg_database stores the oldest XID, using this query we were able to home on the database object which was allegedly being missed by the VACUUM.  The query returned a TOAST object: pg_toast_35027430.  This was puzzling; as far as I know pg_toasts objects can't be vacuumed directly b

ut only when vacuuming their parent.  This means that somehow this pg_toast object was orphaned, fact confirmed by the following query:

 

select relname from pg_class a where relname like 'pg_toast_3%' and relkind = 't' and not exists (

          select 1 from pg_class b where a.oid = b.reltoastrelid and relkind = 'r')

 

To get vacuum the TOAST object we created a temporary table foo (col1 char(1)) and assigned its reltoastrelid (up till now set to 0) to pg_toast_35027430's OID and then vacuumed foo.  The plan worked and immediately age(datfrozenxid) in pg_database reflected a much younger XID.

 

We then decided to get rid of pg_toast_35027430 by dropping foo.  Foo disappeared but pg_toast_35027430 persisted.  I'd like to get rid of it because in a few months' time we will bump into the same problem again.  Does anyone have any idea how this can be removed manually without causing any unwarranted damage to the system catalogue?

 

Also, can one un-vacuumed database object cause dataloss in other unrelated tabled which have otherwise been VACUUMed and their respective FrozenXIDs reassigned?

 

Thank you in advance,

 

James.