The other day I followed the docs and ran this query:
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
Then after identifying some tables that were close to triggering an
automatic vacuum, we vacuumed them only to find that the age of the
database's datfrozenxid hadn't gone down much. A little digging revealed
that some TOAST tables had some quite old xids hanging around. so I
think we need to change the query, maybe to something like:
select c.relname, int4larger(age(c.relfrozenxid), case when c.reltoastrelid = 0 then 0
else age(t.relfrozenxid) end) as age from pg_class c left join pg_class t on c.reltoastrelid = t.oid where
c.relkind= 'r'
Maybe for bonus points we'd print out the schema (e.g. by selectting
c.oid::regclass instead of c.relname), and also include materialized
views which are omitted from the query altogether.
Comments?
cheers
andrew
