Thread: Change behavior of (m)xid_age
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. -- 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
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
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
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
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
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
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
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
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
Attachment
-----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-----
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