ERROR: cannot freeze committed xmax

Поиск
Список
Период
Сортировка
От Sasha Aliashkevich
Тема ERROR: cannot freeze committed xmax
Дата
Msg-id 8C414E45-6DD8-48E0-84B2-2D53545000D0@gmail.com
обсуждение исходный текст
Ответы Re: ERROR: cannot freeze committed xmax  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Список 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
rolledback 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)




В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: statistic target and sample rate
Следующее
От: Adam Norwood
Дата:
Сообщение: Problem upgrading from Postgresql 9.5