Re: pg_upgrade < 9.3 -> >=9.3 misses a step around multixacts - Mailing list pgsql-bugs

On Tue, Jul  1, 2014 at 03:01:06PM -0400, Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> > What I am not sure about is how to set values from pre-9.3 clusters, and
> > whether 9.3 pg_upgrade upgrades from pre-9.3 clusters are a problem.
> > Are users who used pg_upgrade to go to 9.4 beta in trouble?
> >
> > I also have no way to know what value to use for pre-9.3 clusters --- I
> > used controldata.chkpnt_nxtmulti in pg_upgrade (because the value was
> > accessible), but 0 in pg_dump/pg_dumpall, like we already do for frozen
> > xid values, but that usage is for major versions that pg_upgrade doesn't
> > support, so it might be the wrong default.  I am thinking that should be
> > using controldata.chkpnt_nxtmulti, which exists back to 8.4, but I have
> > no access to that value from pg_dump.  In fact, the patch as it exists
> > is flawed because it uses controldata.chkpnt_nxtmulti to set values from
> > set_frozenxids(), because the value is accessible, but uses zero in
> > pg_dump and pg_dumpall for pre-9.3 old clusters.  :-(
>
> Bruce and I discussed this on IM and I think we have reached a
> conclusion on what needs to be done:
>
> * When upgrading from 9.2 or older, all tables need to have relminmxid
>   set to oldestMulti.  However, since pg_dump --binary-upgrade cannot
>   extract useful values from the catalog, we will need to have the
>   schema load create all tables with relminmxid=0.  A subsequent UPDATE
>   will fix the values.

OK, the updated attached patch does this.  It repurposes
set_frozenxids().

>   In this case, each database' datminmxid value is going to be set to
>   pg_control's oldestMulti.
>
>   If I recall correctly, oldestMulti is computed as nextMulti-1.

We don't have oldestMulti in pg_controldata in pre-9.3, so we have to
use nextMulti-1.

> * When upgrading from 9.3 or newer, the relminmxid values from the old
>   cluster must be preserved.  Also, datminmxid is going to be preserved.

Yes, that was already in the patch.

> Finally, there is the question of what to do if the database has already
> been upgraded and thus the tables are all at relminmxid=1.  As far as I
> can tell, if the original value of nextMulti was below 2^31, there
> should be no issue because vacuuming would advance the value normally.
> If the original value was beyond that point, then vacuum would have been
> bleating all along about the wraparound point.  In this case, I think it
> should be enough the UPDATE the pg_class values to the current
> oldestMulti value from pg_control, but I haven't tested this.

Well, we are already having users run a query for the 9.3.X minor
version upgrade to optionally remove the 0000 file.  Is there something
else they should run to test for this?  We certainly could check for
files >= 8000, but I am not sure that is sufficient.  We would then need
them to somehow update all the database/relation minmxid fields, and I
am not even sure what value we should set it to.  Is that something we
want to publish?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +