From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew@dunslane.net>
cc: Jeff Davis <pgsql@j-davis.com>, Jeff Davis <jdavis@postgresql.org>,
        pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Trial fix for old cross-version upgrades.
In-reply-to: <684274.1740273302@sss.pgh.pa.us>
References: <E1tlBJt-000LUO-16@gemulon.postgresql.org>
 <003dc9936317ab987faa0242f8e33e1cd2fcaf57.camel@j-davis.com>
 <40c1e76334f2baa747334c8fc513d12b8cb297bf.camel@j-davis.com>
 <3815127.1740189601@sss.pgh.pa.us>
 <5ebc188e731ac2b98d68459ce1a9ef3066981774.camel@j-davis.com>
 <3892121.1740193866@sss.pgh.pa.us>
 <976dcc37-b629-490e-a052-a057477d062f@dunslane.net>
 <301492.1740249291@sss.pgh.pa.us> <684274.1740273302@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Sat, 22 Feb 2025 20:15:02 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <812816.1740277228.1@sss.pgh.pa.us>
Date: Sat, 22 Feb 2025 21:20:28 -0500
Message-ID: <812817.1740277228@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/812817.1740277228%40sss.pgh.pa.us>
Precedence: bulk

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?

			regards, tom lane