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.