max freeze age query in docs

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: max freeze age query in docs
Date: 2013-09-02 00:34:08
Message-ID: 5223DD00.4060002@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2013-09-02 02:33:32 Re: max freeze age query in docs
Previous Message Greg Stark 2013-09-01 21:54:15 Re: [v9.4] row level security