Re: ERROR: found xmin 54230249 from before relfrozenxid 61349053

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.