Message-ID: <538F3D70.6080902@publicrelay.com>
Date: Wed, 04 Jun 2014 11:38:24 -0400
From: Bill Mitchell <bill@publicrelay.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: General PostgreSQL List <pgsql-general@postgresql.org>
Subject: Partial solution to observed " MultiXactId ### has not been created
 yet -- apparent wraparound" issue with newly upgraded db
Content-Type: multipart/alternative;
 boundary="------------080400090401040405050808"
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

This is a multi-part message in MIME format.
--------------080400090401040405050808
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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


--------------080400090401040405050808
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    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.&nbsp; (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).&nbsp; 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
    <a class="moz-txt-link-freetext" href="http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm">http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm</a>
    in order to get up to the 9.3 level...<br>
    <br>
    <b>The symptom:</b><br>
    <br>
    db# vacuum country_state;<br>
    ERROR:&nbsp; MultiXactId 4800022 has not been created yet -- apparent
    wraparound<br>
    <br>
    db# vacuum country;<br>
    ERROR:&nbsp; MultiXactId 4800023 has not been created yet -- apparent
    wraparound<br>
    <br>
    <br>
    <b>The solution (for small table)</b><br>
    Researching the mailing lists, I found this<br>
<a class="moz-txt-link-freetext" href="http://postgresql.1045698.n5.nabble.com/MultiXactId-error-after-upgrade-to-9-3-4-td5797923.html">http://postgresql.1045698.n5.nabble.com/MultiXactId-error-after-upgrade-to-9-3-4-td5797923.html</a><br>
    <br>
    So, following this advice, I did the following:<br>
    select * from country_state for update \g /tmp/ignore;<br>
    select * from country for update \g /tmp/ignore;<br>
    <br>
    and now vacuum is happy with those tables again.<br>
    <br>
    <br>
    <b>What's not working , still...</b><br>
    <br>
    Unfortunately, getting this same symptom on a 3rd table is getting a
    bit large (102GB, with 28million rows)<br>
    db=# vacuum article;<br>
    ERROR:&nbsp; MultiXactId 4998189 has not been created yet -- apparent
    wraparound<br>
    <br>
    I'm wondering if there is a query which would just return the rows
    which had a "bad t_xmax value"&nbsp; (or if we can even differentiate
    those from correct values). - such that I can only search for those
    with the 'for update' construct.<br>
    <br>
    <br>
    <br>
    Some common information that I've seen provided in the past by
    others:<br>
    <br>
    db=# select txid_current();<br>
    &nbsp;txid_current <br>
    --------------<br>
    &nbsp;&nbsp; 1008772125<br>
    (1 row)<br>
    <br>
    db=# SELECT datname, datfrozenxid FROM pg_database where
    datname='db';<br>
    &nbsp;&nbsp; datname&nbsp;&nbsp; | datfrozenxid <br>
    -------------+--------------<br>
    &nbsp;db |&nbsp;&nbsp;&nbsp; 851079261<br>
    (1 row)<br>
    &nbsp;<br>
    <br>
    db=# show autovacuum_freeze_max_age;<br>
    &nbsp;autovacuum_freeze_max_age <br>
    ---------------------------<br>
    &nbsp;200000000<br>
    (1 row)<br>
    <br>
    db=# show vacuum_freeze_min_age;<br>
    &nbsp;vacuum_freeze_min_age <br>
    -----------------------<br>
    &nbsp;50000000<br>
    (1 row)<br>
    <br>
    db=# show vacuum_freeze_table_age;<br>
    &nbsp;vacuum_freeze_table_age <br>
    -------------------------<br>
    &nbsp;150000000<br>
    (1 row)<br>
    <br>
    <br>
    Thanks in advance for any advice<br>
    Bill<br>
    <br>
  </body>
</html>

--------------080400090401040405050808--