Content-Type: multipart/alternative;
 boundary="------------y7nZ3dydvdgcnkR8JbVBz8oq"
Message-ID: <10bd5e38-c8e1-4162-8dd7-ef65e71170b4@dunslane.net>
Date: Sun, 23 Feb 2025 08:24:08 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pgsql: Trial fix for old cross-version upgrades.
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Davis <pgsql@j-davis.com>, Jeff Davis <jdavis@postgresql.org>,
 pgsql-committers@lists.postgresql.org
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>
 <812817.1740277228@sss.pgh.pa.us>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAmwQTAQgARQIbAwIX
 gAIZAQULCQgHAgMiAgEGFQoJCAsCBBYCAwECHgcWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUC
 ZFlxxwUJGVGAbgAKCRCZ+n/LWfw7gXikB/9ZdcUy6CTBFIIuL/bVsc1eLEW/gJBjJBF6HxNY
 xgEkAgXAp4Lg4A5U+QB9GouFr7+GYxF0BU4hzoGhNPUWltxnHdMWP8nC/38LAqgMi8L/bbsm
 HW5YPBdWYaAZAPJQVfOAgjTbRUb26KSprpyrrJKW0ZmrZfjhNPcQ72jpWzoPLQqx2X6B0fru
 1jq+cBh8lb6r1mJTim1T3JIn+F/v5VpdQS+EL8xqsHkfzKjIPsW3CIXpkypSk6saA55Rkkbl
 26AW8ftPVB0Q6Lnn6FLt9CP0MGNixBQ55yq8r1K+nCBvCCjvQjM8RDm0UUum0WNl+ifQgTLO
 E8TWEnwVtkBf+3QWzsBNBE7KWFkBCADRnOM0FCzsYW6jtncg+dWIagjUZpvaClmqn/sJluLa
 Q3v1VXMQJzYs3eC1gh386W+XBwLRpDj3jzH81lX+p73Re3d3oJW7X+ffsxuzu5ZVdMUkqBYo
 nkAbKxr6gyJ12F/+JkUVzLcoTN+d/7YsQvUVi7NaKH8mJgjz112O4fUe3p9wfAaFa0RXHc5S
 GPzRTYRRlv/XZBIho4J2tkZOnteZJZ+GbxQVlINt6fd8P6al3MWOvpP/ExJPguEfjOsO6Njy
 xjo3WfpD4lHMOR/Oc3/8mScEF84rF2jXbsFgelWnbPWAvXY+pD0dXOFRkagGmC/viwBDqq5b
 5tk76kKmUbZxABEBAAHCwHwEGAEIACYCGwwWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUCZFlx
 5wUJGVGAjgAKCRCZ+n/LWfw7gf+iB/4g8CPY5jihf5r/8EsoIGe2H+dpVmpPF8YGBzTIvCz/
 fQoOq8AX/pE76QEuFnFZWfjw+wgBXgCVmkox2Eflkk6z4ND3pcwGZ6CfCxTQCDk/dij+2DQ4
 6bmDCy/sBgcbz9mTpoLC11HLoPae6YN9nBNQRZDcEFEu54OaVOqlIdbA6m+POIBCXZdHOFc0
 WoDTgxHRzC1jgQNidyd6tKqcsVJs0dzF0oKTmFFmUAqTdJO12LBuNA1rlqrR3EtpYk8B/wtS
 5dIMD7Q8hwQpL+4C6GNpb6ZKnPkLi47pDOLhz2qBrqN+rqUEsT3YnExYpzj5yOBi+FlmV1Hw
 49QYe1sn2ZPs
In-Reply-To: <812817.1740277228@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/10bd5e38-c8e1-4162-8dd7-ef65e71170b4%40dunslane.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------y7nZ3dydvdgcnkR8JbVBz8oq
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


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

--------------y7nZ3dydvdgcnkR8JbVBz8oq
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 2025-02-22 Sa 9:20 PM, Tom Lane
      wrote:<br>
    </div>
    <blockquote type="cite" cite="mid:812817.1740277228@sss.pgh.pa.us">
      <pre wrap="" class="moz-quote-pre">I wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="" class="moz-quote-pre">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.
</pre>
      </blockquote>
      <pre wrap="" class="moz-quote-pre">
Oh!  If the source version is &lt;= 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?

			</pre>
    </blockquote>
    <p><br>
    </p>
    <p><span style="white-space: pre-wrap">
</span></p>
    <p><span style="white-space: pre-wrap">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.</span></p>
    <p><span style="white-space: pre-wrap">
</span></p>
    <p><span style="white-space: pre-wrap">cheers</span></p>
    <p><span style="white-space: pre-wrap">
</span></p>
    <p><span style="white-space: pre-wrap">andrew
</span></p>
    <p><span style="white-space: pre-wrap">
</span></p>
    <pre class="moz-signature"
    signature-switch-id="d0437855-2267-4610-80b3-83167ec45b0b" cols="72">--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
  </body>
</html>

--------------y7nZ3dydvdgcnkR8JbVBz8oq--