pg_amcheck warnings after upgrade to 14.4

[Aleš Zelený <zeleny.ales@xxxxxxxxx>]

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