From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Davis <pgsql@j-davis.com>
cc: Jeff Davis <jdavis@postgresql.org>, Andrew Dunstan <andrew@dunslane.net>,
        pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Trial fix for old cross-version upgrades.
In-reply-to: <40c1e76334f2baa747334c8fc513d12b8cb297bf.camel@j-davis.com>
References: <E1tlBJt-000LUO-16@gemulon.postgresql.org>
 <003dc9936317ab987faa0242f8e33e1cd2fcaf57.camel@j-davis.com>
 <40c1e76334f2baa747334c8fc513d12b8cb297bf.camel@j-davis.com>
Comments: In-reply-to Jeff Davis <pgsql@j-davis.com>
	message dated "Fri, 21 Feb 2025 16:57:43 -0800"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <3815005.1740189505.0@sss.pgh.pa.us>
Date: Fri, 21 Feb 2025 21:00:01 -0500
Message-ID: <3815127.1740189601@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3815127.1740189601%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3815005.1740189505.1@sss.pgh.pa.us>

Jeff Davis <pgsql@j-davis.com> writes:
> The version that I committed had the following change to
> 002_pg_upgrade.pl:

>   # Stabilize stats before pg_dumpall.
>   $oldnode->append_conf('postgresql.conf', 'autovacuum = off');
>   $oldnode->restart;

>   ...

>   $newnode->append_conf('postgresql.conf', 'autovacuum = off');

> I think we need a similar change in the buildfarm client's
> TestUpgradeXversion.pm? Is -hackers the right place to discuss that?

I think we might indeed want that, but it doesn't seem to be the
explanation for the buildfarm failures, because the diffs look
to be consistent across runs which you'd not expect from
autovacuum-driven changes.  I suspect that the problem is that
pg_dump is interpreting old-version stats in some way that doesn't
match up with what we get from restoring the dump.

I did experiment with the attached very-quick-n-dirty patch, which
should succeed in suppressing autovacuum in both the old and new
versions if I understand the code correctly (which I might well not).
It made no difference at all in the dump diffs ...

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="bf-lock-stats.patch"; charset="us-ascii"
Content-ID: <3815005.1740189505.2@sss.pgh.pa.us>
Content-Description: bf-lock-stats.patch
Content-Transfer-Encoding: quoted-printable

--- PGBuild/Modules/TestUpgradeXversion.pm~	2024-11-02 01:47:21.000000000 =
-0400
+++ PGBuild/Modules/TestUpgradeXversion.pm	2025-02-21 20:50:35.705564091 -=
0500
@@ -419,7 +419,7 @@ sub test_upgrade    ## no critic (Subrou
 	# run in which it was set up, which will be gone by now, so we repoint
 	# it to the current run's tmpdir.
 	# listen_addresses will be set correctly and requires no adjustment.
-	if (!$using_localhost)
+	# In any case, disable autovacuum to prevent stats changing under us.
 	{
 		my $tdir =3D $tmpdir;
 		$tdir =3D~ s!\\!/!g;
@@ -431,7 +431,9 @@ sub test_upgrade    ## no critic (Subrou
 		$param =3D "unix_socket_directory"
 		  if $oversion ne 'HEAD' && $oversion lt 'REL9_3_STABLE';
 		print $opgconf "\n# Configuration added by buildfarm client\n\n";
-		print $opgconf "$param =3D '$tdir'\n";
+		print $opgconf "$param =3D '$tdir'\n"
+		    if (!$using_localhost);
+		print $opgconf "autovacuum =3D off\n";
 		close($opgconf);
 	}
 =

@@ -507,7 +509,7 @@ sub test_upgrade    ## no critic (Subrou
 		  . qq{> "$upgrade_loc/$oversion-initdb.log" 2>&1});
 	return if $?;
 =

-	unless ($using_localhost)
+	# Again, adjust connection location and disable autovacuum.
 	{
 		open(my $pgconf, ">>", "$installdir/$oversion-upgrade/postgresql.conf")
 		  || die "opening $installdir/$oversion-upgrade/postgresql.conf: $!";
@@ -515,8 +517,12 @@ sub test_upgrade    ## no critic (Subrou
 		$tmp_param =3D "unix_socket_directory"
 		  if $this_branch ne 'HEAD' && $this_branch lt 'REL9_3_STABLE';
 		print $pgconf "\n# Configuration added by buildfarm client\n\n";
-		print $pgconf "listen_addresses =3D ''\n";
-		print $pgconf "$tmp_param =3D '$tmpdir'\n";
+		unless ($using_localhost)
+		{
+		    print $pgconf "listen_addresses =3D ''\n";
+		    print $pgconf "$tmp_param =3D '$tmpdir'\n";
+		}
+		print $pgconf "autovacuum =3D off\n";
 		close($pgconf);
 	}
 =


------- =_aaaaaaaaaa0--