public inbox for [email protected]
help / color / mirror / Atom feedFrom: milist ujang <[email protected]>
To: [email protected]
Subject: db was corrupted, ERROR: cannot freeze committed xmax; fix by deleting rows in catalog tables
Date: Thu, 16 May 2024 11:01:27 +0700
Message-ID: <CACG9ogyzzzWueyFCV-XPUdMtXN7ZNfLR-60fh1Ht_mWhg0pR=w@mail.gmail.com> (raw)
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: db was corrupted, ERROR: cannot freeze committed xmax; fix by deleting rows in catalog tables
In-Reply-To: <CACG9ogyzzzWueyFCV-XPUdMtXN7ZNfLR-60fh1Ht_mWhg0pR=w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox