Re: ERROR: found xmin 4133102167 from before relfrozenxid 4151440783

On Fri, 2025-09-12 at 22:09 +0530, Tushar Takate wrote:
> PostgreSQL version: 15.12
> Disk type: RAID5
> OS: RHEL 8.10
>
> Error/Issue :
>
> vacuumdb: error: processing of database "live_order_us_db" failed: ERROR:  found xmin 4133102167 from before
relfrozenxid4151440783 
>
> 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
xmin4133102167 from before relfrozenxid 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,"127.0.0.1:59344",68c2342b.243ff8,4,"VACUUM",2025-09-1102:30:03
UTC,169/38875732,0,ERROR,XX001,"foundxmin 4133102167 from before relfrozenxid 4151440783",,,,,"while scanning block
5821149offset 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 :
>
> The following message consistently appeared once a day during the past week
>
> 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
notmarked all-visible but visibility map bit is set in relation ""order"" page 5815453",,,,,"while scanning block
5815453of relation ""public.order""",,,,"","autovacuum worker",,0 
>
> What specific condition or scenario is triggering this PostgreSQL error? Can it be classified
> as a bug? If not, what’s a safe and efficient way to resolve 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