db was corrupted, ERROR: cannot freeze committed xmax; fix by deleting rows in catalog tables

Поиск
Список
Период
Сортировка
От milist ujang
Тема db was corrupted, ERROR: cannot freeze committed xmax; fix by deleting rows in catalog tables
Дата
Msg-id CACG9ogyzzzWueyFCV-XPUdMtXN7ZNfLR-60fh1Ht_mWhg0pR=w@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Dmitry O Litvintsev
Дата:
Сообщение: Re: Seeing new stuff in log after upgrading from 11 to 15
Следующее
От: HORDER Philip
Дата:
Сообщение: Re: Restore of a reference database kills the auto analyze processing.