found xmin x from before relfrozenxid y
Hi,
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?
Best
Johannes
=?UTF-8?Q?Johannes_Gra=c3=abn?= <johannes@selfnet.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
Thanks for your answer.
On 21/10/2018 16.24, Tom Lane wrote:
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.
Since I have so many of them, I decided to go for a quick-and-dirty
solution (what about REFRESH ALL MATERIALIZED VIEWS in the future?):
Show quoted text
DO
$$
DECLARE command text;
BEGIN
FOR command IN SELECT 'REFRESH MATERIALIZED VIEW '|| nspname||'.'||relname||';'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm'
LOOP
RAISE NOTICE '%', command;
EXECUTE command;
END LOOP;
END
$$;
Hi,
On 2018-10-21 10:24:16 -0400, Tom Lane wrote:
=?UTF-8?Q?Johannes_Gra=c3=abn?= <johannes@selfnet.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.
Because it was a really complicated bugfix already, I don't think the
answer is more complicated than that.
(Well, I see the short answer: the code layer throwing the error
doesn't know. But that could be fixed easily enough.)
I wonder if the better approach wouldn't be to add an errcontext for
vaccuum, where continually update the block number etc. Theres plenty of
different sources of corruption that'd potentially cause debug messages
or errors, and that should get most of them.
Greetings,
Andres Freund
Andres Freund <andres@anarazel.de> writes:
On 2018-10-21 10:24:16 -0400, Tom Lane wrote:
(Well, I see the short answer: the code layer throwing the error
doesn't know. But that could be fixed easily enough.)
I wonder if the better approach wouldn't be to add an errcontext for
vaccuum, where continually update the block number etc. Theres plenty of
different sources of corruption that'd potentially cause debug messages
or errors, and that should get most of them.
I did not chase this in detail, but it looked to me like there were
two code paths leading to heap_prepare_freeze_tuple, and only one
of them came from VACUUM. So adding a Relation parameter seemed like
a more promising fix for it. But possibly there are more error messages
we need to worry about besides this.
regards, tom lane