Skip site navigation (1) Skip section navigation (2)

Orphan TOAST object

From: "James Farrugia" <james(dot)farrugia(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Orphan TOAST object
Date: 2008-05-11 16:46:23
Message-ID: 8d01be800805110946r792efacdp3dbe02829b6da7a9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2008-05-11 17:09:48
Subject: Re: Orphan TOAST object
Previous:From: Tino SchwarzeDate: 2008-05-11 14:45:47
Subject: Re: Require security ACCESS matrix in prostgresql !!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group