Re: pg_upgrade bug found!

On Fri, Apr 08, 2011 at 12:16:50PM -0700, Jeff Davis wrote:
> On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote:
> > > 1. Make relfrozenxid go backward to the right value. There is currently
> > > no mechanism to do this without compiling C code into the server,
> > > because (a) VACUUM FREEZE will never move the relfrozenxid backward; and
> > > (b) there is no way to find the oldest xid in a table with a normal
> > > snapshot.
> > 
> > Couldn't you set relfrozenxid and datfrozenxid to txid_current() - 1100000000
> > (the highest possible vacuum_freeze_min_age, plus some slop), then run "SET
> > vacuum_freeze_table_age = 0; VACUUM tbl" on all tables for which you did this?
> > There's no need to set relfrozenxid back to a particular "right" value. 
> 
> That's a good point that we don't need relfrozenxid to really be the
> right value; we just need it to be less than or equal to the right
> value. I don't think you need to mess around with
> vacuum_freeze_table_age though -- that looks like it's taken care of in
> the logic for deciding when to do a full table vacuum.

Actually, I think the only reason to VACUUM at all after hacking relfrozenxid is
to visit every tuple and see whether you need to restore any clog segments from
backup.  Suppose your postgresql.conf overrides vacuum_freeze_table_age to the
maximum of 2B.  If you hacked relfrozenxid and just VACUUMed without modifying
vacuum_freeze_table_age, you wouldn't get a full table scan.  In another ~1B
transactions, you'll get that full-table VACUUM, and it might then discover
missing clog segments.  Though you wouldn't risk any new clog loss in the mean
time, by doing the VACUUM with vacuum_freeze_table_age=0 on each affected table,
you can go away confident that any clog restoration is behind you.

> This has the additional merit that transaction IDs are not needlessly
> removed; therefore leaving some forensic information if there are
> further problems.
> 
> > 
> > Suppose that your next xid at pg_upgrade time was 500M, and it's now 505M.  If
> > you're using the default vacuum_freeze_min_age = 50M, "SET
> > vacuum_freeze_table_age = 0; VACUUM tbl" will only freeze tuples covering 5M
> > transaction ids.
> 
> If the pg_upgrade time was at txid 500M, then the relfrozenxid of the
> toast table will be about 500M. That means you need to get rid of all
> xids less than about 500M (unless you already fixed relfrozenxid,
> perhaps using the process you mention above).
> 
> So if you only freeze tuples less than about 455M (505M - 50M), then
> that is wrong.

Agreed.  If you don't fix relfrozenxid, you can't win much in that example.

> 
> The only difference really is that you don't really need to freeze those
> last 5M transactions since the upgrade happened.

But change the numbers somewhat.  Say you ran pg_upgrade at xid 110M.  Your
TOAST table had relfrozenxid = 100M before pg_upgrade and 110M+epsilon after.
The next xid now sits at 170M.  Without any manual relfrozenxid changes, any
full-table VACUUM will bump the relfrozenxid to 120M.  A VACUUM FREEZE would
freeze tuples covering 70M transactions, while a VACUUM with
vacuum_freeze_table_age = 0 would freeze tuples across only 20M transactions.
An unadorned VACUUM wouldn't even perform a full-table scan.

All that being said, recommending VACUUM FREEZE seems sensibly conservative.

Thanks,
nm