On 2025-02-22 Sa 9:20 PM, Tom Lane wrote:
I wrote:
Furthermore, it can't be a coincidence that the four tables we are
seeing relallvisible diffs for are exactly the four tables in the
regression database that have hash indexes.
Oh! If the source version is <= 9.6, old_9_6_invalidate_hash_indexes()
generates a script "reindex_hash.sql" to reindex all hash indexes.
And TestUpgradeXversion faithfully executes that, *before* making
the new-database comparison dump.
I added some instrumentation and confirmed that these tables'
relallvisible values match between the pre-dump state on the old
database and the immediately-post-upgrade state on the new database.
It's definitely reindex_hash.sql that's changing them. This doesn't
in itself explain why 9.3-9.5 don't show the problem, but I noticed
something interesting: it's the pre-dump state that is out of line
in 9.2 and 9.6. All the other cases show relallvisible that's a
couple pages less than relpages, but in those two branches we
start from a state that claims these rels are fully all-visible,
and then seemingly REINDEX discovers that that's not so.
What I'm guessing is that the variance is due to changes in
vacuum/analyze's heuristics for updating pg_class.relallvisible
after a partial scan of the table.
Anyway, we know where the culprit is, and I'm not sure that
explaining the differences in behavior of long-dead branches
is an exciting use of time.
Question is, what to do about this? We probably want to continue to
test that reindex_hash.sql does something sane, so just deleting that
step won't do. I experimented with moving it from immediately before
the pg_dumpall of the new installation to immediately after, but that
didn't work at all: the indexes are marked invalid and so pg_dump just
doesn't dump them, so we still end up with a diff in the dump output.
I'm not really seeing a better answer than hacking the comparison
rules to ignore the relallvisible difference for these tables.
That's darn ugly, and I suspect the implementation will be messy,
but do we have another way?
Having slept on it I can't see anything better. It's only for very old branches, and nothing is really going wrong here, so ignoring the difference seems quite reasonable.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
