Обсуждение: ERROR: found xmin 54230249 from before relfrozenxid 61349053

Поиск
Список
Период
Сортировка

ERROR: found xmin 54230249 from before relfrozenxid 61349053

От
rihad
Дата:
Hi all. We see such errors on one of our own (non-system) tables on 
Postgres 10.3. They stop both automatic vacuum & automatic analyze not 
only on that table, but on all tables & databases (manual vacuuming 
works though). Luckily it's a small (but important) table - less than 
1000 rows. Any quick way of fixing it with no downtime? Thanks.

Re: ERROR: found xmin 54230249 from before relfrozenxid 61349053

От
rihad
Дата:
On 08/02/2018 04:38 PM, rihad wrote:
> Hi all. We see such errors on one of our own (non-system) tables on 
> Postgres 10.3. They stop both automatic vacuum & automatic analyze not 
> only on that table, but on all tables & databases (manual vacuuming 
> works though). Luckily it's a small (but important) table - less than 
> 1000 rows. Any quick way of fixing it with no downtime? Thanks.
>

BTW, it's a materialized view, not a table. "refresh materialized view 
concurrently" is being run on it regularly, but apparently doesn't fix 
the problem.

Re: ERROR: found xmin 54230249 from before relfrozenxid 61349053

От
rihad
Дата:
On 08/02/2018 05:34 PM, rihad wrote:
On 08/02/2018 04:38 PM, rihad wrote:
Hi all. We see such errors on one of our own (non-system) tables on Postgres 10.3. They stop both automatic vacuum & automatic analyze not only on that table, but on all tables & databases (manual vacuuming works though). Luckily it's a small (but important) table - less than 1000 rows. Any quick way of fixing it with no downtime? Thanks.


BTW, it's a materialized view, not a table. "refresh materialized view concurrently" is being run on it regularly, but apparently doesn't fix the problem.


Answering to myself: this probably relates to this issue:

https://www.postgresql.org/docs/9.4/static/release-9-4-17.html

  • Repair pg_upgrade's failure to preserve relfrozenxid for materialized views (Tom Lane, Andres Freund)

    This oversight could lead to data corruption in materialized views after an upgrade, manifesting as "could not access status of transaction" or "found xmin from before relfrozenxid" errors. The problem would be more likely to occur in seldom-refreshed materialized views, or ones that were maintained only with REFRESH MATERIALIZED VIEW CONCURRENTLY.

    If such corruption is observed, it can be repaired by refreshing the materialized view (without CONCURRENTLY).


Fixed on 2018-03-01, so the bug existed not only for 9.4.17, but for all major versions too.