Re: pg_upgrade < 9.3 -> >=9.3 misses a step around multixacts

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.

  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.


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


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.

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