debugsqlMinor
PostgreSQL ERROR: cannot freeze committed xmax
Viewed 0 times
postgresqlcannoterrorcommittedfreezexmax
Problem
First of all, I am new to PostgreSQL database.
In my PostgreSQL log I can see errors bellow and I don't know what is wrong or what I should change.
I use default vacuum settings except this one parameter:
We are using PostgreSQL
Can you help me?
In my PostgreSQL log I can see errors bellow and I don't know what is wrong or what I should change.
2018-03-29 10:54:38 CEST::@:[5059]: CONTEXT: automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:55:38 CEST::@:[5144]: ERROR: cannot freeze committed xmax 710196
2018-03-29 10:55:38 CEST::@:[5144]: CONTEXT: automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:56:38 CEST::@:[5182]: ERROR: cannot freeze committed xmax 710196
2018-03-29 10:56:38 CEST::@:[5182]: CONTEXT: automatic vacuum of table "jiradb.public.propertyentry"
2018-03-29 10:57:38 CEST::@:[5208]: ERROR: cannot freeze committed xmax 710196
2018-03-29 10:57:38 CEST::@:[5208]: CONTEXT: automatic vacuum of table "jiradb.public.propertyentry"I use default vacuum settings except this one parameter:
vacuum_cost_limit = 200We are using PostgreSQL
9.4.16, single node solution on RedHat 7. Can you help me?
Solution
Two leads:
If "jiradb.public.propertyentry" is a materialized view, you might be running into a bug that was fixed with the release of 9.4.17 on 2018-03-01. The release notes:
-
Repair pg_upgrade's failure to preserve
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
If such corruption is observed, it can be repaired by refreshing the
materialized view (without
The last line also hints how to repair this.
Or, if you upgraded from Postgres 9.2 or older, this other issue resulting in
Fixed, but not released, yet. Should be in 9.4.18.
If "jiradb.public.propertyentry" is a materialized view, you might be running into a bug that was fixed with the release of 9.4.17 on 2018-03-01. The release notes:
-
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).The last line also hints how to repair this.
Or, if you upgraded from Postgres 9.2 or older, this other issue resulting in
cannot freeze committed xmax may be yours:- https://www.postgresql.org/message-id/E1erVTy-0005Lp-8p%40gemulon.postgresql.org
- https://www.postgresql.org/message-id/CAPYLKR6yxV4=pfW0Gwij7aPNiiPx+3ib4USVYnbuQdUtmkMaEA@mail.gmail.com
Fixed, but not released, yet. Should be in 9.4.18.
Context
StackExchange Database Administrators Q#202690, answer score: 2
Revisions (0)
No revisions yet.