Re: VACUUM FULL results in ERROR: integer out of range

I wrote:
> What's evidently happening is that since the row with c0 = 2147483647
> is still potentially live to some onlooker transaction, the index
> rebuild forced by VACUUM FULL is trying to create an index entry for
> it. I imagine that your original example with a concurrent database
> drop/create is likewise causing a transaction to be open during the
> relevant window.

> Now, what's curious is that the CREATE INDEX itself didn't fail likewise.
> Apparently, we have more-careful analysis of live vs. dead rows during
> the initial index creation than we do during a forced rebuild, because
> somehow CREATE INDEX is deciding that it needn't make an index entry
> for that row, even though it was exactly as live-to-somebody at that
> point as it was during the VACUUM FULL.

Ah, here's the explanation (in HEAD this is in
heapam_index_build_range_scan):

case HEAPTUPLE_RECENTLY_DEAD:

/*
* If tuple is recently deleted then we must index it
* anyway to preserve MVCC semantics. (Pre-existing
* transactions could try to use the index after we finish
* building it, and may need to see such tuples.)
*
* However, if it was HOT-updated then we must only index
* the live tuple at the end of the HOT-chain. Since this
* breaks semantics for pre-existing snapshots, mark the
* index as unusable for them.
* ...

The tuple in question *was* HOT-updated, since there were no indexes
in existence at the time of the UPDATE that would prevent that.
So we more or less accidentally avoid generating the index-entry-
that-would-fail, at the price that the index is not immediately
usable by transactions with old snapshots.

VACUUM FULL preserves RECENTLY_DEAD tuples, as it must, but does not
preserve HOT-update tuple relationships. So it needs to generate
an index entry for this row, and kaboom.

The only way to make the two cases behave identically would be for
VACUUM FULL to preserve HOT-update tuple relationships. Even if
we wished to do that (unlikely, since it'd be a complicated and
hard-to-test code path), doing that would have its own downsides.
To name one, we'd be forced into putting all the copies of tuples
in a HOT chain into the same output page, resulting in worse packing.
Having the output indexes be not-immediately-usable would not be
really nice either.

In short, there's nothing I particularly want to change here. The
HOT-induced behavior is a little surprising, but it's adjacent to
behaviors that are absolutely required by the MVCC semantic model.

regards, tom lane