public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ishan joshi <[email protected]>
To: [email protected] <[email protected]>
Subject: correct formula for calculating distance t transaction and distance to snapshot
Date: Fri, 13 Mar 2026 09:33:26 +0000
Message-ID: <LV8PR84MB3786A92D42B91C74D2922BBEA945A@LV8PR84MB3786.NAMPRD84.PROD.OUTLOOK.COM> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: correct formula for calculating distance t transaction and distance to snapshot
  In-Reply-To: <LV8PR84MB3786A92D42B91C74D2922BBEA945A@LV8PR84MB3786.NAMPRD84.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox