MIME-Version: 1.0
From: milist ujang <ujang.milist@gmail.com>
Date: Thu, 16 May 2024 11:01:27 +0700
Message-ID: <CACG9ogyzzzWueyFCV-XPUdMtXN7ZNfLR-60fh1Ht_mWhg0pR=w@mail.gmail.com>
Subject: db was corrupted, ERROR: cannot freeze committed xmax; fix by
 deleting rows in catalog tables
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ea62a406188a4a0b"
Archived-At: <https://www.postgresql.org/message-id/CACG9ogyzzzWueyFCV-XPUdMtXN7ZNfLR-60fh1Ht_mWhg0pR%3Dw%40mail.gmail.com>
Precedence: bulk

--000000000000ea62a406188a4a0b
Content-Type: text/plain; charset="UTF-8"

Hi all,

I have a case on pg12.9 - rhel8 which crashed a long time ago and now in
log keep saying:

automatic vacuum of table "dbnamexx.pg_catalog.pg_statistic"automatic
vacuum of table "dbnamexx.pg_catalog.pg_class"automatic vacuum of table
"dbnamexx.pg_toast.pg_toast_2619"

automatic vacuum of table "dbnamexx.pg_catalog.pg_class"automatic vacuum of
table "dbnamexx.ibent.loginsession_old"automatic vacuum of table
"dbnamexx.pg_toast.pg_toast_2619"

The problem is on the user tables:

vacuum schema.tbl1_old;
ERROR:  cannot freeze committed xmax ...

vacuum schema.tbl2_old;
ERROR:  cannot freeze committed xmax ...

vacuum schema.tbl3_bak;
ERROR:  cannot freeze committed xmax ...

vacuum schema.tbl4_old;
ERROR:  cannot freeze committed xmax ...

unluckily cannot drop those tables. will return "ERROR:  cannot freeze
committed xmax " too

figured out by pg_catcheck utility there are orphaned catalog entries, so
try to fix on restore database by these DML:

delete from pg_attribute where attrelid=<many id>;
delete from pg_index where indrelid=<id>;
delete from pg_statistic where starelid=<many id>;
delete from pg_constraint where conrelid=...;
delete from pg_depend where refobjid=...;
delete from pg_depend where objid=....;
delete from pg_shdepend where objid=...;

delete from pg_type where typrelid=...;
delete from pg_type where  typelem=...;

delete from pg_statistic where starelid=.... and staattnum=27 and
stainherit='f';

recheck by pg_catcheck, now clean.
then vacuum now without error.

My question:
1. is it OK DML on catalog tables?
2. Are there any impacts in the future?


-- 
regards

ujang jaenudin | Self-Employed, DBA Consultant
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

--000000000000ea62a406188a4a0b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi all,<div><br></div><div>I have a case on pg12.9 - rhel8=
 which crashed a long time ago and now in log keep saying:</div><div><br></=
div><div>automatic vacuum of table &quot;dbnamexx.pg_catalog.pg_statistic&q=
uot;automatic vacuum of table &quot;dbnamexx.pg_catalog.pg_class&quot;autom=
atic vacuum of table &quot;dbnamexx.pg_toast.pg_toast_2619&quot;</div><div>=
<br>automatic vacuum of table &quot;dbnamexx.pg_catalog.pg_class&quot;autom=
atic vacuum of table &quot;dbnamexx.ibent.loginsession_old&quot;automatic v=
acuum of table &quot;dbnamexx.pg_toast.pg_toast_2619&quot;</div><div><div><=
br></div><div>The problem is on the user tables:</div><div><br></div>vacuum=
 schema.tbl1_old;<br>ERROR: =C2=A0cannot freeze committed xmax ...<br><br>v=
acuum schema.tbl2_old;<br>ERROR: =C2=A0cannot freeze committed xmax ...<br>=
<br>vacuum schema.tbl3_bak;<br>ERROR: =C2=A0cannot freeze committed xmax ..=
.<br><br>vacuum schema.tbl4_old;<br>ERROR: =C2=A0cannot freeze committed xm=
ax ...<br><div></div></div><div><br></div><div>unluckily cannot drop those=
=C2=A0tables. will return &quot;ERROR: =C2=A0cannot freeze committed xmax=
=C2=A0&quot; too</div><div><br></div><div>figured out by pg_catcheck utilit=
y there are orphaned catalog entries, so try to fix on restore database by =
these DML:</div><div><br></div><div>delete from pg_attribute where attrelid=
=3D&lt;many id&gt;;</div><div>delete from pg_index where indrelid=3D&lt;id&=
gt;;</div><div>delete from pg_statistic where starelid=3D&lt;many id&gt;;</=
div><div>delete from pg_constraint where conrelid=3D...;</div><div>delete f=
rom pg_depend where refobjid=3D...;</div><div>delete from pg_depend where o=
bjid=3D....;</div><div>delete from pg_shdepend where objid=3D...;</div><div=
><br></div><div>delete from pg_type where typrelid=3D...;</div><div>delete =
from pg_type where =C2=A0typelem=3D...;</div><div><br></div><div>delete fro=
m pg_statistic where starelid=3D.... and staattnum=3D27 and stainherit=3D&#=
39;f&#39;;<br clear=3D"all"><div><br></div><div>recheck by pg_catcheck, now=
 clean.</div><div>then vacuum now without error.</div><div><br></div><div>M=
y question:</div><div>1. is it OK DML on catalog tables?</div><div>2. Are t=
here any impacts in the future?</div><div><br></div><div>=C2=A0</div><span =
class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gm=
ail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">regards<=
br><br>ujang jaenudin | Self-Employed, DBA Consultant<br><a href=3D"http://=
id.linkedin.com/pub/ujang-jaenudin/12/64/bab" target=3D"_blank">http://id.l=
inkedin.com/pub/ujang-jaenudin/12/64/bab</a><br></div></div></div></div>

--000000000000ea62a406188a4a0b--