Re: pg_upgrade bug found!

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.

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.

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

Regards,
Jeff Davis