Message-ID: <d5220c2ab77715c6325e33d1b01f67ac073050af.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>
Cc: pgsql-bugs@lists.postgresql.org
Date: Sat, 13 Sep 2025 03:24:37 +0200
In-Reply-To: 
 <CAE4W+SJZee1XJ4my_8AtdDiZJ-SL_JCdo0yb4FXQxhHfXQ6f0w@mail.gmail.com>
References: 
	<CAE4W+SLbLuis0B4mVKaCEYmxcf4C0UHo_UQeRF6ScPqfS24j5Q@mail.gmail.com>
	 <1d7c9fc27e3a304b1f4ba225afdfca9c3b3fb536.camel@cybertec.at>
	 <CAE4W+SJZee1XJ4my_8AtdDiZJ-SL_JCdo0yb4FXQxhHfXQ6f0w@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/d5220c2ab77715c6325e33d1b01f67ac073050af.camel%40cybertec.at>
Precedence: bulk

On Sat, 2025-09-13 at 06:40 +0530, Tushar Takate wrote:
> On Sat, Sep 13, 2025 at 2:40=E2=80=AFAM Laurenz Albe <laurenz.albe@cybert=
ec.at> wrote:
> > 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: ER=
ROR: =C2=A0found xmin 4133102167 from before relfrozenxid 4151440783
> > >=20
> > > 2025-09-11 02:29:58.888 UTC,,,2362287,,68c233e1.240hbf,1,,2025-09-11 =
02:28:49 UTC,122/46371006,0,ERROR,XX001,"found xmin 4133102167 from before =
relfrozenxid 4151440783",,,,,"while scanning block 5821149 offset 5 of rela=
tion ""public.order""
> > > 2025-09-11 02:40:50.361 UTC,"prod_user_ap","live_order_us_db",2375672=
,"127.0.0.1:59344",68c2342b.243ff8,4,"VACUUM",2025-09-11 02:30:03 UTC,169/3=
8875732,0,ERROR,XX001,"found xmin 4133102167 from before relfrozenxid 41514=
40783",,,,,"while scanning block 5821149 offset 5 of relation ""public.orde=
r""","VACUUM (VERBOSE, ANALYZE) public.order;",,,"vacuumdb","client backend=
",,-5528190995457849841
> >=20
> > That is probably caused by a PostgreSQL bug; you can get rid of the mes=
sage
>=20
> In which version can we expect the fix for it? Also, can you please help =
to understand
> which specific condition or scenario is triggering this PostgreSQL error =
and=C2=A0skipping
> to freeze=C2=A0xmin?

I *believe* there must be a bug that causes that problem, because I have se=
en that error
reported often enough that I don't think it can be attributed to hardware e=
rrors.
Unfortunately, I think that nobody knows how it happens, so we cannot fix i=
t.

> > by creating the "pg_surgery" extension and running
> >=20
> > =C2=A0 SELECT heap_force_freeze('public.order'::regclass, '{(5821149,5)=
}'::tid[]);
>=20
> I agree we can run=C2=A0pg_surgery=C2=A0, but the question is how safe it=
 is to run for large and mission-critical tables over 200GB.
> From=C2=A0pg_surgery doc:=C2=A0These functions are unsafe by design and u=
sing them may corrupt (or further corrupt) your database

It is dangerous, and that has nothing to do with the size of the table.
If you do the wrong thing with that knife, you can cause more problems
than you fix.

> > > One more thing/observation we saw in the PostgreSQL logs :
> > >=20
> > > The following message consistently appeared once a day during the pas=
t week
> > >=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 =
visibility map bit is set in relation ""order"" page 5815453",,,,,"while sc=
anning block 5815453 of relation ""public.order""",,,,"","autovacuum worker=
",,0
> > >=20
> > > What specific condition or scenario is triggering this PostgreSQL err=
or?=C2=A0Can it be classified
> > > as a bug? If not, what=E2=80=99s a safe and=C2=A0efficient=C2=A0way t=
o resolve it without relying on a dump
> > > and restore, particularly for large, mission-critical tables over 200=
GB?
> >=20
> > That is some kind of data corruption, perhaps caused by a bug, perhaps =
by
> > something else.=C2=A0 The autovacuum run should fix that problem.
>=20
> This is something supporting data I have provided, before the issue, the =
above WARNING was seen in db-logs for the same table.=C2=A0

Sorry, I cannot parse that sentence.

Yours,
Laurenz Albe