Negative amount of remaining transactions before wraparound

Question

Running a PostgreSQL 11 instance with a table of more than two billion rows.

I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens:

Query source:

SELECT
   oid::regclass::text AS table,
   age(relfrozenxid) AS xid_age, 
   mxid_age(relminmxid) AS mxid_age, 
   least( 
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid), 
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)  
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;

Output:

-[ RECORD 1 ]---------------+------------------------------
table                       | tablename 
xid_age                     | 2146483650
mxid_age                    | 0
tx_before_wraparound_vacuum | -1946483650
size                        | 1583 GB
last_autovacuum             | 2019-08-18 05:03:56.64232+00

Why does it return a negative number for tx_before_wraparound_vacuum?

Does this mean that there have been 1946483650 transactions more than allowed since the last wraparound protection was run?

Answer 1

That just means that your query is bogus.

age(xid) compares the age to the current transaction ID, so it is useless to call it with a multixact ID.

Moreover, running that query will return nonsense for anything except for regular tables (relkind = 'r').

You could try something like

SELECT cc.next_multixact_id::text::bigint - t.relminmxid::text::bigint
FROM pg_class AS t
   CROSS JOIN pg_control_checkpoint() AS cc
WHERE t.relkind = 'r';

to see how old the oldest multixact is.