Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How is age(datfrozenxid) calculated in PostgreSQL?

The postgres documentation says:

"The age column measures the number of transactions from the cutoff XID to the current transaction's XID."

like image 530
Piyush Patel Avatar asked Feb 20 '19 00:02

Piyush Patel


People also ask

What is age function in PostgreSQL?

In PostgreSQL the age() function is used to calculate ages. Syntax: age(timestamp, timestamp); Let's analyze the above syntax: The age() function accepts two TIMESTAMP values. It subtracts the second argument from the first one and returns an interval as a result.

What is Datfrozenxid?

datfrozenxid xid. All transaction IDs before this one have been replaced with a permanent (“frozen”) transaction ID in this database. This is used to track whether the database needs to be vacuumed in order to prevent transaction ID wraparound or to allow pg_xact to be shrunk.

What is transaction ID wraparound in PostgreSQL?

Transaction ID Wraparound occurs when the VACUUM process cannot keep up with database activity and the PostgreSQL service is forced to shut down.


1 Answers

XIDs are just sequential numbers, so calculating the "age" of an XID is simple subtraction, i.e.:

age(datfrozenxid) = txid_current() - datfrozenxid

XIDs for data created during initdb, as well as for data frozen prior to Postgres 9.4, will always report an age of 2147483647.

The full source code of the age() function (all five lines of it) can be found here.

like image 146
Nick Barnes Avatar answered Sep 24 '22 16:09

Nick Barnes