Hi,
Few weeks ago at one of the databases we started to observe the following error in Postgresql logs:
ERROR: cannot freeze committed xmax 572
For the note it's Postgresql 10.14 running on RHEL:
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
Apparently it happens during the autovacuum and leads to the failing autovacuum freeze progress.
We have detected two dictionary tables where manual VACUUM FREEZE fails: pg_proc and pg_depend.
Below you can see the page_inspect results for the appropriate blocks as well as the status of xid.
Am I understand correctly that for some reason the transaction is marked as commited while on the page level it's rolled back so the inconsistency is detected?
Could you advise how to fix it and make autovacuum work again?
SELECT txid_status(572);
txid_status
-------------
committed
(1 row)
SELECT ctid, xmin, xmax FROM pg_proc WHERE xmax=572;
ctid | xmin | xmax
---------+------+------
(75,19) | 571 | 572
(1 row)
SELECT ctid, xmin, xmax FROM pg_depend WHERE xmax=572;
ctid | xmin | xmax
---------+------+------
(55,76) | 571 | 572
(55,77) | 571 | 572
(55,78) | 571 | 572
(55,79) | 571 | 572
(55,80) | 571 | 572
(5 rows)
SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact
FROM heap_page_item_attrs(
get_raw_page('pg_proc', 75),
'pg_proc'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact
----+---------+-----------+------+--------------+----------------+------------------+----------------
1 | (75,1) | 564 | 0 | f | f | t | f
2 | (75,2) | 564 | 0 | f | f | t | f
3 | (75,3) | 564 | 0 | f | f | t | f
4 | (75,4) | 564 | 0 | f | f | t | f
5 | (75,5) | 564 | 0 | f | f | t | f
6 | (75,6) | 564 | 0 | f | f | t | f
7 | (75,7) | 564 | 0 | f | f | t | f
8 | (75,8) | 564 | 0 | f | f | t | f
9 | (75,9) | 564 | 0 | f | f | t | f
10 | (75,10) | 566 | 0 | f | f | t | f
11 | (75,11) | 566 | 0 | f | f | t | f
12 | | | | | | |
13 | | | | | | |
14 | (75,14) | 212156121 | 0 | f | f | t | f
15 | (75,15) | 212156121 | 0 | f | f | t | f
16 | (75,16) | 212156121 | 0 | f | f | t | f
17 | (75,17) | 212156121 | 0 | f | f | t | f
18 | (75,18) | 212156121 | 0 | f | f | t | f
19 | (75,21) | 571 | 572 | f | f | t | f
20 | (75,20) | 212156121 | 0 | f | f | t | f
21 | (75,21) | 572 | 0 | f | f | t | f
22 | (75,22) | 97113057 | 0 | f | f | t | f
23 | (75,23) | 212156121 | 0 | f | f | t | f
24 | (75,24) | 212156121 | 0 | f | f | t | f
25 | (75,25) | 212156121 | 0 | f | f | t | f
26 | (75,26) | 212156121 | 0 | f | f | t | f
27 | (75,27) | 212156121 | 0 | f | f | t | f
(27 rows)
SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact
FROM heap_page_item_attrs(
get_raw_page('pg_depend', 55),
'pg_depend'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact
-----+----------+-----------+------+--------------+----------------+------------------+----------------
...
75 | | | | | | |
76 | (55,76) | 571 | 572 | f | f | t | f
77 | (55,77) | 571 | 572 | f | f | t | f
78 | (55,78) | 571 | 572 | f | f | t | f
79 | (55,79) | 571 | 572 | f | f | t | f
80 | (55,80) | 571 | 572 | f | f | t | f
81 | | | | | | |
...
85 | | | | | | |
86 | (55,86) | 572 | 0 | f | f | t | f
87 | | | | | | |
88 | (55,88) | 572 | 0 | f | f | t | f
89 | | | | | | |
90 | | | | | | |
91 | (55,91) | 572 | 0 | f | f | t | f
92 | (55,92) | 572 | 0 | f | f | t | f
93 | (55,93) | 572 | 0 | f | f | t | f
94 | (55,94) | 572 | 0 | f | f | t | f
...
(169 rows)
