3

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.

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 = 200

We are using PostgreSQL 9.4.16, single node solution on RedHat 7.

Can you help me?

  • Did you upgrade recently? Is propertyentry a materialized view? – Erwin Brandstetter Mar 30 '18 at 14:56
  • Yes, I upgraded our DB from 9.2 to 9.4.16 via pg_upgrade. – Rohlik Mar 30 '18 at 16:26
2

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 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 <xid> may be yours:

Fixed, but not released, yet. Should be in 9.4.18.

  • Can be this reason why I have hit ratio 74.80 on that DB? – Rohlik Apr 2 '18 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.