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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
