Message-ID: <1d7c9fc27e3a304b1f4ba225afdfca9c3b3fb536.camel@cybertec.at>
Subject: Re: ERROR: found xmin 4133102167 from before relfrozenxid 4151440783
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tushar Takate <tushar11.takate@gmail.com>,
	pgsql-bugs@lists.postgresql.org
Date: Fri, 12 Sep 2025 23:10:34 +0200
In-Reply-To: 
 <CAE4W+SLbLuis0B4mVKaCEYmxcf4C0UHo_UQeRF6ScPqfS24j5Q@mail.gmail.com>
References: 
	<CAE4W+SLbLuis0B4mVKaCEYmxcf4C0UHo_UQeRF6ScPqfS24j5Q@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/1d7c9fc27e3a304b1f4ba225afdfca9c3b3fb536.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-09-12 at 22:09 +0530, Tushar Takate wrote:
> PostgreSQL version: 15.12
> Disk type: RAID5
> OS: RHEL 8.10
>=20
> Error/Issue :=20
>=20
> vacuumdb: error: processing of database "live_order_us_db" failed: ERROR:=
 =C2=A0found xmin 4133102167 from before relfrozenxid 4151440783
>
> 2025-09-11 02:29:58.888 UTC,,,2362287,,68c233e1.240hbf,1,,2025-09-11 02:2=
8:49 UTC,122/46371006,0,ERROR,XX001,"found xmin 4133102167 from before relf=
rozenxid 4151440783",,,,,"while scanning block 5821149 offset 5 of relation=
 ""public.order""
> 2025-09-11 02:40:50.361 UTC,"prod_user_ap","live_order_us_db",2375672,"12=
7.0.0.1:59344",68c2342b.243ff8,4,"VACUUM",2025-09-11 02:30:03 UTC,169/38875=
732,0,ERROR,XX001,"found xmin 4133102167 from before relfrozenxid 415144078=
3",,,,,"while scanning block 5821149 offset 5 of relation ""public.order"""=
,"VACUUM (VERBOSE, ANALYZE) public.order;",,,"vacuumdb","client backend",,-=
5528190995457849841

That is probably caused by a PostgreSQL bug; you can get rid of the message
by creating the "pg_surgery" extension and running

  SELECT heap_force_freeze('public.order'::regclass, '{(5821149,5)}'::tid[]=
);

> One more thing/observation we saw in the PostgreSQL logs :
>=20
> The following message consistently appeared once a day during the past we=
ek
>=20
> 2025-09-10 23:33:14.469 UTC,,,157915,,68c21a46.268fb,3,,2025-09-10 23:31:=
18 UTC,45/49119328,0,WARNING,01000,"page is not marked all-visible but visi=
bility map bit is set in relation ""order"" page 5815453",,,,,"while scanni=
ng block 5815453 of relation ""public.order""",,,,"","autovacuum worker",,0
>=20
> What specific condition or scenario is triggering this PostgreSQL error?=
=C2=A0Can it be classified
> as a bug? If not, what=E2=80=99s a safe and=C2=A0efficient=C2=A0way to re=
solve it without relying on a dump
> and restore, particularly for large, mission-critical tables over 200GB?

That is some kind of data corruption, perhaps caused by a bug, perhaps by
something else.  The autovacuum run should fix that problem.

Yours,
Laurenz Albe