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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +