Date: Fri, 10 Oct 2014 13:26:24 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: jim_yates <pg@wg5jim.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: could not access status of transaction pg_multixact issue
Message-ID: <20141010162623.GO7043@eldon.alvh.no-ip.org>
References: <1412797415000-5822268.post@n5.nabble.com>
 <20141008205416.GZ7043@eldon.alvh.no-ip.org>
 <1412863620263-5822376.post@n5.nabble.com>
 <5436A34C.8090306@aklaver.com>
 <20141009151229.GD7043@eldon.alvh.no-ip.org>
 <1412874160409-5822399.post@n5.nabble.com>
 <20141009180022.GE7043@eldon.alvh.no-ip.org>
 <1412885826355-5822449.post@n5.nabble.com>
 <20141009203637.GH7043@eldon.alvh.no-ip.org>
 <1412951821680-5822573.post@n5.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1412951821680-5822573.post@n5.nabble.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

jim_yates wrote:
> Alvaro Herrera-9 wrote
> > jim_yates wrote:
> > 
> >> Then I'm really confused.  
> >> The minimum relminmxid for all the rows in pg_class that have relminmxid
> >> greater then zero is 1.
> >> That's the current value of datminmxid in pg_database.  
> >> 
> >> And the NextMultiXactId from pg_controldump is  303464.
> >> 
> >> So if I use the min value from pg_class then I have some other issue.  
> >> 
> >> Where should I get the new pg_database value from?
> > 
> > I'm deep in another issue which I don't want to page out right now, but
> > try vacuuming the tables that have relminmxid=1 with low values set for
> > vacuum_multixact_freeze_table_age and vacuum_multixact_freeze_min_age,
> > say 100000.  (I think 65536 ought to get you beyond segment
> > pg_multixact/offset/0000, and then that file would be removed.) Since
> > any multixact values below the point at which pg_upgrade ran should be
> > marked "no longer running" through hint bits, there would be no
> > pg_multixact lookups anyway and thus the vacuuming should complete with
> > no errors.
> > 
> > -- 
> > Álvaro Herrera                http://www.2ndQuadrant.com/
> > PostgreSQL Development, 24x7 Support, Training & Services
> 
> I set vacuum_multixact_freeze_table_age and vacuum_multixact_freeze_min_age
> to 100,000 and vacuumed all the tables with a relminmxid='1' and relkind='r'
> using pg_class as the source.
> I still couldn't vacuum or select the original table with the issue. 
> I did solve the problem by dropping the table and restoring from my standby
> server.

It might have proven interesting to look into the actual values related
to the multixact that caused you grief.  It's not clear to me whether
the 187k value you got in the error message came from before the upgrade
or after.  If it's prior to the upgrade, there should have been no
lookup of it; if it was after, the pg_multixact files should have been
there.

I wonder if this is somehow related to this problem:
http://www.postgresql.org/message-id/20140330040029.GY4582@tamriel.snowman.net

> Is there else anything I need to do to prevent being bitten by this bug
> again?

Supposedly it's a one-time thing after the upgrade.

> I still have a value of 1 for datminmxid in pg_database, and the 0000 file
> is still in pg_multixact/members and offsets.

Eventually the datminmxid should advance.   Make sure the minimum
relminmxid is no longer 1.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql