Partial solution to observed " MultiXactId ### has not been created yet -- apparent wraparound" issue with newly upgraded db

From: Bill Mitchell <bill(at)publicrelay(dot)com>
To: General PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: Partial solution to observed " MultiXactId ### has not been created yet -- apparent wraparound" issue with newly upgraded db
Date: 2014-06-04 15:38:24
Message-ID: 538F3D70.6080902@publicrelay.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general

I wanted to share an experience that we had this morning, after the
upgrade from postgres 9.2.7 to postgres 9.3.4 on our production system.
(The data set was upgraded in QA without issues,but I rather suspect
that I had done a full 'vacuum' on that data set more recently). NOTE:
this was within an Amazon Web Services machine, so it was using Amazon's
packaged 9.2.7 version, and then we moved back to the RPMs pointed to by
wget
http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
in order to get up to the 9.3 level...

*The symptom:*

db# vacuum country_state;
ERROR: MultiXactId 4800022 has not been created yet -- apparent wraparound

db# vacuum country;
ERROR: MultiXactId 4800023 has not been created yet -- apparent wraparound

*The solution (for small table)*
Researching the mailing lists, I found this
http://postgresql.1045698.n5.nabble.com/MultiXactId-error-after-upgrade-to-9-3-4-td5797923.html

So, following this advice, I did the following:
select * from country_state for update \g /tmp/ignore;
select * from country for update \g /tmp/ignore;

and now vacuum is happy with those tables again.

*What's not working , still...*

Unfortunately, getting this same symptom on a 3rd table is getting a bit
large (102GB, with 28million rows)
db=# vacuum article;
ERROR: MultiXactId 4998189 has not been created yet -- apparent wraparound

I'm wondering if there is a query which would just return the rows which
had a "bad t_xmax value" (or if we can even differentiate those from
correct values). - such that I can only search for those with the 'for
update' construct.

Some common information that I've seen provided in the past by others:

db=# select txid_current();
txid_current
--------------
1008772125
(1 row)

db=# SELECT datname, datfrozenxid FROM pg_database where datname='db';
datname | datfrozenxid
-------------+--------------
db | 851079261
(1 row)

db=# show autovacuum_freeze_max_age;
autovacuum_freeze_max_age
---------------------------
200000000
(1 row)

db=# show vacuum_freeze_min_age;
vacuum_freeze_min_age
-----------------------
50000000
(1 row)

db=# show vacuum_freeze_table_age;
vacuum_freeze_table_age
-------------------------
150000000
(1 row)

Thanks in advance for any advice
Bill

Browse pgsql-general by date

  From Date Subject
Next Message Tim Kane 2014-06-04 16:04:23 Re: [GENERAL] Migrating from 9.2.4 to 9.3.0 with XML DOCTYPE
Previous Message Tom Lane 2014-06-04 15:06:26 Re: Migrating from 9.2.4 to 9.3.0 with XML DOCTYPE