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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM2a7549f37a7311ca111801c30f867058f43b733081b3fda2d64c697f4ff4673c2b1cec7f7163d87e077e4405cf490efa@asav-3.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers