Re: [GENERAL] pg_upgrade problem

Alvaro Herrera wrote:
> > > I don't understand the pg_upgrade code here.  It is setting the
> > > datfrozenxid and relfrozenxid values to the latest checkpoint's NextXID,
> > > 
> > >         /* set pg_class.relfrozenxid */
> > >         PQclear(executeQueryOrDie(conn,
> > >                                   "UPDATE   pg_catalog.pg_class "
> > >                                   "SET  relfrozenxid = '%u' "
> > >         /* only heap and TOAST are vacuumed */
> > >                                   "WHERE    relkind IN ('r', 't')",
> > >                                   old_cluster.controldata.chkpnt_nxtxid));
> > > 
> > > but I don't see why this is safe.  I mean, surely the previous
> > > vacuum might have been a lot earlier than that.  Are these values reset
> > > to more correct values (i.e. older ones) later somehow?  My question is,
> > > why isn't the new cluster completely screwed?
> > 
> > Have you looked at my pg_upgrade presentation?
> > 
> >     http://momjian.us/main/presentations/features.html#pg_upgrade
> 
> I just did, but it doesn't explain this in much detail.  (In any case I
> don't think we should be relying in a PDF presentation to explain the
> inner pg_upgrade details.  I think we should rely more on the
> IMPLEMENTATION file rather than your PDF ... amusingly that file doesn't
> mention the frozenxids.)
> 
> > This query happens after we have done a VACUUM FREEEZE on an empty
> > cluster.
> 
> Oh, so it only affects the databases that initdb created, right?
> The other ones are not even created yet.

Right.

> > pg_dump --binary-upgrade will dump out the proper relfrozen xids for
> > every object that gets its file system files copied or linked.
> 
> Okay.  I assume that between the moment you copy the pg_clog files from
> the old server, and the moment you do the UPDATEs on pg_class and
> pg_database, there is no chance for vacuum to run and remove clog
> segments.

Right, we disable it, and had a long discussion about it.  We actually
start the server with:
       "-c autovacuum=off -c autovacuum_freeze_max_age=2000000000",

> Still, it seems to me that this coding makes Min(datfrozenxid) to go
> backwards, and that's bad news.

Yes, it is odd, but I don't see another option.  Remember the problem
with xid wrap-around --- we really are defining two different xid eras,
and have to freeze to make that possible.

> > > I wonder if pg_upgrade shouldn't be doing the conservative thing here,
> > > which AFAICT would be to set all frozenxid values as furthest in the
> > > past as possible (without causing a shutdown-due-to-wraparound, and
> > > maybe without causing autovacuum to enter emergency mode either).
> > 
> > I already get complaints about requiring an "analyze" run after the
> > upgrade --- this would make it much worse.  In fact I have to look into
> > upgrading optimizer statistics someday.
> 
> Why would it make it worse at all?  It doesn't look to me like it
> wouldn't affect in any way.  The only thing it does, is tell the system
> to keep clog segments around.

It will cause excessive vacuum freezing to happen on startup, I assume.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +