Change behavior of (m)xid_age

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.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-10-22 18:07:06 -0400, Robert Haas wrote:

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';

"WHERE NOT relfrozenxid = 0" should work as well and is a bit nicer.

FWIW, adding an <> operator for xid seems like a perfectly good idea.

- Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund wrote:

FWIW, adding an <> operator for xid seems like a perfectly good idea.

+1 (two of them actually -- another one for <>(xid,int) which mirrors
the =(xid,int) we already have).

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, 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 10/22/15 5:07 PM, Robert Haas wrote:

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.

Is there no case where it can be a permanent XID for a table or toast table?

The other issue is relminmxid, which if you're looking at relfrozenxid
you'd want to look at as well. So you can't do a simple WHERE here.

Perhaps a better way to handle this is to just add correctly computed
age fields to pg_stat_all_tables and pg_stat_database.
--
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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 22, 2015 at 6:57 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

Is there no case where it can be a permanent XID for a table or toast table?

I don't think so.

The other issue is relminmxid, which if you're looking at relfrozenxid you'd
want to look at as well. So you can't do a simple WHERE here.

I don't really know what you're referring to here, but it doesn't sound serious.

Perhaps a better way to handle this is to just add correctly computed age
fields to pg_stat_all_tables and pg_stat_database.

I'll defer to others on that point.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Oct 23, 2015 at 7:20 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Andres Freund wrote:

FWIW, adding an <> operator for xid seems like a perfectly good idea.

+1. I have wanted that more than once, but avoided it all the time
with some casts.

+1 (two of them actually --

See for example the attached (do we care about commutativity with int
= xid and int <> xid?).

(another one for <>(xid,int) which mirrors the =(xid,int) we already have).

To which one are you referring here?
--
Michael

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

On 23/10/2015 14:59, Michael Paquier wrote:

On Fri, Oct 23, 2015 at 7:20 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Andres Freund wrote:

FWIW, adding an <> operator for xid seems like a perfectly good
idea.

+1. I have wanted that more than once, but avoided it all the time
with some casts.

+1 (two of them actually --

See for example the attached

I just reviewed the patch, tests passed and feature works as intended.
I change the status to ready for committer.

(do we care about commutativity with int = xid and int <> xid?).

(another one for <>(xid,int) which mirrors the =(xid,int) we
already have).

To which one are you referring here?

- --
Julien Rouhaud
http://dalibo.com - http://dalibo.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.17 (GNU/Linux)

iQEcBAEBAgAGBQJWPeWGAAoJELGaJ8vfEpOqxtQIAIH5v1aNlcH/vvPgdZ9YTx3q
ZzyUfjtzn4mtqHNaTy9Nmob3N0EzeP6U+veVG5gkFJhBT/Dj0xSJn9QNMYTcXimX
RYpNJFlp0MF8b3xC10Oyz5fWcMmeBJYi7/CzHWUa3GLyT27qeDqJmv+npVgadPUC
WKMdBNQP4X5tdN3SfPlmIIfrAmkZFpJukcmor01rATBZckPV/7jHvNK34MQcpvgP
0LwpsomPbe1FKTMVafatRhDYczMptqSxpaCbay9+E6LqANiEmV82aANnnqFO7UcV
XdVSQSC0e5YXjcOj+4NRDsaY28CfqtUCzyXd76X1CRnhgEbLsoPSHyg9RBnennE=
=6JDx
-----END PGP SIGNATURE-----

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Julien Rouhaud <julien.rouhaud@dalibo.com> writes:

On 23/10/2015 14:59, Michael Paquier wrote:

Andres Freund wrote:

FWIW, adding an <> operator for xid seems like a perfectly good
idea.

See for example the attached

I just reviewed the patch, tests passed and feature works as intended.
I change the status to ready for committer.

Pushed.

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