Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w0yth-002Nty-12 for pgsql-general@arkaria.postgresql.org; Fri, 13 Mar 2026 09:33:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w0ytf-002qLl-2e for pgsql-general@arkaria.postgresql.org; Fri, 13 Mar 2026 09:33:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w0ytf-002qLd-12 for pgsql-general@lists.postgresql.org; Fri, 13 Mar 2026 09:33:31 +0000 Received: from mail-westus2azolkn19010034.outbound.protection.outlook.com ([52.103.10.34] helo=CO1PR03CU002.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1w0ytd-00000001urG-3Ne3 for pgsql-general@lists.postgresql.org; Fri, 13 Mar 2026 09:33:30 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=obK6OwnI0EtbVItqxDxRW9zgQtmpcOcE08IATOgCX3wQkb2w2432IEAk7NMCZEX9nKz5K8zzCiOIuthFO5EHtWDL7sIZScKvEA2RIbRU0JLxcELiO0qykW07SapNX/EIruXx7TQ1IK/FkSC9fTlSXbdEDTWoBumKSUHaHjAqyTVh2KkyFHshbXw4jHtI9/hu6ggFMNinyHzezBtCO7R9aJBF/+bF+BUynEv6eZ5w/Mlsgswcszv5oBCVstE+YF2sfkH6uqtCj8n/IZHn7gVZOQ3l/uh4bcQ+BYUCGDTRHHvDPA+CgnTCh7nVklx6PxGKD2Tb80sybgFwL4DguhVTQg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=7ZApqEK/BgwDclYXmH3/k8enEy4UKEKchMKeXMotbjc=; b=XfuenjMPt3yBQcHfxwMmuceOSy/vfxBn4VkZJH9jFPmeUequjNlOPeFGGPIH3emWx1s5/c63Z+zezAMd8/yhpgNtu4xVLQHh0x39ivZkiyt0ufzwg4DlYYoXcXX+r8nM2rv20SIyBHFcPmRIRedztSxddhGVT7wGYZSuplnAUQbUGwdH57k4N2NVPrXggIL0ubGnhJJPGDon6ITseZQn++wTUNND60EYGty/NcZo3rIiAudgGfUPgOZF+MevrgYM8p93EKmNqp66fucdadVA82C7j+rV7rWuCFquwI0felPehccEO2omIbLCCpjafDFNgD1PvbDeMyfaFw6bK20bTw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=live.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=7ZApqEK/BgwDclYXmH3/k8enEy4UKEKchMKeXMotbjc=; b=Oy4xqweuTOF6PwtUXC8RacFfMg9E6T86/UeBLvWB1C8WQ8+KdOOM+wc9GtryLkk8R9+BdM1JAWcyMeszzin8DqigBx+4pGeva3Zd0cz+9BDp30PAEGj4GrOdoENsH6fog4YDoNCzbflBoPXIPm5wCREKeFvtMCGCPULmK9+mGVQgIskumYBZCG6o/xtc0mjMf4sFXAaiEyxZYZZQrsWlLm1iAH+pMpcNKxQ1mn+IqFf790FH1KDHwtSE3yjRumRyKUFwr/0rY7RtiPS1cJSppKaQe6fjsGniB3pjY4GSWZ3K5yiB9NyryAOv1QXZvZayCCEbl6G4kRQkhB3lreQ/MQ== Received: from LV8PR84MB3786.NAMPRD84.PROD.OUTLOOK.COM (2603:10b6:408:1cb::13) by LV3PR84MB3699.NAMPRD84.PROD.OUTLOOK.COM (2603:10b6:408:21a::6) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9700.16; Fri, 13 Mar 2026 09:33:26 +0000 Received: from LV8PR84MB3786.NAMPRD84.PROD.OUTLOOK.COM ([fe80::509f:6883:6d0a:6c6]) by LV8PR84MB3786.NAMPRD84.PROD.OUTLOOK.COM ([fe80::509f:6883:6d0a:6c6%4]) with mapi id 15.20.9700.015; Fri, 13 Mar 2026 09:33:26 +0000 From: Ishan joshi To: "pgsql-general@lists.postgresql.org" Subject: correct formula for calculating distance t transaction and distance to snapshot Thread-Topic: correct formula for calculating distance t transaction and distance to snapshot Thread-Index: AQHcsso9GOJ2Gfj99UyoGKuVm9Ko6w== Date: Fri, 13 Mar 2026 09:33:26 +0000 Message-ID: Accept-Language: en-IN, en-US Content-Language: en-IN X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: LV8PR84MB3786:EE_|LV3PR84MB3699:EE_ x-ms-office365-filtering-correlation-id: aaa574c5-036b-48c6-7c1d-08de80e39428 x-microsoft-antispam: BCL:0;ARA:14566002|31061999003|11091999009|8060799015|12121999013|8062599012|19110799012|15080799012|15030799006|461199028|24071999003|25031999004|39105399006|3412199025|440099028|102099032|19061999003|40105399003; x-microsoft-antispam-message-info: =?iso-8859-1?Q?ux30e21xfoLaG8lywpob/EhfCu/cGDVWwwsjQihIZUbI9hAaLoA+i+DEV8?= =?iso-8859-1?Q?/x2zBU0Ll9oVI0dcbspvb1HYk3OOf5JrmQ0S02brz1Yf72YhLnpC6e9gWk?= =?iso-8859-1?Q?9ZOAzOGC0L4OXp0iOuN6AScqRoJ4PPK4zHftEqoTHB5F0rMLRDgeWxxWcJ?= =?iso-8859-1?Q?T+829FQTqzQkbV8w4gC6xqVqRhu3mQh5T5nhJlHuQHul/5KAf2EMXnLrwf?= =?iso-8859-1?Q?7boyn6P2a25+IHI5LbJt5bZ0x3nWLQ321ZrYn/kEdB6tFxfLyeQuJrrPAH?= =?iso-8859-1?Q?89O4T3QfgL4KFVpo92M2hmEtevZb+GYUak6KN5+5akdYPZBXYlEGEnrKB4?= =?iso-8859-1?Q?slYg+nEvOO0rxkv3ZDOVQEL4J553acF8MhXbbQFZ1vS3ozrueUOTrA0Yhn?= =?iso-8859-1?Q?2kPsVXTPL8+NaISCR3mEoUZgfVnKfnuhCeWN6/mO13+UaCxA5fhb2LNJFS?= =?iso-8859-1?Q?653J53pODDoBDjui8HJSH0hSAgB53DLHfGrHa2cvfXZ4mNEV/vBAg8CYUr?= =?iso-8859-1?Q?31Km9FaRqUk/sju2PcSsuOgRf8FrxNiOD7M2m3PR1B3oFBtHh84a4dFMDT?= =?iso-8859-1?Q?erUdRjE4mfr1IvCS9CTj9C5yiFGpoiTZAFy48GKaPUIFwZfL6P1zDpCmsm?= =?iso-8859-1?Q?3AE8CQdvmOvLA5mY+i0+jsvxKL6Pb9Kvhoh+lC9M6E/6jujXwVcuVBHxKD?= =?iso-8859-1?Q?wnlmXYR3lPLzYnGsQ4o7wxrBns49rJ/Tm1RvwnZ9vKsf5P+SLIOpBDqFl/?= =?iso-8859-1?Q?AQk4r2z+Ux832hV1A72t2FHlsEA/JOcJHA8dzy+TWjUfZ6FabIW87NtnEh?= =?iso-8859-1?Q?Q2xO72NotgePOd99W7di5o1NmXOZ7KCpAF57ja5GLMCtW64YOEn0UCKzdi?= =?iso-8859-1?Q?PyQYxx6uD7ihpVFmnyDH1DGKK4Umg5oZUso3dt+WyG/cFIbbmlwMFsdNdE?= =?iso-8859-1?Q?42DeiKyPeWcvbmAMCUjXX8T1goLLfIzfW09mOPS4PIL68S2JEZ10n4IUGF?= =?iso-8859-1?Q?JHv3iHPw6WrZrgdpkkZVon75ZZdSow7QvK4+jgYuY+vCDgs03HPhuck00C?= =?iso-8859-1?Q?MWez5dNq44fbu3OyDs5tm6LCt+bzWJJgUpPswuvZKz2l9xXoLyTXi8b4QY?= =?iso-8859-1?Q?lKXnzGXI7VAPRoHzPmP6Qg/T587Lg=3D?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?XXY7197CrmvguV6baYSZfLAYXt+VS1KIhinpUP349bHSXkubo1NUtM2jUt?= =?iso-8859-1?Q?njzVpHswVO40X3W190XFn4lgZBB/G0iIwFVTWWM92rrbvr4vRzhgjDYHpa?= =?iso-8859-1?Q?BfiDz0MeZAv3XeoTUZLuvleSCsZ1yOKIO11ukDQIt/4E3TQYz/UcT6gNzT?= =?iso-8859-1?Q?pIxvmv/H+fejw29/BPumuVvHAr4jYhbDa+DxEV+qLDHci5+i8jtCCJ7Ss5?= =?iso-8859-1?Q?QlyoEkIwWRLr7IOUbHrVtu5kVo/FatAeiqH4agQZ472C1FH6wG+Aw0L8rf?= =?iso-8859-1?Q?qqOYVOmvPglBbvl1QESD09jJ9rQlnLUYzZjLYeGkRWXjcqZ50sMQAxb2ve?= =?iso-8859-1?Q?a56KTFXztvWxQZ1+zl4Ha6BQra+vVKfTKyOPoxFx+IVcFv0kN6bLOiDA3D?= =?iso-8859-1?Q?M/cj5spYqAbLUgwIPfSS6EgZYHiL0cVaoGrOqMWHjeajl4GEh1Fx46GtQX?= =?iso-8859-1?Q?Ml6UO3kZonABHKDmclJ1gyPKnHotJeVNidoBg8sw4MaK3xAhowII8dM8Rr?= =?iso-8859-1?Q?xEkydvAob155CW4GGftxYzYP81ZO4K2E9WFsj1BggM6SuxnOr6+MyfYIAV?= =?iso-8859-1?Q?1FpLolw394ilux+IFfLaavzPkKBj2QT7Rsg0c5GgZuJVJEKNuS4whTW6y+?= =?iso-8859-1?Q?mijOt/VWXm3R8o27ooBROOuONOc4v8zKByMVD9hj1nCJ+d2Qm8wH2GjYnD?= =?iso-8859-1?Q?phm7UXOsllTJUir+GOPAIrTqh4gVaWuGn1dP7i3Lm06BCIFFpGsMjRxosp?= =?iso-8859-1?Q?jtvyC4yU11PAr0io87VvPdfqf5rZm6IKRaw2z8ZB7tunqI3xfMxVjEA4Vl?= =?iso-8859-1?Q?cwN4pRLjhvY7zre+FJQjDWELvD7yv1EEQEUoPjEmngp8XrgswcFlufo/3D?= =?iso-8859-1?Q?zGw89RCuWkLmLcC2A0gC87NG8SfBO2JfjZRy0l2BIqsDTVk4v4uet1MZlU?= =?iso-8859-1?Q?f57HMl5g4hK4Pqm9PTJOdGIrT5L/FRegEcDfbbT+Z/Wv0aHGWx8IHsbYoC?= =?iso-8859-1?Q?IKjvv+PlmIrHWCahwJVcsAbS+oh+yOm6+N350SzTZlLZgRQpK+JIJ/HQvZ?= =?iso-8859-1?Q?wgtbtodHsQixFV1/EFUokYReyiRDdifzsGD77T/HxarkIJrBmv5ricpYyH?= =?iso-8859-1?Q?tl2LA6KDqroCPFdEShFYkqUK/y6wt/wMrR+TrfOOS+v4EXCjHnHyLX2X/Z?= =?iso-8859-1?Q?wp34ZZoYcC5tVDwNGNDmQvsrAAhyeUDcEMkDvFC58PWrogA/RTMO1MrLe5?= =?iso-8859-1?Q?lTQngVIEhfSBMebGUI7v2TfJMTynvVmCrI8LbmanOw6XuIazjrDkBhibIr?= =?iso-8859-1?Q?wBnua32KCh+0eP+wsjnAvjh2APz0/tMDkrmm7Rra5r8EzH5QoGFInAO/IW?= =?iso-8859-1?Q?a0MOGU91VMpCIrIBEFDlr0EUEdUsm1PVzxA8l56KWg4GxTYKSdlVc=3D?= Content-Type: multipart/alternative; boundary="_000_LV8PR84MB3786A92D42B91C74D2922BBEA945ALV8PR84MB3786NAMP_" MIME-Version: 1.0 X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-4a72f.templateTenant X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: LV8PR84MB3786.NAMPRD84.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: aaa574c5-036b-48c6-7c1d-08de80e39428 X-MS-Exchange-CrossTenant-originalarrivaltime: 13 Mar 2026 09:33:26.4654 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: LV3PR84MB3699 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_LV8PR84MB3786A92D42B91C74D2922BBEA945ALV8PR84MB3786NAMP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Team, I am trying to understand the details for calculating the "distance to earl= iest transaction" and "distance to earlier snapshot". I can see many post t= hat 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 wo= uld be very low compare to current transaction id. Also as per my understan= ding "age" function does not have override function that calculate xid diff= erence [age(xid,xid) not exists]. age( pg_current_xact_id()::xid, (SELECT min(backend_xmin::text::bigint)::t= ext::xid FROM pg_stat_activity WHERE backend_xmin IS NOT NULL) So if we performing type cast for current transaction id to number or chang= ing backend_xmin to number and calculate the age, This will also not correc= t way. (pg_current_xact_id()::text::bigint) - (backend_xmin::text::bigint) So, is there any method to calculate the distance to transactionid and dist= ance to snapshot. In my case it is showing very big number (8597422911) but while checking mo= re details, I dont have any long running transaction/session. Number of dea= d tuples are less, running vacuum (analyze,freeze) everyday hence the wrap= around situation age % is very less (4.1% that is away from wraparound sit= uation about 95.9%) , min(backend_xmin) is giving low (123707539). So, is there any way to calculate the correct value? Thanks & Regards, Ishan Joshi --_000_LV8PR84MB3786A92D42B91C74D2922BBEA945ALV8PR84MB3786NAMP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
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 transac= tionid, min(backend_xmin)) but It is not the same as current transaction id increase with every operation an it will be high an= d if we use old backend_xmin values that would be very low compare to curre= nt transaction id. Also as per my understanding "age" function do= es not have override function that calculate xid difference [age(xid,xid) not exists].  

 age( pg_current_xact_id()::xid, (SELECT min(backend_xmin::text::bigin= t)::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 chang= ing backend_xmin to number and calculate the age, This will also not correc= t way.

(pg_current_xact_id()::text::bigint) - (backend_xmin::text::bigint)

So, is there any method to calculate the distance to transactionid and dist= ance to snapshot.

In my case it is showing very big number (8597422911) but while checking mo= re details, I dont have any long running transaction/session. Number of dea= d 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%)  &nbs= p;, min(backend_xmin) is giving low (123707539). 

So, is there any way to calculate the correct value?

Thanks & Regards,
Ishan Joshi
--_000_LV8PR84MB3786A92D42B91C74D2922BBEA945ALV8PR84MB3786NAMP_--