Hello,
we have suffered from BUG #17485, so we have upgraded our databases to 14.4.
When (on 14.3 we discovered the issue all affected indexes were rebuilt
[not concurrently]), so subsequent pg_amcheck --heapallindexed was without
errors or warnings.
After the upgrade to 14.4, we run pg_amcheck --heapallindexed again to
ensure all indexes are in a good shape, and on some databases, pg_amcheck
yields some messages (supposed to be wanings since pg_amcheck exit code was
zero).
On some tables, running vacuum freeze resolved all of the warning messages,
but we have at least one table where vacuum freeze (have no chance to run
vacuum full to completely rewrite the table) did not resolve the warnings.
Table vacuum:
live=# VACUUM (DISABLE_PAGE_SKIPPING, VERBOSE)
live.live_opportunities.tab_odds_history;
INFO: aggressively vacuuming "live_opportunities.tab_odds_history"
INFO: table "tab_odds_history": index scan bypassed: 50245 pages from
table (0.20% of total) have 3361027 dead item identifiers
INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO: table "tab_odds_history": found 0 removable, 583340008 nonremovable
row versions in 25325175 out of 25325175 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4076779300
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 81.77 s, system: 122.32 s, elapsed: 243.07 s.
INFO: aggressively vacuuming "pg_toast.pg_toast_18089"
INFO: table "pg_toast_18089": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4076806500
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
pg_amcheck warnings:
$ /usr/pgsql-14/bin/pg_amcheck --heapallindexed -t
live.live_opportunities.tab_odds_history -P |& tee -a
20220630_amcheck.03.log
0/5 relations (0%), 0/27755550 pages (0%)
heap table "live.live_opportunities.tab_odds_history", block 2955073,
offset 48:
xmin 4075979939 precedes relation freeze threshold 1:4075978425
...
heap table "live.live_opportunities.tab_odds_history", block 2955076,
offset 27:
xmin 4075980540 precedes relation freeze threshold 1:4075978425
Checked the XIDs:
live=# select relfrozenxid, relminmxid from pg_class where oid =
'live_opportunities.tab_odds_history'::regclass::oid;
relfrozenxid | relminmxid
--------------+------------
4075978425 | 1144161405
(1 row)
live=# select now(), pg_xact_commit_timestamp('4075978425'::xid) AS
relfrozenxid_time, pg_xact_commit_timestamp('4075979939'::xid) as
block_2955073_xmin;
now | relfrozenxid_time |
block_2955073_xmin
-------------------------------+-------------------------------+-------------------------------
2022-06-30 11:57:42.800162+02 | 2022-06-30 11:33:00.729004+02 | 2022-06-30
11:33:57.208792+02
(1 row)
live=# select now(), pg_xact_commit_timestamp('4075978425'::xid) AS
relfrozenxid_time, pg_xact_commit_timestamp('4075980540'::xid) as
block_2955076_xmin;
now | relfrozenxid_time |
block_2955076_xmin
------------------------------+-------------------------------+-------------------------------
2022-06-30 11:53:39.05382+02 | 2022-06-30 11:33:00.729004+02 | 2022-06-30
11:34:28.281048+02
(1 row)
The table relfrozenxid is less than the tuple xmin reported by pg_amcheck,
that makes me confused by the pg_amcheck messages - so probably I did not
understand the warning message properly.
What is the meaning of the warning (if they are really only warnings)
message?
The key question is - are these pg_amcheck messages listed above a reason
to take an remedy action (if yes, what is the suggested action?) or they
can be safely ignored?
Thanks Ales Zeleny
