Re: found xmin x from before relfrozenxid y

=?UTF-8?Q?Johannes_Gra=c3=abn?= <johannes(at)selfnet(dot)de> writes:
> after upgrading to version 11, I see the error pattern "found xmin x
> from before relfrozenxid y" in different databases on different hosts.
> From https://www.postgresql.org/docs/10/static/release-10-3.html, I
> learned that this was an error caused by pg_upgrade, which apparently
> had been fixed in 10.3. This page also states that refreshing the
> affected materialized view non-concurrently would fix the problem.
> My question is now how to infer the affected materialized view from the
> error message. Is there a way to tell which one to refresh from the xmin
> or relfrozenxid value?

No :-(. I wonder why in the world we didn't make that error message
include the relation and block number the tuple was found in.

(Well, I see the short answer: the code layer throwing the error
doesn't know. But that could be fixed easily enough.)

In the meantime, the only answer I can think of offhand is to manually
do VACUUM FREEZE on each of your MVs, and then refresh anything that
shows up with an error.

regards, tom lane