Message-ID: <a5eefdf4ff9d38c1eb4d4a1e4e4984b386a71218.camel@cybertec.at>
Subject: Re: PostgreSQL v15.12 fails to perform PG_UPGRADE from v13 and v9
 on Windows
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Avi Uziel <avi.uziel@aidoc.com>, Manika Singhal
	 <manika.singhal@enterprisedb.com>
Cc: Ben Caspi <benc@aidoc.com>, pgsql-bugs@lists.postgresql.org, Liran
 Amrani	 <lirana@aidoc.com>, Shahar Amram <shahara@aidoc.com>, Sandeep
 Thakkar	 <sandeep.thakkar@enterprisedb.com>, thomas.munro@gmail.com,
 tgl@sss.pgh.pa.us
Date: Tue, 08 Apr 2025 07:50:56 +0200
In-Reply-To: 
 <CAMjTNpvFL8P3Y_gdoY_K-f8RbPd3KLNF_8v_Gf5u4HkJ=g3gGQ@mail.gmail.com>
References: 
	<CABv1Mx6uBG1OJX3+FHewqCoGtGuQqsLniB47WUrrt8sd1PDK1Q@mail.gmail.com>
	 <CAB1XC2KYtPk1h-nopnq3nQMNAG8knA419WozrzSPnkT9LyOfNA@mail.gmail.com>
	 <CAMjTNpvFL8P3Y_gdoY_K-f8RbPd3KLNF_8v_Gf5u4HkJ=g3gGQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a5eefdf4ff9d38c1eb4d4a1e4e4984b386a71218.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-04-07 at 17:59 +0300, Avi Uziel wrote:
> On Fri, Apr 4, 2025 at 3:30=E2=80=AFPM Manika Singhal <manika.singhal@ent=
erprisedb.com> wrote:
> > On Fri, Mar 21, 2025 at 4:43=E2=80=AFPM Ben Caspi <benc@aidoc.com> wrot=
e:
> > > We have an environment with numerous client Windows machines. The mac=
hines have PSQL v9.6/v13.13/v15.6 installed.
> > >=20
> > > However, running PG_UPGRADE on PSQL v9.6/13.13 to v15.12 has been fai=
ling.
> > >=20
> > > This is the error message:
> > > lc_collate values for database "template1" do not match: =C2=A0old "E=
nglish_United States.1252", new "en-US"
> > >=20
> > > Upon investigation, we came to understand that the PSQL v9.6/v13.13 c=
onfig contains the following configuration by default:
> > > lc_messages =3D 'English_United States.1252'
> > > lc_monetary =3D 'English_United States.1252'
> > > lc_numeric =3D 'English_United States.1252'
> > > lc_time =3D 'English_United States.1252'
> > >=20
> > > But when installing PSQL 15.12 it's changed to:
> > > lc_messages =3D 'en-US'
> > > lc_monetary =3D 'en-US'
> > > lc_numeric =3D 'en-US'
> > > lc_time =3D 'en-US'
> >=20
> > This change in the behaviour of the installer is the result of this com=
mit [1].
> >=20
> > =C2=A0As a part of this change, the installer would convert the chosen =
locale to its corresponding BCP-47
> > [2] code name before passing it on to initdb.exe. This was helpful for =
users where the locale name
> > contained non-ascii characters and initdb would fail. We received a sig=
nificant number of tickets
> > from users after Microsoft made that change (to add non-ascii character=
s) in their updates.
> >=20
> > Reading the thread [3], it seems it's probably not recommended to updat=
e the=C2=A0pg_database.datcollate
> > or=C2=A0datctype. I am thinking if it might help if installer converted=
 the chosen locale name to BCP-47 only
> > when it contains non-ascii characters, otherwise, it should use the nam=
e as is during initdb run.
> > Will this help?
> >=20
> > [1]https://github.com/EnterpriseDB/edb-installers/commit/e6404b5194051e=
20cfc0e7f268a69091e6445a73
> > [2]https://www.postgresql.org/message-id/CA%2BhUKGL5mBN3JQuebAPbX0yxDNt=
pui04J%2BKSy2F7KBbhLGaJig%40mail.gmail.com
> > [3]https://www.postgresql.org/message-id/2694195.1700072765%40sss.pgh.p=
a.us
>
> I anticipate that users could face an upgrade issue if they installed a c=
luster on a Windows
> machine using the default locale before this change and then attempt to u=
pgrade to a release
> that includes this commit.

Yes, I think this will cause more trouble.

I think that we should stick with BCP-47 locale names as much as possible. =
 The problem with
the long locale names is not only non-ASCII characters, but that Microsoft =
keeps changing these
names, and PostgreSQL persists them in the catalog, which causes trouble if=
 Windows is upgraded.

I can see three potential ways to deal with that:

1. Only when creating a cluster for upgrade, use the locale names that the =
old cluster uses.

   That is difficult, because it requires to connect to that cluster, as th=
e information is
   only in catalog tables.  Also, if there are several clusters, which one =
to use?

2. Before upgrading a cluster, update the catalog tables of the old cluster=
 with the
   corresponding BCP-47 name.

   That would be a good way into a world with only BCP-47 locale names.

   On the one hand, it would be convenient to have pg_upgrade do this autom=
atically, but if
   the upgrade fails for whatever reason, your original cluster got modifie=
d, which doesn't
   feel right.

   Perhaps the Windows binaries could come with an extra tool for such a ch=
ange, and perhaps
   the installer could suggest running it before an upgrade.

3. A minimal solution would be to equip the Windows installer with some gui=
dance for the user
   at the point where the locale is chosen.  The BCP-47 locale name would b=
e suggested, and
   a text could point out "If you plan to upgrade a cluster that was create=
d with long locale
   names, please select the same locale names for the new cluster".

I would like #2, but I cannot tell how easy or feasible it would be.

Yours,
Laurenz Albe