ERROR: cannot freeze committed xmax

From: Sasha Aliashkevich <olsender(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ERROR: cannot freeze committed xmax
Date: 2021-07-14 14:36:22
Message-ID: 8C414E45-6DD8-48E0-84B2-2D53545000D0@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Zhihong Yu 2021-07-14 16:03:21 Re: Have I found an interval arithmetic bug?
Previous Message Tom Lane 2021-07-14 14:30:29 Re: statistic target and sample rate