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 1vIlnS-00DhxT-3C for pgsql-hackers@arkaria.postgresql.org; Tue, 11 Nov 2025 10:40:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vIlnQ-005QDU-1c for pgsql-hackers@arkaria.postgresql.org; Tue, 11 Nov 2025 10:40:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vIlnQ-005QDM-0Y for pgsql-hackers@lists.postgresql.org; Tue, 11 Nov 2025 10:40:20 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vIlnN-0078wF-2P for pgsql-hackers@lists.postgresql.org; Tue, 11 Nov 2025 10:40:19 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-64080ccf749so6364493a12.2 for ; Tue, 11 Nov 2025 02:40:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1762857616; x=1763462416; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=wbC9c/RvwoAKYLWCYRBTpYGIlKfaXLDFBtOP3Mfj6XI=; b=TzvzZ8DEhjN8PLjVljtee6O+wMuipovBc/qwYeuix2tQSFluVzMAcl0o9st3NsMG2y wPiX10XAJeqAA24Gq+LLIVl4YUGYP7q64ROGQw5UIZeBuIibQmfD/J4R+S1ZPkBtjG59 bVFq7Dl+lwzc8OSLXh8b/Ac9neqxoSTRGjI5+T2+OdR8Z8bW/9SKfWnj8S7rRCWArZBT ayX3p7JhHm9bNAI1d58SNclTxKs57WYodJAYiVtVSOacAyrE/idUvNJy7fvYgtS80dsR vYrPvyb8khgyktZL1ZZDtvrs6QNC+n52Ri1S3bwKLXRQXzq7twry0ldhUN0U9lh4bzTc wmog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1762857616; x=1763462416; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=wbC9c/RvwoAKYLWCYRBTpYGIlKfaXLDFBtOP3Mfj6XI=; b=o15KRY6vO6lzVNCNuzyM487qqr9tyjlxbaie1JuFtjVvqHiKUze3HgykV6soMnjVHH RGa/lZfEOG2z2Id7TbwhHGfZaKfaCwZ28VC3vuaVn8bqtycEbuACjtMXEaYQ+rgSS+Bb s1ySD+C4hnsyaAOvmMvMV6RzkD9O+5LuJVyi4mNgIqSZiHOW2qW3crh/xbZuQr6AV9vG Kv6LY300Rcc3Ed3YQuBKdvU8zjopsdcvACGFxJXiKhWbcyVDXDt3lGldVVvDBm2Y34oP yxApf+wtPPJH1HWgDz6FS37CMQ85+zvtg/CYDK8b0VNtroEdnb3Jat6iObJmpjYlCnYq Tj/g== X-Gm-Message-State: AOJu0Yz2NvNiVjbq9MGB8zZcJkIhPdAOBKo1rm8/CgRe7ZMoC74aFXMJ bwhq8qwMYQEEg6a3mDYj7GKGDirDkRaBhzKKXlMhgQI/W/RotZqCpISfqOebyOZOsyhY5OFcVpz Qw97CAEtpoJsR3uNvT7vQlB3FsMqmrGk= X-Gm-Gg: ASbGncuXTrgFQ/6eQTFFj9nePbt5BPUdkMT4ntNUn7v3l7Bn+6nzzor/NH/FuXjn4/b 2grI4BLaI+CCgaeKQ2+Niq/fDJnWAJnmllRgo9fZfT+G8WnpWaj2alGA3Sv/O809qVKCT6qMwQ0 RePd3XKGVxF1HcWe9dMHyagV5UXGUejMWdWs0u3cVv6zyxW8Y48n8y9oYJUD2X7jhXyHlw0ghiB D221G4uVtrZ6icQZsVlhJFybrtGnZpH+h60laNGCiDZPelHqfw8GVWJHgWWMUCNSETuLA== X-Google-Smtp-Source: AGHT+IHYaaoVK/wUdpt+lIoV7mGAu693k9t+Oi6GHz5Sbs/yi16SurNdbRgVhLjXxVndHj1phWphfyi3rvh12BFB1e0= X-Received: by 2002:a05:6402:40c2:b0:640:c3c4:45f3 with SMTP id 4fb4d7f45d1cf-6415e5ccd11mr9915406a12.6.1762857616154; Tue, 11 Nov 2025 02:40:16 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: wenhui qiu Date: Tue, 11 Nov 2025 18:40:04 +0800 X-Gm-Features: AWmQ_blvUN3XUBZjzZLoz7zjIuKq2gr-ZM5tWfisPuf3HsAzbYq8CLdjzfK3hZs Message-ID: Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples To: Shinya Kato Cc: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="0000000000000029ca06434f468e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000029ca06434f468e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Thank you for your response , In this context, that session state is idle in transaction .If we could further distinguish between active sessions that are still running and idle sessions, that would be clearer. Thanks On Tue, Nov 4, 2025 at 11:59=E2=80=AFPM Fujii Masao = wrote: > On Fri, Oct 31, 2025 at 3:32=E2=80=AFPM Shinya Kato > wrote: > > > > Hi hackers, > > > > I am proposing to add the reason for the oldest xmin to VACUUM logs. > > This feature would be useful for identifying why dead tuples cannot be > > removed, thereby helping to diagnose and prevent table bloat. > > +1 > > I like this idea. Thanks for working on this! > > > > This PoC patch addresses this problem. The implementation now outputs > > the reason for the oldest xmin and, where applicable, the backend PID. > > This information was originally discarded when calculating the oldest > > xmin horizon, and the computation required to retrieve these reasons > > is considered reasonable. > > > > The patch is attached. What do you think? > > According to cfbot, the 010_autovacuum_oldest_xmin_reason regression test > passes on some platforms but fails on others (see [1]), so it doesn't > appear stable. > > > When I set up a primary and standby with hot_standby_feedback enabled, > then created an old prepared transaction expected to prevent dead tuples > from being vacuumed, VACUUM VERBOSE reported "hot standby feedback" > instead of "prepared transaction" as the oldest xmin source. This isn't a > bug > since both xmins are the same in this case. But it may be confusing? > Would it be better to report "prepared transaction" in such cases? > > > + case OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION: > + msgfmt =3D include_pid ? > + _("oldest xmin source: active transaction (pid=3D%d)\n") : > + _("oldest xmin source: active transaction\n"); > + break; > + case OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK: > + msgfmt =3D include_pid ? > + _("oldest xmin source: hot standby feedback (pid=3D%d)\n") : > + _("oldest xmin source: hot standby feedback\n"); > > In these two cases, the PID should always be non-zero, so the message > formats without (pid=3D%d) might not be necessary. > > > + /* Identify what forced each of the horizons above. */ > + OldestXminInfo shared_oldest_nonremovable_info; > + OldestXminInfo catalog_oldest_nonremovable_info; > + OldestXminInfo data_oldest_nonremovable_info; > + OldestXminInfo temp_oldest_nonremovable_info; > > It might be good to add a comment explaining why we track > sources only for these four oldest xmins, and not for others > like oldest_considered_running. > > > + TransactionId old; > + TransactionId new_horizon; > + > + if (!TransactionIdIsValid(candidate)) > + return; > > The TransactionIdIsValid(candidate) check may be redundant, > since TransactionIdOlder(old, candidate) already performs > the same validation. > > > - switch (GlobalVisHorizonKindForRel(rel)) > + kind =3D GlobalVisHorizonKindForRel(rel); > + switch (kind) > > This change doesn't seem necessary. > > Regards, > > [1] https://cirrus-ci.com/task/6063548834512896 > > -- > Fujii Masao > > > --0000000000000029ca06434f468e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0
Thank you for your response ,=C2=A0In this co= ntext, that session =C2=A0state is idle in transactio= n .If we could further distinguish between active sessions that are = still running and idle sessions, that would be clearer.




