Re: Change behavior of (m)xid_age

On Thu, Oct 22, 2015 at 5:51 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> It's also a permanent ID when the relation is first created.

No it isn't.  If it were, the first insert into the table would have
to update the pg_class tuple, which it certainly doesn't.  Before we
had MVCC catalog scans, that wouldn't have been possible with less
than AccessExclusiveLock, and it would still require a self-exclusive
relation lock, which would be a deadlock hazard if multiple processes
tried to access the relation at once.  Also:

rhaas=# create table foo (a int);
CREATE TABLE
rhaas=# select relfrozenxid from pg_class where relname = 'foo';relfrozenxid
--------------         946
(1 row)

> 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).

Well, if the only purpose of age() were to be applied to every
pg_class.relfrozenxid value, I might agree with you.  But I'm not sure
that's so; for example, it could be applied to XID fields from
individual tuples.  And there is certainly a backward-compatibility
argument for not changing the semantics now.

> 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

It takes a few more characters than that, but it's not really that hard.

rhaas=# select count(*) from pg_class where relfrozenxid::text <> '0';count
-------   81
(1 row)

You can alternatively search for the correct set of relkinds.

> So first we make the user add the WHERE clause, then we make them figure out
> how to work around the missing operator...

Before any of that, we make them learn what relfrozenxid is and what
age() does.  Once they've learned that, I don't think the few extra
characters to filter out zeroes is really a big deal.  Most of these
queries are presumably being issued by monitoring software anyway, and
hopefully commonly-used monitoring tools already include a suitable
query.  Rolling your own monitoring queries from scratch for a
high-value production system is not an especially good idea.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company