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
