Thread: 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