agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: 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
Date: Fri, 10 Oct 2014 13:26:24 -0300
Message-ID: <20141010162623.GO7043@eldon.alvh.no-ip.org> (raw)
In-Reply-To: <1412951821680-5822573.post@n5.nabble.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (16+ messages)
Message-ID: <20141010162623.GO7043@eldon.alvh.no-ip.org>
Permalink: ../20141010162623.GO7043@eldon.alvh.no-ip.org/
Also on: postgresql.org/message-id/20141010162623.GO7043@eldon.alvh.no-ip.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alvherre@2ndquadrant.com, pg@wg5jim.net
Subject: Re: could not access status of transaction pg_multixact issue
In-Reply-To: <20141010162623.GO7043@eldon.alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox