Обсуждение: max freeze age query in docs
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
> 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. Given the importance of this, maybe we need to have it as part of pg_stat_user_tables? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 09/01/2013 10:33 PM, Josh Berkus wrote: >> 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. > Given the importance of this, maybe we need to have it as part of > pg_stat_user_tables? > > Yes, possibly, but we can't do that now, but I would like to fix the docs now. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Yes, possibly, but we can't do that now, but I would like to fix the
> docs now.
If you want this in 9.3.0 it needs to be committed in the next couple of
hours.
FWIW, the idea seemed generally sane to me, but I'd suggest not depending
on reltoastrelid being zero when and only when there's no match.
Why not test whether t.oid IS NULL, instead?
Or actually, code it like this
GREATEST(age(c.relfrozenxid), age(t.relfrozenxid))
and be done, as well as not having an ugly direct use of int4larger.
regards, tom lane
On 09/02/2013 01:30 PM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> Yes, possibly, but we can't do that now, but I would like to fix the >> docs now. > If you want this in 9.3.0 it needs to be committed in the next couple of > hours. > > FWIW, the idea seemed generally sane to me, but I'd suggest not depending > on reltoastrelid being zero when and only when there's no match. > Why not test whether t.oid IS NULL, instead? > > Or actually, code it like this > > GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) > > and be done, as well as not having an ugly direct use of int4larger. > > OK, I'll do it that way. Working on it now. cheers andrew cheers andrew
On 2013-09-02 14:20:57 -0400, Andrew Dunstan wrote: > > On 09/02/2013 01:30 PM, Tom Lane wrote: > >Andrew Dunstan <andrew@dunslane.net> writes: > >>Yes, possibly, but we can't do that now, but I would like to fix the > >>docs now. > >If you want this in 9.3.0 it needs to be committed in the next couple of > >hours. > > > >FWIW, the idea seemed generally sane to me, but I'd suggest not depending > >on reltoastrelid being zero when and only when there's no match. > >Why not test whether t.oid IS NULL, instead? > > > >Or actually, code it like this > > > > GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) > > > >and be done, as well as not having an ugly direct use of int4larger. > > > > > > > OK, I'll do it that way. Working on it now. I'd vote for c.relkind != 't' AND NOT c.relfrozenxid = 0; instead of relkind = 'r' for the main relation, that way you'd include materialized views and stuff. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 09/02/2013 02:26 PM, Andres Freund wrote: > On 2013-09-02 14:20:57 -0400, Andrew Dunstan wrote: >> On 09/02/2013 01:30 PM, Tom Lane wrote: >>> Andrew Dunstan <andrew@dunslane.net> writes: >>>> Yes, possibly, but we can't do that now, but I would like to fix the >>>> docs now. >>> If you want this in 9.3.0 it needs to be committed in the next couple of >>> hours. >>> >>> FWIW, the idea seemed generally sane to me, but I'd suggest not depending >>> on reltoastrelid being zero when and only when there's no match. >>> Why not test whether t.oid IS NULL, instead? >>> >>> Or actually, code it like this >>> >>> GREATEST(age(c.relfrozenxid), age(t.relfrozenxid)) >>> >>> and be done, as well as not having an ugly direct use of int4larger. >>> >>> >> >> OK, I'll do it that way. Working on it now. > I'd vote for c.relkind != 't' AND NOT c.relfrozenxid = 0; instead of > relkind = 'r' for the main relation, that way you'd include materialized > views and stuff. > See what was just committed - the matview case is included for 9.3+ (as it was in fact in the original - I must have been looking at older docs when saw it wasn't there.) I'll be back in an hour or so if any final tweeks are needed. cheers andrew