Message-ID: <4f3aca16-5b6e-4231-a13f-ef1c730a8a07@dunslane.net>
Date: Sat, 22 Feb 2025 20:08:19 -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>
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: <301492.1740249291@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/4f3aca16-5b6e-4231-a13f-ef1c730a8a07%40dunslane.net>
Precedence: bulk


On 2025-02-22 Sa 1:34 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> On 2025-02-21 Fr 10:11 PM, Tom Lane wrote:
>>> ... It seems there is
>>> something different between what TestUpgradeXversion.pm is doing
>>> and what 002_pg_upgrade.pl is doing.  No clue what, although it
>>> does look like an additional round of analyze'ing has added more
>>> stats than were there before.
> Hah!  Looking at the script with less bleary eyes, I see it does
> this after pg_upgrade:
>
>      if (-e "$installdir/analyze_new_cluster.sh")
>      {
>          system( "cd $installdir && sh ./analyze_new_cluster.sh "
>                . qq{> "$upgrade_loc/$oversion-analyse.log" 2>&1 });
>          return if $?;
>      }
>      else
>      {
>          system( qq{"$installdir/bin/vacuumdb" --all --analyze-only }
>                . qq{> "$upgrade_loc/$oversion-analyse.log" 2>&1 });
>          return if $?;
>      }
>
> So there's our extra round of ANALYZE right there.  I diked out the
> vacuumdb call and things are working much better.  It seems to pass
> for branches back through 9.3, and upgrade from 9.2 has only some
> diffs for relallvisible (see attached).  We still need to figure out
> why that is, but a quick-n-dirty patch could just be to make the dump
> comparison logic ignore relallvisible diffs.
>
> We might want to make this vacuumdb invocation dependent on version,
> but I could also see just removing it entirely.
>
>> Here's what I have so far:
>> . for HEAD/18 disable running the analyze script / vacuumdb --analyze.
>> . turn off autovacuum on the old and upgraded database.
>> . reverse the order of testing so we do newest first
> Check.
>
>> What I'm thinking of doing is running all the eligible upgrades rather
>> than stopping on the first failure.
> Seems like possibly wasted work --- I'd be content with
> newest-to-oldest.
>
> 			


OK, went with that. crake is getting a failure at 9.6 like you saw with 
9.2, but things are a whole lot better than they were.


I have updated drongo and fairywren with the new code too, so they 
should also improve before long.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com