Re: Change behavior of (m)xid_age

On 10/22/15 4:18 PM, Robert Haas wrote:
> On Wed, Oct 21, 2015 at 1:33 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
>> Currently, xid_age() returns INT_MAX for a permanent xid. The comment in the
>> function that 'Permanent XIDs are always infinitely old' may be technically
>> correct, but returning INT_MAX is a useless behavior because it actually
>> makes it look like that XID is in immediate wraparound danger. I think we
>> should change it to return either 0, -1, or INT_MIN. To me, 0 makes the most
>> sense for monitoring relfrozenxid.
>
> As far as I know, relfrozenxid is only a permanent XID for relkinds
> that don't have storage; then it's zero.  So I think you should just
> change your query to ignore pg_class rows where relfrozenxid = 0, and
> leave xid_age() alone.

It's also a permanent ID when the relation is first created.

I agree that you can just ignore relfrozenxid = 0, but it seems kinda 
silly to force everyone to do that (unless there's some use case for the 
current 'infinity behavior' that I'm not seeing).

BTW, ignoring relfrozenxid = 0 also isn't as easy as you'd think:

select count(*) from pg_class where relfrozenxid <> 0;
ERROR:  operator does not exist: xid <> integer at character 50

So first we make the user add the WHERE clause, then we make them figure 
out how to work around the missing operator...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com