Hi Team,
I am trying to understand the details for calculating the "distance to earliest transaction" and "distance to earlier snapshot". I can see many post that provide the details to perform age (current transactionid, min(backend_xmin)) but It is not the same as current transaction id increase with every operation an it will be high and if we use old backend_xmin values that would be very low compare to current transaction id. Also as per my understanding "age" function does not have override function that calculate xid difference [age(xid,xid) not exists].
age( pg_current_xact_id()::xid, (SELECT min(backend_xmin::text::bigint)::text::xid FROM pg_stat_activity WHERE backend_xmin IS NOT NULL)
So if we performing type cast for current transaction id to number or changing backend_xmin to number and calculate the age, This will also not correct way.
(pg_current_xact_id()::text::bigint) - (backend_xmin::text::bigint)
So, is there any method to calculate the distance to transactionid and distance to snapshot.
In my case it is showing very big number (8597422911) but while checking more details, I dont have any long running transaction/session. Number of dead tuples are less, running vacuum (analyze,freeze) everyday hence the wraparound situation age % is very less (4.1% that is away from wraparound situation about 95.9%) , min(backend_xmin) is giving low (123707539).
So, is there any way to calculate the correct value?
Thanks & Regards,
Ishan Joshi
