Postgresql – Negative amount of remaining transactions before wraparound

postgresqlpostgresql-11

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?

Best Answer

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.

Related Question