From: Japin Li <japinli@hotmail.com>
To: Shinya Kato <shinya11.kato@gmail.com>
Cc: wenhui qiu <qiuwenhuifx@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: 
 <CAOzEurSa37w_wgP7SeVicsoo5ERXc4V7XQH2xee5YM-W9uRRBA@mail.gmail.com>
	(Shinya Kato's message of "Fri, 22 May 2026 22:05:31 +0900")
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>
	<SY7PR01MB109210F944A86A8771BC5EE2AB640A@SY7PR01MB10921.ausprd01.prod.outlook.com>
	<CAOzEurSa37w_wgP7SeVicsoo5ERXc4V7XQH2xee5YM-W9uRRBA@mail.gmail.com>
User-Agent: mu4e 1.14.1; emacs 30.2
Date: Sat, 23 May 2026 09:40:39 +0800
Message-ID: 
 <SY7PR01MB10921CD524617DCF4FE8E2565B60C2@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_|SY7PR01MB8432:EE_
X-MS-Office365-Filtering-Correlation-Id: 564c0e33-a3d1-4d02-73be-08deb86c4d57
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|8022599003|12121999013|23021999003|24121999003|6090799003|5072599009|22091999003|24021099003|15080799012|19110799012|8060799015|440099028|3412199025|18061999006|26121999007;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?NDVmSUtiRjNMcDNkMDZleXE0VXozSktlcjZlVTBsMXdKQmFJd3BtZkhvMmRJ?=
 =?utf-8?B?eThPODYxdUJ5WWxhdG02Zko2N1RXTlNuR0ZOcGVRNEZTZ091RDJVamJjRGU3?=
 =?utf-8?B?VTY3TGt2MjZqeW9vWHpGdHM0d0w2NzJvWjVLN1VpSms2aU1EOHN2V1BVaDY5?=
 =?utf-8?B?ZnZYQmJPR0x5VERwb25sdC9jc0F2ZUVVYWJTdDgxWFBFZHFhL1VsMmUwYXVl?=
 =?utf-8?B?dWdYZllrU3lQNmtrVGhYMGovZEllV0pVQWVkbzBJbllXNS9GZ2xyL2N2SWtq?=
 =?utf-8?B?ZnpGTlhBN2N3R3p4NTFxZTNkN3c2cUlwQUszNmFZTDFvUVdINzRvNkl5ejk0?=
 =?utf-8?B?MUNrTWtVK1J4TXlJRCtaaHZIdCswaFZramQ4QWV3V3ZrUklMVVJXN0Uvakxo?=
 =?utf-8?B?TWUxNUZJdTJJUmFiL0xhWFpqU2pla1VvbmczMW1GT3UvZ2ZCbVlRTFpLT0Vl?=
 =?utf-8?B?Q3Rpek5MdWJFZm1RU3JsS1p4T282NEVYWXhmQU1RSGFUMktuV1J4akJuelF1?=
 =?utf-8?B?VzBTQkd5MlNnOTBrTHFoY1ZNWmJoSTlLemdqL29FQlMvQVRJazAxOFFyYUNl?=
 =?utf-8?B?M0pXYUhYL3lpVmNzRUdrbk1zZmRhMW1EZ1dOQ3MvTnN1bW81NFNxMHFnN3Qx?=
 =?utf-8?B?eFduNjV3K2s3UmVtUmdRWXp4TkZvNmkwWjdteFJIVk1seFg5SWlCMzJXV0pt?=
 =?utf-8?B?NElUYnFDTEJJSE1zKzdRQTBCVHJRSHAxZzNySjVnWHFwYzBLbm1udGhkdGZr?=
 =?utf-8?B?aGRER3NxZDdVNmF3WTk5NUtlbnRRdnBEZ0NnWFhYLytiTWJRN01jR3UwR3pE?=
 =?utf-8?B?YlRCZUJHMUhOZGtuZzZLbnZobnBkNVkrNHdwNUgwTFpkNXFRUjR5akhha1dk?=
 =?utf-8?B?UE5sSGdZTzVFaVR6UEoyRXczV3JmYnpaVW1hVDVQV1BibEtFVUN3SWlWQXht?=
 =?utf-8?B?KzRjcVFySlBhdXp2R3BlbC9IY25QLy9EcU8rSUhxVEs5T3RWMjZlbUZ6MWtu?=
 =?utf-8?B?RUFmNEtQaWdzbmJzSTYxeHFRZjlDRksrVWNNenN1WHlZWVVXUDFaUVVOd3hy?=
 =?utf-8?B?T2wvUlR4VmxYaTNoM3UxdzRIazJ5YXB0NWhaTWd4N2ZmWXc4b1V2czdjWnNM?=
 =?utf-8?B?Mm5wVTNkcVk4bXYrc0NNbkhPZXVKd0pFNWhTRjk4NXAxU1JyL2FnSkZuaW16?=
 =?utf-8?B?L3ZLODE0aE04VnpQWXdZOXNwODNNUXRoclJibnZodHpZREtNRW1GR1Z4OGJJ?=
 =?utf-8?B?ck1SL0QvdUpTTUIzSjB4U0FGTFppaStKK3BzWEkyM0FSZ0lzREJUZDY4bHlz?=
 =?utf-8?B?aVFBdnBtZTZXR1lHN3ptaXowak5LQ1psZWVUMmZ6RzliaUdKTlRvZzZmQTRJ?=
 =?utf-8?B?eXFGSHNtL2VabzFSOTRGbjRITWY1RmZNT2p6OFo3bjFRMGd2OVlFQ1ViclJn?=
 =?utf-8?B?YURpY241a2hwSUpRMEQwZlcvTDhWakNySGRJakhRPT0=?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?TnNkY3kwV1VyTlk2UkdTcjJhUjgyYVpGMEVmWFRjd21xY1RsdDNpdmNEcnhs?=
 =?utf-8?B?TnNsUDJyRTdKMmkzb3UwNFlWdHJRTVp6ODhYRE1IcUxmY0V4eGJaOTlEUFVu?=
 =?utf-8?B?V1FNS1Z1UitZeC90NmJBTkNIcWw0eHBsa3RnMWE4aUQwUTlNWThHb0ZaQllP?=
 =?utf-8?B?eFd6ZTdRVHhDU1pnMDIwQ2dCTjNaZDVhV0NiSDFwWGZWMFdFUnpEb1VCMTdw?=
 =?utf-8?B?VWd0NUk5QmpNYTF0NFBuZk1aT0RzMHhHWG9VTjJMUm5sdndqY0tVTEFmVUdm?=
 =?utf-8?B?UXh1bDhxY2o5MUh4R2lVZ3JWVmpIY1ZLZ1NDSGxwY2dtcGJrM2s2cGY3YU9X?=
 =?utf-8?B?WXdHYUIySFF5VXZLdnFFL1NMQllRdUJpV2daeFBRWWNUMjZDU00xUEM1L29E?=
 =?utf-8?B?QVRGMzJxSmxaMDNhS3NlY2ZNUXZMUkl0ZkVjSEMxS3RaN3IyNDIyR0ZnK0hV?=
 =?utf-8?B?M3NXY3p2OE1lcWJ4bkwwb284SHMxanBDR0dKR2VoUWhKUXYxbzUwZnZHamlE?=
 =?utf-8?B?a3hwQW12NzRnaFFpajI2dEVKazdCTmk1cmpIYVJoTkpUY2JkOEMvcFFCQmxz?=
 =?utf-8?B?MUxvR0RLNFFZSVhielRaVnN4bG1qNVFBLzlRek44NFdHMVkrTmtTTWxIT2xN?=
 =?utf-8?B?d0FsREhCMmN3cDI4Q2NGY0g1OC9aYXpOK0RPOUx2YzFIWTloRHlYd1dEclpW?=
 =?utf-8?B?eWJ1SkZFWkMrV1NGZkhnb3hpd3Q0eU5oZFJUTmdFSWFBeVUwMm93OHEvSWln?=
 =?utf-8?B?MHcrQnVvN1ZjMzI0bkc4NHJWT2doYm5aN1pWWGVuUGpBUTl6dWNwY3RuODE2?=
 =?utf-8?B?N3BNRTQzOFpqdE52UjQ0SG96eTFyMENzUHdtMHdFUVF1dU43dTRqa2VGTW16?=
 =?utf-8?B?dnAxVTZuYmsxSXV3cFkrenMvT0RHR0JaVkNZQ0ViRmdmTHFET3g0ZGVpR0s4?=
 =?utf-8?B?MXVKUHZFYXE1dlFGTFh4RURwZThhR0lqRlBvR09tOUFVQ1g0aWh5N1Z4bmcv?=
 =?utf-8?B?V3JUTU15R09nVTBjY2o4emtIL2puMm0zRktKTElTeWZXaXl1d1hHV0RGYzdU?=
 =?utf-8?B?eERwOW1XMjdMZkNaTXZMNkdiN2ZBcnRwSjhxVjBmaVhPZmlCVVJzaitUL1Js?=
 =?utf-8?B?aGJHTnNVd1NqSEovOFk4bDBKN3NXUkQ0ZFlkTG4yT0NRdmw0MDJ5Mk9PRVFQ?=
 =?utf-8?B?d2xvVDNsTFYzREtpRi82dGp5Smx2Z3RIUUxnWlhWM3pIbitkZG1SYktMcHRD?=
 =?utf-8?B?Mkc2MHZJVXlEMEdleUlUSzRsaEVnTDBYYVA3Ry9JdWd1b2t3UEUzOENNK3gz?=
 =?utf-8?B?aWUwYlAxT1BWaGZWMThGa0dpaWc1blhDd1p0TTJ6N25nLzFNa0hhMTNsTDZo?=
 =?utf-8?B?TUZsOWxnQUtqQzlKcmZMV2FxODhBam5tZHpEYlhIc0xCRXplelhJMStKTmlB?=
 =?utf-8?B?dTBBZjA5N2F5RWpNZDRmQkRiUTZrTmoyMno0SUY5MHA3VjE5ak1rR3dwMWkv?=
 =?utf-8?B?bndEMVF0ZE9XVHl1VkxIdWFaM1o4OFduM0liKzdlR2ZJMFp1VE5EM2VZNHVO?=
 =?utf-8?B?L0ZGQTVBK3JjQ3FVa0lOeDY2RUJqamRkWTJiaXdJZXNLN1R3ZmF1Vkkza3pJ?=
 =?utf-8?B?MElPMFE5dSt2V3lrWDFMMFpENi9yQVhYZGQ0cFlUT2laMnZ5cUhNaFQyWWhD?=
 =?utf-8?B?OUdKNFVsZHJxOTZDZERFQmh1TktzemxPa2ZxdlQ5d0FUMXNvbVF5M2lJekNL?=
 =?utf-8?B?RlU2T0ZuWWNTcG9rTElkcTdSa1dsM3hERW1pc1FkVko4M0ZBQTVkNklCRW9p?=
 =?utf-8?B?cjRMNjk4cVhDZEtIamNWb1Qrek9tSk1DOHdCeENKNmFBRWJtWDFvaTFBSHdx?=
 =?utf-8?Q?AoVSv3UvJ/bt/?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 564c0e33-a3d1-4d02-73be-08deb86c4d57
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 23 May 2026 01:40:45.5577
 (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: SY7PR01MB8432
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/SY7PR01MB10921CD524617DCF4FE8E2565B60C2%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk

On Fri, 22 May 2026 at 22:05, Shinya Kato <shinya11.kato@gmail.com> wrote:
> Thank you for your feedback!
>
> On Mon, Mar 16, 2026 at 8:19=E2=80=AFPM Japin Li <japinli@hotmail.com> wr=
ote:
>>
>> 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
>> > The priority ordering encoded in XidHorizonBlockerType determines whic=
h blocker gets reported when multiple candidates
>> > exist. In particular:
>> >
>> > ACTIVE_TRANSACTION
>> > IDLE_IN_TRANSACTION
>> > PREPARED_TRANSACTION
>> >
>> > Prepared transactions are currently ranked after idle-in-transaction s=
essions. Operationally, prepared transactions are
>> > often harder for DBAs to resolve than idle sessions, so it might be wo=
rth clarifying the rationale behind this ordering
>> > or reconsidering whether prepared transactions should have higher prio=
rity.
>>
>> Agreed.  Explaining the reason for this priority is very helpful.
>
> We always pick a blocker from the xid-match group first (it is the
> transaction actually holding the horizon, while the xmin-match entries
> are just held back by it). Within the xid-match group, the
> active/idle/prepared order never matters: a given xid is owned by only
> one backend, so when the horizon equals a proc's xid there is only one
> matching entry, and it is exactly one of active, idle, or prepared. So
> moving prepared ahead of idle would not change which blocker we
> report.
>
>> >> 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 th=
e enum 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 th=
e highest-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 mis=
s anything?
>
> I did think about this, but I would like to keep reporting one blocker
> in the VACUUM log, for two reasons.
>
> First, the log can get very large. In Sami's earlier example [0], a
> pgbench run had many backends all sharing the same xmin while only one
> idle-in-transaction backend actually owned the cutoff xid. Reporting
> every blocker would print 20+ lines, almost all of them just victims
> of the same root cause, which makes the log harder to read, not
> easier.
>
> Second, the one blocker we report is the root cause (the xid owner).
> Once the DBA resolves it, the next VACUUM will show the next blocker
> if one remains.
>
> This is also why the code is split into GetXidHorizonBlockers(), which
> already collects every candidate, and GetXidHorizonBlocker(), which
> picks the highest-priority one for the log. The "show everything" case
> is what I would like to expose later through a dynamic statistics
> view, where a full list makes more sense than in a VACUUM log line.
>
>
> I've rebased the patch.
>

Thanks for updating the patch. LGTM. Just one nitpick.

+	int			pid;			/* backend pid (0 for slots) */

Per the code, the prepared transaction is also associated with a PID of zer=
o.

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