Re: pg_upgrade bug found!

On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote:
> So I think we have four possible approaches to correct databases:
>
> 1) SELECT * to set the hint bits
> 2) VACUUM to set the hint bits
> 3) VACUUM FREEZE to remove the old xids
> 4) some complicated function
>
> I don't like #4, and I think I can script #2 and #3 in psql by using COPY
> to create a VACUUM script and then run it with \i. #1 is easy in a DO
> block with PL/pgSQL.

The only one that sounds very reasonable to me is #3. If there are any
xids older than the relfrozenxid, we need to get rid of them. If there
is some reason that doesn't work, I suppose we can consider the
alternatives. But I don't like the hint-bit-setting approach much.

What if the xmax is really a transaction that got an exclusive lock on
the tuple, rather than actually deleting it? Are you sure that a SELECT
(or even a normal VACUUM) would get rid of that xid, or might something
still try to look it up in the clog later?

Not only that, but hint-bit-setting is not WAL-logged, so you'd really
have to do a checkpoint afterward.

Regards,
Jeff Davis