Re: PostgreSQL v15.12 fails to perform PG_UPGRADE from v13 and v9 on Windows

On Mon, 2025-04-07 at 17:59 +0300, Avi Uziel wrote:
> On Fri, Apr 4, 2025 at 3:30 PM Manika Singhal <manika(dot)singhal(at)enterprisedb(dot)com> wrote:
> > On Fri, Mar 21, 2025 at 4:43 PM Ben Caspi <benc(at)aidoc(dot)com> wrote:
> > > We have an environment with numerous client Windows machines. The machines have PSQL v9.6/v13.13/v15.6 installed.
> > >
> > > However, running PG_UPGRADE on PSQL v9.6/13.13 to v15.12 has been failing.
> > >
> > > This is the error message:
> > > lc_collate values for database "template1" do not match:  old "English_United States.1252", new "en-US"
> > >
> > > Upon investigation, we came to understand that the PSQL v9.6/v13.13 config contains the following configuration by default:
> > > lc_messages = 'English_United States.1252'
> > > lc_monetary = 'English_United States.1252'
> > > lc_numeric = 'English_United States.1252'
> > > lc_time = 'English_United States.1252'
> > >
> > > But when installing PSQL 15.12 it's changed to:
> > > lc_messages = 'en-US'
> > > lc_monetary = 'en-US'
> > > lc_numeric = 'en-US'
> > > lc_time = 'en-US'
> >
> > This change in the behaviour of the installer is the result of this commit [1].
> >
> >  As 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 significant number of tickets
> > from users after Microsoft made that change (to add non-ascii characters) in their updates.
> >
> > Reading the thread [3], it seems it's probably not recommended to update the pg_database.datcollate
> > or datctype. 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 name as is during initdb run.
> > Will this help?
> >
> > [1]https://github.com/EnterpriseDB/edb-installers/commit/e6404b5194051e20cfc0e7f268a69091e6445a73
> > [2]https://www.postgresql.org/message-id/CA%2BhUKGL5mBN3JQuebAPbX0yxDNtpui04J%2BKSy2F7KBbhLGaJig%40mail.gmail.com
> > [3]https://www.postgresql.org/message-id/2694195.1700072765%40sss.pgh.pa.us
>
> I anticipate that users could face an upgrade issue if they installed a cluster on a Windows
> machine using the default locale before this change and then attempt to upgrade 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 the 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 automatically, but if
the upgrade fails for whatever reason, your original cluster got modified, which doesn't
feel right.

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

3. A minimal solution would be to equip the Windows installer with some guidance for the user
at the point where the locale is chosen. The BCP-47 locale name would be suggested, and
a text could point out "If you plan to upgrade a cluster that was created 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