From: Japin Li <japinli@hotmail.com>
To: wenhui qiu <qiuwenhuifx@gmail.com>
Cc: Shinya Kato <shinya11.kato@gmail.com>,  Sami Imseih
 <samimseih@gmail.com>,  PostgreSQL Hackers
 <pgsql-hackers@lists.postgresql.org>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
In-Reply-To: 
 <CAGjGUAK6j4K3OMocdtPZRwTyF_U7Y47-bzxUDu4ZeUdg35Vtnw@mail.gmail.com>
	(wenhui qiu's message of "Mon, 16 Mar 2026 15:59:36 +0800")
References: 
 <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
	<CAHGQGwFVdccfhN7PFHU5gZcMNdQuyL8DhZSks2FuuTcZ5V_pGQ@mail.gmail.com>
	<CAGjGUAL-qZ6Na-cH4RY5raF4NwuF47ZVNyP0uMYQcks3w5b7Dw@mail.gmail.com>
	<CAA5RZ0s+UUXekbeGcC-H71kW=MfeaUCOV=yEWX94NXViO2-=pA@mail.gmail.com>
	<CAOzEurQVUsRa45aKdi1Qrc4wVsQKSmbLcN+2A9Eu1W1jm2Y5fw@mail.gmail.com>
	<CAA5RZ0sjMgMo4Xg-niyyF-CpkQ_CK6uOfNKYT=9RmiBkAxQkbQ@mail.gmail.com>
	<CAGjGUAJ8JVL9J32ChchmVLgZycEyXf-r-KHc5EiRM3LDiEoPtA@mail.gmail.com>
	<CAOzEurTNVkvvscKeEOy0WwfzyqO+J_MyXwkjRteJ_zyydteKCQ@mail.gmail.com>
	<CAGjGUAK6j4K3OMocdtPZRwTyF_U7Y47-bzxUDu4ZeUdg35Vtnw@mail.gmail.com>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Mon, 16 Mar 2026 19:19:34 +0800
Message-ID: 
 <SY7PR01MB109210F944A86A8771BC5EE2AB640A@SY7PR01MB10921.ausprd01.prod.outlook.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SY7PR01MB10921:EE_|MEYPR01MB6486:EE_
X-MS-Office365-Filtering-Correlation-Id: ab61939f-fb47-4e43-0877-08de834dea1d
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|39105399006|461199028|22091999003|24121999003|20031999003|24071999003|55001999003|6090799003|5072599009|15080799012|12121999013|19110799012|23021999003|8060799015|1602099012|40105399003|4302099013|3412199025|440099028|10035399007|18061999006|26131999003;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?SmlDblJoQUJRaHZZOW1uOWFMdU02eEVSbEpDWlR6bWxoaXNGSXJWV0xVUEJP?=
 =?utf-8?B?RGlXTzRIc1p0RWdNeXMva1dNak85c0ZPb2w3bVNqREhpWTJMVG8xdEFOWE1R?=
 =?utf-8?B?MzV1cDdzSFMvbWp4MnE2TExWeHVML1BDVDBvUk16OW55SWNnb3RucE5qS1ZZ?=
 =?utf-8?B?Sk9lQXplNi90OWhGZ3NIZFVYOFRCM2YyMzVNV1diVGlmM0ZPZFpwZzU4RGFh?=
 =?utf-8?B?SlhaMVcxU2gveFJuZy9KWTc3ZXZ1bEdITnpDM0NQZFNLVUFFdmg5eUQ4UVpL?=
 =?utf-8?B?VFNGUWZheEZrZGY1KzBudWZ3ZkxYSnorRysvRklEeVhFb0cyMVBYdHM4aTF5?=
 =?utf-8?B?ZjlXaVRzVjRhY0twK3RzQUxRa2NDbTQ2RFRSbE9lbjVRN1gycjIxZitacGVG?=
 =?utf-8?B?TGZmWEJlUmVjSFY3ZWpmbmVFRzFLMG1aVUtMU1VSSGR4WGhiakRmOG1NVTd6?=
 =?utf-8?B?a1B3QlVyRGlEeFZWZVBZTzh3aWl4cUorZFI1UFZZS1V0OGJYU2Vwa2RUcFI2?=
 =?utf-8?B?dXBhS2FOOEFHWVFWMzN3Qlk0VmNMV3BPbDNHMFpDRVR4V1FZK2NhWnVQc1p0?=
 =?utf-8?B?Q2lOcHlWV01sY21TZ0tFYW9zRVMzUFRNWEhraHFWcVBXUzZGNVRaZzlVRGpD?=
 =?utf-8?B?VlpkMW5lV0tlMnhQdk5rTXhqZGhvbW1aU0h2NzB3ZndHeEM4RWNEVUtadTdK?=
 =?utf-8?B?SDF0R3VCL1hVQ1hnVmgrVDdmbnkzc29uQnZaai85MFVrOWUzMGZkSHZ4TmdJ?=
 =?utf-8?B?QllQcTluYWxnOGZyd2QxK2xtK09jci9sNE5JY1A1Y2NCZHZ5c0sxRHg4R2Mw?=
 =?utf-8?B?RVp0V2xYSUpTSG14bTE1T29MdjdBUFdqSTRHZGY2bDd4ZFFQRCs0d3FWZFRP?=
 =?utf-8?B?SkYvOGdHZ3hiclRtVkNUZVdONDFweWFoTzhneEp4M0tGLzFpem45aGx0aGtQ?=
 =?utf-8?B?TG1aODd3VWNhNnU1dDl6Y0l3OExaRWNwZkRWVElGVE5lVUJXai9Uc3JrUFZO?=
 =?utf-8?B?MWF0WW1xM3F0b1dVNTRGa3V4Y0NCdzFaZFllQWNadWxnQ3hCMlpETlYvMzZS?=
 =?utf-8?B?b2Q5OVJhdlU1ek4rZ3cxaVE4SzVrNEpBMGpCS1ZKWGF1UTUxa2ErYTNjbUxW?=
 =?utf-8?B?M1VOQUNML1htNnd0WUtoeXRQVWEyT3QvZExvQSswOUlXZjlzemI1T3ZFTm5k?=
 =?utf-8?B?bUJNY3h2eVg2ckE0N2k0TmIvNnFkV0V5ZHY2eFRuT3E5cGhWYWVaZ0hjR0Q4?=
 =?utf-8?B?MjJ2bTEyano4OUdVMU1zMUd0QkxVL0pwOEFzeDRORXBHN0MrRndKa2JTdElI?=
 =?utf-8?B?OC9kY0VQRzlSbTRad3BSczFWWEtreklXTWkweFZINkdGNExYRVRHVDFXRWtE?=
 =?utf-8?B?eE5tOUdmcmUvdGREVU1tTlZQcFk0WkdVeVBGSkswdUhGcXJwUkw3SGQzVGhL?=
 =?utf-8?B?QlpIejB4YnBGWjRyY1Yva0YxaWxiMmhTejRld01DQTdmRHc3aGJCK0RSTUY1?=
 =?utf-8?B?L1NaZk5hdVVJVGxiUk9CNWhoc1BYb2xLYnZFZUJUb1FEWTNoNndXaVIrRzBY?=
 =?utf-8?B?bHdLVE5JcjVkdUdCU1BQdDA4VlhDTFJtdmg5WGJnMHRqNmU0cjk0Vlc1UzdS?=
 =?utf-8?B?a0Y1R3U0VTlZV3Y2cG52cXhNa01VaWFGVnd4V0F3d2wwSlZNMHpXVUxTUlow?=
 =?utf-8?B?SEV5R1FqazlpVWFmckd4OEJMc3NadEdrTXBYOXFLd1diekxZTk54em93PT0=?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?REZja2ZGNXQycGJOUUN5Q0hWT2VudjZ5WlNtQStmT0ZraDFqYm4wMHkyTUVM?=
 =?utf-8?B?N1ZtZnd3QTZGRHE1eXk0L1EwMUlPOGgrREltNlpyd21UTUh1aCs5M1Y2MWhl?=
 =?utf-8?B?YW1tTzhOVUNUZHlCVGQyTFQvZzJmUGVGK3RxK0p4R01QVUNmQXQwd0xndndD?=
 =?utf-8?B?dlhwaXljOXlkdHBFd1EyR2IyTGZQZ0paM0FVVFNzR1huMnU1UEdnaDhyQWIz?=
 =?utf-8?B?bmRhY1NXN2pQWU5LQ3FYYlRwNHA1MGFTclFRK1FSU25QOEhXRDBiamljai9B?=
 =?utf-8?B?K204eTdXYWNlY21SWDRjdXkxVHd0UFFCYXZwZUdTR2NmOWQvblZXdTRWbzhZ?=
 =?utf-8?B?TkJOdU5JaDlia3AwR2ZVQ0h2bXM5MmhpUUlwekhkK1doWStGaGdUa2JNWW43?=
 =?utf-8?B?cXFUamxCNG9MejVxSlhud2NwSnhTeEV2N3BwcUNwSjR0cTZqMTZXd25WWCt2?=
 =?utf-8?B?YUtQeDF1M2RHbWlDeUNrLzlENXNOMDZuc1MyQ2V5VGx6N2tXNzZIdXBnMmdl?=
 =?utf-8?B?aFAwT1JLd0F5Y29JdUxWdUFXclorSnd5cFBDYVF6VDdBSEYyazhrcWF4Vzkw?=
 =?utf-8?B?ZjhzeFE1ZWNCVHYzRmRMQnJXRW1Sd0RENkZoUkVrblRrSkcyZEFGNmxRWkZK?=
 =?utf-8?B?ODRZNGQwUkF0UGpjNW5nek0xaEhqa1ZuZXl2R0x4UnlEbkxzNnFkVkV2Ym9Q?=
 =?utf-8?B?UHNDZGZrTXVRR0RLaHAyK0lvOXNKb2NKUW9mdnZCNUsvS3ozTnRiNmZzVkdT?=
 =?utf-8?B?TkFOQTdQUXovc2VnZk12K3JsZHp2VGxxTnNMRnBVbWlQUFVONG9KMUpUV2ta?=
 =?utf-8?B?cDQyRW5YT1BlUW9LRlJteG1ndGRvWEJWWWEvc1BjcjBGcVBZTDU1Vk81RXla?=
 =?utf-8?B?VDJiZVJOM3ZCaDRTTjdnRElDN2ZYTmhSUEJYUkpOYkFrVDZXWjJtWHJvQ2tV?=
 =?utf-8?B?VUFMNGFUL1Vydy9ONjVEUFBCTHZmd3lTZTR3YVo2WHRmQkNGNjFKV0paUnBl?=
 =?utf-8?B?d0FEb2VMNmdrL1pOWWtIMzB5M003ZXVsSkYwMXJqL1VGRTJmUFBoKzYwaGg1?=
 =?utf-8?B?S0FDVVR6ZUJIb3hodFFnTkU2SUFDOFRFQkpzY2t3VEZReTQ4bkEwVGRnS0Nn?=
 =?utf-8?B?ODNKdGt3K0h6T3F0L1lVMTh3T1dNSnJ1NGx5cHo3VDBMaFpmVlJndEtHRjRE?=
 =?utf-8?B?TTViaVJyMFU5RWFWeUYwUFpiaDRDclk2RDJQVDdwK0ZlOElkVk1JdmpQNmdj?=
 =?utf-8?B?WmlLSjV0TldrWlNwN0c0QURZQnJTc1RMVFoxdW5iVnZ5YjJRcHZvZ29wbXJk?=
 =?utf-8?B?Y0M5WUtkMTUyUWlMU2RoL2h5VVY3WlNUTXVwS1JBNUlyd25BZ3hwQjVzWk9V?=
 =?utf-8?B?WHZhQllBdG9rMEdoVld1MXNDY0lmU0dCN2h3N3RZb3NtUmt3dC91Y3NPclhv?=
 =?utf-8?B?dGpRcmR0RndaTjZzd1ppS0l0L2hSY2pnVTJzQmtIc0NRQUxvTlBzNlYzYlVU?=
 =?utf-8?B?ZnRwS0JlMHR4Nm83SlBnTzI3L2Fad3M2cFkySGdSenErdnFyMXJLS1p1U2RD?=
 =?utf-8?B?UDA2dlpOU1RZZlRkKy9kUzYrVFlGWGZhR21jUTRoaDRhTGxQbUNYRkVLTUMr?=
 =?utf-8?B?MTFzVU56Y29vZElxd0o0dTFOSzhXbWJwb0pmWDBzdUdxdXJ1RGFiNzRtUEYy?=
 =?utf-8?B?VkhDVWtwRmlDeHFXT3ZxUkpObFpRenpuSkZWdlduZnVqYzZ4TzZjcVpXVHh5?=
 =?utf-8?B?OFdaMjhneTZkT3hvUDBWUkx5ODFkckQ5VnN6V2ZLeVZlMGRkT20wbWtFSC85?=
 =?utf-8?B?T0htWnoyK21xVWhXNG5Wa1JaNXh1SURSeW9zM3RsclQ0aUhHWGZJdGE1cnk5?=
 =?utf-8?Q?/IxiAWsMiAGMc?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 ab61939f-fb47-4e43-0877-08de834dea1d
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 16 Mar 2026 11:19:39.7843
 (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: MEYPR01MB6486
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/SY7PR01MB109210F944A86A8771BC5EE2AB640A%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk

On Mon, 16 Mar 2026 at 15:59, wenhui qiu <qiuwenhuifx@gmail.com> wrote:
> HI Shinya
>> typedef enum XidHorizonBlockerType
>> {
>>     XHB_NONE =3D 0,
>>     XHB_ACTIVE_TRANSACTION,
>>     XHB_IDLE_IN_TRANSACTION,
>>     XHB_PREPARED_TRANSACTION,
>>     XHB_XMIN_ACTIVE_TRANSACTION,
>>     XHB_XMIN_IDLE_IN_TRANSACTION,
>>     XHB_HOT_STANDBY_FEEDBACK,
>>     XHB_REPLICATION_SLOT,
>> }
> Thank you for your working on this ,I have another small suggestion=20
> The priority ordering encoded in XidHorizonBlockerType determines which b=
locker gets reported when multiple candidates
> exist. In particular:
>
> ACTIVE_TRANSACTION
> IDLE_IN_TRANSACTION
> PREPARED_TRANSACTION
>
> Prepared transactions are currently ranked after idle-in-transaction sess=
ions. Operationally, prepared transactions are
> often harder for DBAs to resolve than idle sessions, so it might be worth=
 clarifying the rationale behind this ordering
> or reconsidering whether prepared transactions should have higher priorit=
y.

Agreed.  Explaining the reason for this priority is very helpful.
>> typedef enum XidHorizonBlockerType
>> {
>>     XHB_NONE =3D 0,
>>     XHB_ACTIVE_TRANSACTION,
>>     XHB_PREPARED_TRANSACTION,
>>     XHB_IDLE_IN_TRANSACTION,
>>     XHB_XMIN_ACTIVE_TRANSACTION,
>>     XHB_XMIN_IDLE_IN_TRANSACTION,
>>     XHB_HOT_STANDBY_FEEDBACK,
>>     XHB_REPLICATION_SLOT,
>> }
> Another one:
> Currently GetXidHorizonBlocker() selects only one blocker (based on the e=
num priority) even though multiple independent
> sources could hold back the xmin horizon simultaneously. For example, it =
is possible to have both a prepared transaction
> and a replication slot preventing the horizon from advancing.
> Have you considered reporting all detected blockers instead of just the h=
ighest-priority one? Returning only a single
> entry might hide other relevant blockers from the user.
>

I'm also curious =E2=80=94 why don't we list all the blockers? Did I miss a=
nything?

> Thanks
>
> On Thu, Feb 5, 2026 at 12:40=E2=80=AFPM Shinya Kato <shinya11.kato@gmail.=
com> wrote:
>
>  HI,
>
>  Sorry for the late reply. I've updated the patch to follow Sami's
>  recommended approach.
>
>  Overview:
>  - Instead of modifying ComputeXidHorizons(), this patch introduces two
>  new functions: GetXidHorizonBlockers() and GetXidHorizonBlocker().
>  - GetXidHorizonBlockers() retrieves all potential blockers. This API
>  design leaves open the possibility of exposing this information
>  through a dynamic statistics view in the future [0].
>  - GetXidHorizonBlocker() selects the highest-priority blocker from the
>  candidates returned by GetXidHorizonBlockers().
>  - Priority is defined in the XidHorizonBlockerType enum. By
>  distinguishing whether the blocker matches the horizon via xid or
>  xmin, the appropriate blocker is selected.
>
>  Changes addressed from review comments:
>  - Fixed unstable regression test (Fujii-san's and Andres's comments).
>  - When multiple blockers share the same horizon, the blocker with the
>  highest priority is now selected for output (Fujii-san's comment).
>  - Removed unnecessary code (Fujii-san's comment).
>  - Distinguished between active transactions and idle-in-transaction
>  sessions, and added tests for both (Sami's and Wenhui's comments).
>  - Added a trailing comma to the last value of the enum (Sami's comment).
>  - Added a new function GetXidHorizonBlockers(), modeled after
>  BackendXidGetPid(), instead of modifying ComputeXidHorizons() (Sami's
>  comment).
>  - Added a test for a SERIALIZABLE transaction (Sami's comment).
>
>  Not addressed:
>  - Did not switch from int to pid_t for the pid type, because int is
>  used consistently throughout the PostgreSQL codebase for this purpose
>  (Sami's comment).
>
>  Other changes:
>  - Changed the TAP test to use VACUUM (VERBOSE) instead of autovacuum.
>
>  [0] https://www.postgresql.org/message-id/CAAaqYe9Dy9sicKg3xzCQUMK3VLdEP=
39g9nMGZheqtFYfNiO5Bg%40mail.gmail.com
>
>  --
>  Best regards,
>  Shinya Kato
>  NTT OSS Center

--=20
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.