Thanks

On Tue, Nov 4, 2025 at 11:59=E2=80=AFPM Fujii= Masao <masao.fujii@gmail.com> wrote:
On Fri, Oct 31, 2025 at 3:32=E2=80= =AFPM Shinya Kato <shinya11.kato@gmail.com> wrote:
>
> Hi hackers,
>
> I am proposing to add the reason for the oldest xmin to VACUUM logs. > This feature would be useful for identifying why dead tuples cannot be=
> removed, thereby helping to diagnose and prevent table bloat.

+1

I like this idea. Thanks for working on this!


> This PoC patch addresses this problem. The implementation now outputs<= br> > the reason for the oldest xmin and, where applicable, the backend PID.=
> This information was originally discarded when calculating the oldest<= br> > xmin horizon, and the computation required to retrieve these reasons > is considered reasonable.
>
> The patch is attached. What do you think?

According to cfbot, the 010_autovacuum_oldest_xmin_reason regression test passes on some platforms but fails on others (see [1]), so it doesn't appear stable.


When I set up a primary and standby with hot_standby_feedback enabled,
then created an old prepared transaction expected to prevent dead tuples from being vacuumed, VACUUM VERBOSE reported "hot standby feedback&quo= t;
instead of "prepared transaction" as the oldest xmin source. This= isn't a bug
since both xmins are the same in this case. But it may be confusing?
Would it be better to report "prepared transaction" in such cases= ?


+ case OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION:
+ msgfmt =3D include_pid ?
+ _("oldest xmin source: active transaction (pid=3D%d)\n") :
+ _("oldest xmin source: active transaction\n");
+ break;
+ case OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK:
+ msgfmt =3D include_pid ?
+ _("oldest xmin source: hot standby feedback (pid=3D%d)\n") : + _("oldest xmin source: hot standby feedback\n");

In these two cases, the PID should always be non-zero, so the message
formats without (pid=3D%d) might not be necessary.


+ /* Identify what forced each of the horizons above. */
+ OldestXminInfo shared_oldest_nonremovable_info;
+ OldestXminInfo catalog_oldest_nonremovable_info;
+ OldestXminInfo data_oldest_nonremovable_info;
+ OldestXminInfo temp_oldest_nonremovable_info;

It might be good to add a comment explaining why we track
sources only for these four oldest xmins, and not for others
like oldest_considered_running.


+ TransactionId old;
+ TransactionId new_horizon;
+
+ if (!TransactionIdIsValid(candidate))
+ return;

The TransactionIdIsValid(candidate) check may be redundant,
since TransactionIdOlder(old, candidate) already performs
the same validation.


- switch (GlobalVisHorizonKindForRel(rel))
+ kind =3D GlobalVisHorizonKindForRel(rel);
+ switch (kind)

This change doesn't seem necessary.

Regards,

[1] https://cirrus-ci.com/task/6063548834512896

--
Fujii Masao


--0000000000000029ca06434f468e--