Windows Application Issues | PostgreSQL | REF # 48475607

On Fri, May 3, 2024 at 7:05 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

Deploy windows server 2019 Turkey Build.

...

Postgres.exe crash is observed while installing the application.

Hi,

"Crash" with no details is not a very useful report, but I guess that
this is the Turkey -> Türkiye issue caused by a recent operating
system upgrade, which is apparently now hitting the server edition of
Windows based on the new inflow of bug reports. I think we know
approximately how to fix it, and there are several possible
workarounds for a system that is already in this state, but interested
parties who know and care about the relevant OS need to get involved
to make progress. It's too late for Turkish but ideally we'll be able
to stop this from happening the next time a country changes its name.
I have written everything I know about the issue here:

/messages/by-id/18196-b10f93dfbde3d7db@postgresql.org

Not being a Windows user, I have not been able to take that proposed
fix all the way to the finish line, so all I can do is post what I
know and hope that open source will happen.

On Fri, May 3, 2024 at 11:50 AM Thomas Munro <thomas.munro@gmail.com> wrote:

/messages/by-id/18196-b10f93dfbde3d7db@postgresql.org

And more here:

/messages/by-id/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=2zDbB+SxDs59pv7Fw@mail.gmail.com

Hi Thomas,

Thanks a lot for your previous reply. Still need to double check with you:

You said the Turkey -> Türkiye issue caused by a recent operating system upgrade, you mean this crash is caused by the changes made on Windows side? If that is the case, you prefer to leave the bug there? Any plan for the future?

Thanks!
Haifang

-----Original Message-----
From: Thomas Munro <thomas.munro@gmail.com>
Sent: Thursday, May 2, 2024 4:51 PM
To: Haifang Wang (Centific Technologies Inc) <v-haiwang@microsoft.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

[You don't often get email from thomas.munro@gmail.com. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

On Fri, May 3, 2024 at 7:05 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

Deploy windows server 2019 Turkey Build.

...

Postgres.exe crash is observed while installing the application.

Hi,

"Crash" with no details is not a very useful report, but I guess that this is the Turkey -> Türkiye issue caused by a recent operating system upgrade, which is apparently now hitting the server edition of Windows based on the new inflow of bug reports. I think we know approximately how to fix it, and there are several possible workarounds for a system that is already in this state, but interested parties who know and care about the relevant OS need to get involved to make progress. It's too late for Turkish but ideally we'll be able to stop this from happening the next time a country changes its name.
I have written everything I know about the issue here:

/messages/by-id/18196-b10f93dfbde3d7db@postgresql.org

Not being a Windows user, I have not been able to take that proposed fix all the way to the finish line, so all I can do is post what I know and hope that open source will happen.

Hi Thomas and team,

Some suggestion from our engineers, please take a look and let me know if you have any question:

1. Could you please use _wsetlocale API in case of wide string locale parameter, this is what the recommended way as par Microsoft documentation as well. (Reference: setlocale: https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/setlocale-wsetlocale?view=msvc-170, wsetlocale: https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/setlocale-wsetlocale?view=msvc-170)
2. Additionally you can work around in case you don’t want to change the API to update their mapping table with Turkish_Tur in case of Turkish_Türkiye as you have done for others locale with same issue as well in your code
(reference PostgreSQL Source Code: src/port/win32setlocale.c Source File: https://doxygen.postgresql.org/win32setlocale_8c_source.html, Line number 66-67)

Please let me know if there is any other questions.

Regards!
Haifang

-----Original Message-----
From: Haifang Wang (Centific Technologies Inc)
Sent: Monday, May 13, 2024 11:52 AM
To: Thomas Munro <thomas.munro@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: RE: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

Hi Thomas,

Thanks a lot for your previous reply. Still need to double check with you:

You said the Turkey -> Türkiye issue caused by a recent operating system upgrade, you mean this crash is caused by the changes made on Windows side? If that is the case, you prefer to leave the bug there? Any plan for the future?

Thanks!
Haifang

-----Original Message-----
From: Thomas Munro <mailto:thomas.munro@gmail.com>
Sent: Thursday, May 2, 2024 4:51 PM
To: Haifang Wang (Centific Technologies Inc) <mailto:v-haiwang@microsoft.com>
Cc: mailto:pgsql-bugs@lists.postgresql.org
Subject: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

[You don't often get email from mailto:thomas.munro@gmail.com. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

On Fri, May 3, 2024 at 7:05 AM Haifang Wang (Centific Technologies
Inc) <mailto:v-haiwang@microsoft.com> wrote:

Deploy windows server 2019 Turkey Build.

...

Postgres.exe crash is observed while installing the application.

Hi,

"Crash" with no details is not a very useful report, but I guess that this is the Turkey -> Türkiye issue caused by a recent operating system upgrade, which is apparently now hitting the server edition of Windows based on the new inflow of bug reports. I think we know approximately how to fix it, and there are several possible workarounds for a system that is already in this state, but interested parties who know and care about the relevant OS need to get involved to make progress. It's too late for Turkish but ideally we'll be able to stop this from happening the next time a country changes its name.
I have written everything I know about the issue here:

/messages/by-id/18196-b10f93dfbde3d7db@postgresql.org

Not being a Windows user, I have not been able to take that proposed fix all the way to the finish line, so all I can do is post what I know and hope that open source will happen.

On Tue, May 14, 2024 at 6:51 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

You said the Turkey -> Türkiye issue caused by a recent operating system upgrade, you mean this crash is caused by the changes made on Windows side? If that is the case, you prefer to leave the bug there? Any plan for the future?

First, let me restate the problem:

When you create a database cluster (= a PostgreSQL instance) with
"initdb", unless you request a default locale with --locale, initdb
uses setlocale("") to query the system/user default locale. It then
records that string in postgresql.conf, and also in the pg_database
catalog. On POSIX systems, that captures something like "tr-TR.UTF-8"
or similar. On Windows, that captures something like
"Turkish_Turkey.1254". Later, PostgreSQL uses newlocale() or
setlocale() functions to access that locale again. In rare cases
where a country changes its name, a Windows update *renames* the
locale, and then those calls fail, because the old name is not
recognised anymore.

I proposed a partial solution that should help avoid the problem in future:

I think that initdb should instead call GetUserDefaultLocaleName() to
discover the user account's default locale, because it returns BCP47
names like "tr-TR". It is probably less likely for a country or
language to change its ISO code (but not impossible[1]https://learn.microsoft.com/en-us/globalization/locale/standard-locale-names), than for the
English-language names of them to change. Even if you reject this
idea because technically they can both change, there are other reasons
why we should not be storing this "display"-style names anywhere,
including that PostgreSQL needs to store them in a place where the
encoding must be ASCII, which Türkiye is not. And finally, the
Windows manual explicitly warns us about this[2]https://learn.microsoft.com/en-us/cpp/c-runtime-library/locale-names-languages-and-country-region-strings?view=msvc-160: "We don't recommend
this form for locale strings embedded in code or serialized to
storage: These strings are more likely to be changed by an operating
system update than the locale name form."

(Note: the BCP47 support in Windows did not exist when that PostgreSQL
code was written, so it did what it had to at the time.)

There are further problems to resolve:

1. We don't know if we should put encodings (AKA codepages?) on the
end of those strings or not. There is some confusion about what
exactly it does, and how it interacts with the "ACP". I'd be worried
that if you don't put the endings on, perhaps it can change under your
feet. (I suspect that part of the discussion on that other thread
took some wrong turns, based on citext_utf8 results that were actually
probably misleading.)
2. If we do decide to put the encoding suffixes on, which encoding
should we be suggesting? It appears from anecdotal reports that most
PostgreSQL-on-Windows users are stuck in the past, using the old
pre-UTF-8 language-specific encodings. Should we be encouraging UTF-8
use by default, if we can? Maybe that is a separate question.

Then there is the practical question of what to do with an
already-broken system. One idea would be to introduce a "locale
remapping" file, pgdata/pg_locale.map where you can write things
like "Turkish_Turkey.1254"="Turkish_Türkiye.1254".

I think there might be a GUI tool that allows you to duplicate,
rename, etc locales in Windows, so you can re-create the old name. I
believe that is how some people have fixed their broken databases. I
don't know if there is a good reference/blog/article on that, that we
should be pointing people towards if they show up with broken systems.

Patches, testing, research are welcome! Even though I put forward that
BCP47 idea, it was based on reading the manual, so the "unresolved"
questions may in fact be very easy to resolve by people who actually
use/know Windows. Even if I had been gung-ho about committing
that in 16 without feedback from Windows users, it would have been too
late to help Turkish users with existing databases.

[1]: https://learn.microsoft.com/en-us/globalization/locale/standard-locale-names
[2]: https://learn.microsoft.com/en-us/cpp/c-runtime-library/locale-names-languages-and-country-region-strings?view=msvc-160

Hi Thomas,

Thanks for your reply. But I'm not sure if there is any miscommunication. Let me make it clear again.

I’m a program manager in the Windows App Assure ISV Outreach Team at Microsoft. We work with Microsoft’s test organization to notify developers when issues have been identified in their applications. The issue I reported in this mail is an issue we found in our testing and I believe it also impact a lot of end users. Like you mentioned previously, it is caused by recent operating system upgrade.

The solution I shared in my last email was suggested our engineers. It would be great is you could use _wsetlocale API. this is what the recommended way as par Microsoft documentation as well. (Reference: setlocale wsetlocale: https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/setlocale-wsetlocale?view=msvc-170

If you don’t want to change the API to update their mapping table with Turkish_Tur in case of Turkish_Türkiye as you have done for others locale with same issue as well in your code (reference PostgreSQL Source Code: src/port/win32setlocale.c Source File: https://doxygen.postgresql.org/win32setlocale_8c_source.html, Line number 66-67).

Please let me know if there is any misunderstanding.

Thanks!
Haifang

-----Original Message-----
From: Thomas Munro <thomas.munro@gmail.com>
Sent: Monday, May 13, 2024 3:12 PM
To: Haifang Wang (Centific Technologies Inc) <v-haiwang@microsoft.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

On Tue, May 14, 2024 at 6:51 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

You said the Turkey -> Türkiye issue caused by a recent operating system upgrade, you mean this crash is caused by the changes made on Windows side? If that is the case, you prefer to leave the bug there? Any plan for the future?

First, let me restate the problem:

When you create a database cluster (= a PostgreSQL instance) with "initdb", unless you request a default locale with --locale, initdb uses setlocale("") to query the system/user default locale. It then records that string in postgresql.conf, and also in the pg_database catalog. On POSIX systems, that captures something like "tr-TR.UTF-8"
or similar. On Windows, that captures something like "Turkish_Turkey.1254". Later, PostgreSQL uses newlocale() or
setlocale() functions to access that locale again. In rare cases where a country changes its name, a Windows update *renames* the locale, and then those calls fail, because the old name is not recognised anymore.

I proposed a partial solution that should help avoid the problem in future:

I think that initdb should instead call GetUserDefaultLocaleName() to discover the user account's default locale, because it returns BCP47 names like "tr-TR". It is probably less likely for a country or language to change its ISO code (but not impossible[1]https://learn.microsoft.com/en-us/globalization/locale/standard-locale-names), than for the English-language names of them to change. Even if you reject this idea because technically they can both change, there are other reasons why we should not be storing this "display"-style names anywhere, including that PostgreSQL needs to store them in a place where the encoding must be ASCII, which Türkiye is not. And finally, the Windows manual explicitly warns us about this[2]https://learn.microsoft.com/en-us/cpp/c-runtime-library/locale-names-languages-and-country-region-strings?view=msvc-160: "We don't recommend this form for locale strings embedded in code or serialized to
storage: These strings are more likely to be changed by an operating system update than the locale name form."

(Note: the BCP47 support in Windows did not exist when that PostgreSQL code was written, so it did what it had to at the time.)

There are further problems to resolve:

1. We don't know if we should put encodings (AKA codepages?) on the end of those strings or not. There is some confusion about what exactly it does, and how it interacts with the "ACP". I'd be worried that if you don't put the endings on, perhaps it can change under your feet. (I suspect that part of the discussion on that other thread took some wrong turns, based on citext_utf8 results that were actually probably misleading.) 2. If we do decide to put the encoding suffixes on, which encoding should we be suggesting? It appears from anecdotal reports that most PostgreSQL-on-Windows users are stuck in the past, using the old
pre-UTF-8 language-specific encodings. Should we be encouraging UTF-8 use by default, if we can? Maybe that is a separate question.

Then there is the practical question of what to do with an already-broken system. One idea would be to introduce a "locale remapping" file, pgdata/pg_locale.map where you can write things like "Turkish_Turkey.1254"="Turkish_Türkiye.1254".

I think there might be a GUI tool that allows you to duplicate, rename, etc locales in Windows, so you can re-create the old name. I believe that is how some people have fixed their broken databases. I don't know if there is a good reference/blog/article on that, that we should be pointing people towards if they show up with broken systems.

Patches, testing, research are welcome! Even though I put forward that
BCP47 idea, it was based on reading the manual, so the "unresolved"
questions may in fact be very easy to resolve by people who actually use/know Windows. Even if I had been gung-ho about committing that in 16 without feedback from Windows users, it would have been too late to help Turkish users with existing databases.

[1]: https://learn.microsoft.com/en-us/globalization/locale/standard-locale-names
[2]: https://learn.microsoft.com/en-us/cpp/c-runtime-library/locale-names-languages-and-country-region-strings?view=msvc-160

On Tue, May 14, 2024 at 10:27 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

Thanks for your reply. But I'm not sure if there is any miscommunication. Let me make it clear again.

(Sorry, it seems our emails crossed.)

I’m a program manager in the Windows App Assure ISV Outreach Team at Microsoft. We work with Microsoft’s test organization to notify developers when issues have been identified in their applications. The issue I reported in this mail is an issue we found in our testing and I believe it also impact a lot of end users. Like you mentioned previously, it is caused by recent operating system upgrade.

Thanks for doing that, and yes, it affects a lot of users, and this is
not the first time. It is still possible for it to be the last...

The solution I shared in my last email was suggested our engineers. It would be great is you could use _wsetlocale API. this is what the recommended way as par Microsoft documentation as well. (Reference: setlocale wsetlocale: https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/setlocale-wsetlocale?view=msvc-170

I don't understand. Can you explain why _wsetlocale() is better than
setlocale()? They behave identically except one takes wide
characters, which doesn't seem to solve any problem we have.

If you don’t want to change the API to update their mapping table with Turkish_Tur in case of Turkish_Türkiye as you have done for others locale with same issue as well in your code (reference PostgreSQL Source Code: src/port/win32setlocale.c Source File: https://doxygen.postgresql.org/win32setlocale_8c_source.html, Line number 66-67).

Yeah, OK we could put more kludges into win32setlocale.c. I don't
mind committing a patch like that if it addresses the issue. I am not
in a position to confirm that myself... what we need is someone who
works with Windows to write the patch and test it across that upgrade.
Longer term I'm looking for something better than that though, because
it doesn't address the root cause (need for stable identifiers), and
will only ever allow us to fix problems with the old unstable names
*after* users complain that their database is dead, 3-6 months after
in fact due to release cycles. I think a dynamic mapping file might
be better? (Maybe win32locale.c should be able to read that kludge
table from a file that you can give it with an environment variable,
or something like that?)

Thomas Munro <thomas.munro@gmail.com> writes:

Longer term I'm looking for something better than that though, because
it doesn't address the root cause (need for stable identifiers), and
will only ever allow us to fix problems with the old unstable names
*after* users complain that their database is dead, 3-6 months after
in fact due to release cycles. I think a dynamic mapping file might
be better? (Maybe win32locale.c should be able to read that kludge
table from a file that you can give it with an environment variable,
or something like that?)

+1 for the long-term solution being more-stable locale identifiers.
However, we should try to build something that will let users get
out of these situations with the existing identifiers, so I like
your idea of a plain-text mapping file for Windows locale names.
I don't think an environment variable is necessary; just define
a fixed name "$PGDATA/locale_map.txt" or such. If that file
exists, just read it and map the pg_database field values with it.

Maybe this shouldn't even be Windows-specific? Are there any
cases where it'd save people's bacon on other platforms?

regards, tom lane

Just curious. Why a plain text file rather than a system table?

On Mon, May 13, 2024, 18:07 Tom Lane <tgl@sss.pgh.pa.us> wrote:

John McKown <john.archie.mckown@gmail.com> writes:

Just curious. Why a plain text file rather than a system table?

Because you'd have no way to update such a table, if you can't
start the database or connect to it. So that approach isn't
suitable for people whose database has been broken by one of
these system updates. (This is largely the same reason why,
eg, postgresql.conf isn't a table.)

regards, tom lane

On Tue, May 14, 2024 at 11:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

+1 for the long-term solution being more-stable locale identifiers.
However, we should try to build something that will let users get
out of these situations with the existing identifiers, so I like
your idea of a plain-text mapping file for Windows locale names.
I don't think an environment variable is necessary; just define
a fixed name "$PGDATA/locale_map.txt" or such. If that file
exists, just read it and map the pg_database field values with it.

OK, I tried that, first draft attached (with my standard proviso that
I don't do Windows, I just know that this passes CI and that the code
works the way I intended on my local Unix system if extracted into a
little harness). With this, you could in theory create a file
PGDATA/win32setlocale.map containing:

c Turkish_Turkey.1254=Turkish_Türkiye.1254

... or perhaps more likely:

c Turkish_Turkey.1254=tr-TR.1254

I also absorbed the pre-existing kludge table into the new system by
default (though they got a bit shorter 'cause I invented some
wildcards). Some problems came up while wondering how to fit Türkiye
into the defaults, and how to back-patch:

1. In the back-branches, we claim to support ancient Windows releases
as far back as "Windows 2000 SP4" (!), which obviously aren't getting
the Windows updates, so I guess "Turkish_Türkiye.1254" will fail there
and generally before Windows 10. And even if you exclude the
extremities of our support window somehow (how?), modern systems might
not have applied the update yet (IIUC they *have* to at some point
under the new world order, so there is a defined window of version
skew these days).

2. It's generally a terrible idea to be using "ü" in a locale name.
FWIW I assume setlocale() actually accepts and returns names encoded
in the current ACP ("active codepage", system-wide changeable setting
that controls char↔wchar_t conversion in system APIs), so the encoding
of that file (and the built-in default table) would need to match that
to work, as coded. Perhaps it would be possible to make the mapping
file UTF-8 and transform that to ACP! But it feels a bit too loopy
for me, and on the PostgreSQL side it is undefined/illegal whatever
you choose in PostgreSQL due to being accessed from different
databases which are using potentially different encodings that are
only required to be a superset of ASCII. Avoid.

3. Therefore you'd probably want to prefer "tr-TR.1254" as the
replacement string. But what is the oldest Windows release that can
understand a BCP47 code like that?

4. Conversely, on modern systems, I'm still not entirely sure that
"tr-TR.1254" is exactly the same thing as "Turkish-XXX.1254" and that
it's OK to put ".1254" on the end like that. Is it, and is it? I
don't mean just "does it mean Turkish?", I mean "does it give exactly
the same answer for every conceivable pair of strings when compared
with strcoll_l(), and likewise for the ctype-based functions like
towlower() et al".

If the answers are not in our favour, I guess we could leave the
default behaviour unchanged, and let people set up a text file as
shown above to fix their database if they want, but that's also not
very nice and kinda weird (helping hypothetical users of museum-grade
systems by leaving real users' systems broken).

If the answer to 4 is yes, yes then we could also push ahead with the
plan to make initdb pick BCP47 names by default in PG18 (or even 17).

Maybe this shouldn't even be Windows-specific? Are there any
cases where it'd save people's bacon on other platforms?

Good question. Sometimes ISO code go away or countries split etc, so
it's no like POSIX locale names are set in stone under all
circumstances. But on Unixen it's all just files in practice, you can
always just symlink them, move them around, compile them yourself from
sources, etc, if you really have to, so I think I'd rather contain the
crazy in win32*.c.

Thanks for your questions, Thomas and Tom. + @Vishwa to help with technical questions.

-----Original Message-----
From: Thomas Munro <thomas.munro@gmail.com>
Sent: Monday, May 13, 2024 11:38 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Haifang Wang (Centific Technologies Inc) <v-haiwang@microsoft.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

On Tue, May 14, 2024 at 11:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

+1 for the long-term solution being more-stable locale identifiers.
However, we should try to build something that will let users get out
of these situations with the existing identifiers, so I like your idea
of a plain-text mapping file for Windows locale names.
I don't think an environment variable is necessary; just define a
fixed name "$PGDATA/locale_map.txt" or such. If that file exists,
just read it and map the pg_database field values with it.

OK, I tried that, first draft attached (with my standard proviso that I don't do Windows, I just know that this passes CI and that the code works the way I intended on my local Unix system if extracted into a little harness). With this, you could in theory create a file PGDATA/win32setlocale.map containing:

c Turkish_Turkey.1254=Turkish_Türkiye.1254

... or perhaps more likely:

c Turkish_Turkey.1254=tr-TR.1254

I also absorbed the pre-existing kludge table into the new system by default (though they got a bit shorter 'cause I invented some wildcards). Some problems came up while wondering how to fit Türkiye into the defaults, and how to back-patch:

1. In the back-branches, we claim to support ancient Windows releases as far back as "Windows 2000 SP4" (!), which obviously aren't getting the Windows updates, so I guess "Turkish_Türkiye.1254" will fail there and generally before Windows 10. And even if you exclude the extremities of our support window somehow (how?), modern systems might not have applied the update yet (IIUC they *have* to at some point under the new world order, so there is a defined window of version skew these days).

2. It's generally a terrible idea to be using "ü" in a locale name.
FWIW I assume setlocale() actually accepts and returns names encoded in the current ACP ("active codepage", system-wide changeable setting that controls char↔wchar_t conversion in system APIs), so the encoding of that file (and the built-in default table) would need to match that to work, as coded. Perhaps it would be possible to make the mapping file UTF-8 and transform that to ACP! But it feels a bit too loopy for me, and on the PostgreSQL side it is undefined/illegal whatever you choose in PostgreSQL due to being accessed from different databases which are using potentially different encodings that are only required to be a superset of ASCII. Avoid.

3. Therefore you'd probably want to prefer "tr-TR.1254" as the replacement string. But what is the oldest Windows release that can understand a BCP47 code like that?

4. Conversely, on modern systems, I'm still not entirely sure that "tr-TR.1254" is exactly the same thing as "Turkish-XXX.1254" and that it's OK to put ".1254" on the end like that. Is it, and is it? I don't mean just "does it mean Turkish?", I mean "does it give exactly the same answer for every conceivable pair of strings when compared with strcoll_l(), and likewise for the ctype-based functions like
towlower() et al".

If the answers are not in our favour, I guess we could leave the default behaviour unchanged, and let people set up a text file as shown above to fix their database if they want, but that's also not very nice and kinda weird (helping hypothetical users of museum-grade systems by leaving real users' systems broken).

If the answer to 4 is yes, yes then we could also push ahead with the plan to make initdb pick BCP47 names by default in PG18 (or even 17).

Maybe this shouldn't even be Windows-specific? Are there any cases
where it'd save people's bacon on other platforms?

Good question. Sometimes ISO code go away or countries split etc, so it's no like POSIX locale names are set in stone under all circumstances. But on Unixen it's all just files in practice, you can always just symlink them, move them around, compile them yourself from sources, etc, if you really have to, so I think I'd rather contain the crazy in win32*.c.

Hi Thomas and Tom,

Please let us know your question or concern about the bug we discuss in this mail.

Thanks!
Haifang

-----Original Message-----
From: Haifang Wang (Centific Technologies Inc)
Sent: Wednesday, May 15, 2024 4:47 PM
To: Thomas Munro <thomas.munro@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Vishwa Deepak <Vishwa.Deepak@microsoft.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: RE: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

Thanks for your questions, Thomas and Tom. + @Vishwa to help with technical questions.

-----Original Message-----
From: Thomas Munro <thomas.munro@gmail.com>
Sent: Monday, May 13, 2024 11:38 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Haifang Wang (Centific Technologies Inc) <v-haiwang@microsoft.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

On Tue, May 14, 2024 at 11:07 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

+1 for the long-term solution being more-stable locale identifiers.
However, we should try to build something that will let users get out
of these situations with the existing identifiers, so I like your idea
of a plain-text mapping file for Windows locale names.
I don't think an environment variable is necessary; just define a
fixed name "$PGDATA/locale_map.txt" or such. If that file exists,
just read it and map the pg_database field values with it.

OK, I tried that, first draft attached (with my standard proviso that I don't do Windows, I just know that this passes CI and that the code works the way I intended on my local Unix system if extracted into a little harness). With this, you could in theory create a file PGDATA/win32setlocale.map containing:

c Turkish_Turkey.1254=Turkish_Türkiye.1254

... or perhaps more likely:

c Turkish_Turkey.1254=tr-TR.1254

I also absorbed the pre-existing kludge table into the new system by default (though they got a bit shorter 'cause I invented some wildcards). Some problems came up while wondering how to fit Türkiye into the defaults, and how to back-patch:

1. In the back-branches, we claim to support ancient Windows releases as far back as "Windows 2000 SP4" (!), which obviously aren't getting the Windows updates, so I guess "Turkish_Türkiye.1254" will fail there and generally before Windows 10. And even if you exclude the extremities of our support window somehow (how?), modern systems might not have applied the update yet (IIUC they *have* to at some point under the new world order, so there is a defined window of version skew these days).

2. It's generally a terrible idea to be using "ü" in a locale name.
FWIW I assume setlocale() actually accepts and returns names encoded in the current ACP ("active codepage", system-wide changeable setting that controls char↔wchar_t conversion in system APIs), so the encoding of that file (and the built-in default table) would need to match that to work, as coded. Perhaps it would be possible to make the mapping file UTF-8 and transform that to ACP! But it feels a bit too loopy for me, and on the PostgreSQL side it is undefined/illegal whatever you choose in PostgreSQL due to being accessed from different databases which are using potentially different encodings that are only required to be a superset of ASCII. Avoid.

3. Therefore you'd probably want to prefer "tr-TR.1254" as the replacement string. But what is the oldest Windows release that can understand a BCP47 code like that?

4. Conversely, on modern systems, I'm still not entirely sure that "tr-TR.1254" is exactly the same thing as "Turkish-XXX.1254" and that it's OK to put ".1254" on the end like that. Is it, and is it? I don't mean just "does it mean Turkish?", I mean "does it give exactly the same answer for every conceivable pair of strings when compared with strcoll_l(), and likewise for the ctype-based functions like
towlower() et al".

If the answers are not in our favour, I guess we could leave the default behaviour unchanged, and let people set up a text file as shown above to fix their database if they want, but that's also not very nice and kinda weird (helping hypothetical users of museum-grade systems by leaving real users' systems broken).

If the answer to 4 is yes, yes then we could also push ahead with the plan to make initdb pick BCP47 names by default in PG18 (or even 17).

Maybe this shouldn't even be Windows-specific? Are there any cases
where it'd save people's bacon on other platforms?

Good question. Sometimes ISO code go away or countries split etc, so it's no like POSIX locale names are set in stone under all circumstances. But on Unixen it's all just files in practice, you can always just symlink them, move them around, compile them yourself from sources, etc, if you really have to, so I think I'd rather contain the crazy in win32*.c.

On Sat, May 18, 2024 at 10:25 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

Please let us know your question or concern about the bug we discuss in this mail.

Thanks for your help with this! Did I miss an email in between? Here
are some unanswered questions that seem to be stopping progress:

1. What is the oldest Windows release that can understand the "new"
BCP47 locale names, like "tr-TR" or "tr-TR.1452"?
2. If we translate to BCP47 automatically, should we put the ".1452"
on the end? What does it mean exactly? What does it mean if you
don't put it there?
3. Do the new BCP47 locale names give *exactly* the same results for
strcoll() and tolower() etc, as the old "Turkish*" style names?

With answers to those questions we might be able to ship some nice
built-in translations to get users out of this jam. If there are
issues on those points, we might have to face some questions about
what the encoding is of the "Turkish_Türkiye.1254" string itself,
which is tricky for us for technical reasons, among other problems...
not sure.

Thomas Munro <thomas.munro@gmail.com> writes:

With answers to those questions we might be able to ship some nice
built-in translations to get users out of this jam. If there are
issues on those points, we might have to face some questions about
what the encoding is of the "Turkish_Türkiye.1254" string itself,
which is tricky for us for technical reasons, among other problems...
not sure.

TBH, my idea of how this should go was to *not* ship any built-in
translations, or indeed any translation file at all (so I didn't
like your moving some existing hacks into that file). If we
approach it like that, then individual users who hit this problem
are responsible for creating their own translation file, which
will automatically use whatever is the locally-appropriate
encoding. Sure, it's less transparent for affected users, but
it will work for them which other approaches might not; and
evidence so far is that there's not a huge number of affected
users.

regards, tom lane

On Sat, May 18, 2024 at 5:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thomas Munro <thomas.munro@gmail.com> writes:

With answers to those questions we might be able to ship some nice
built-in translations to get users out of this jam. If there are
issues on those points, we might have to face some questions about
what the encoding is of the "Turkish_Türkiye.1254" string itself,
which is tricky for us for technical reasons, among other problems...
not sure.

TBH, my idea of how this should go was to *not* ship any built-in
translations, or indeed any translation file at all (so I didn't
like your moving some existing hacks into that file). If we
approach it like that, then individual users who hit this problem
are responsible for creating their own translation file, which
will automatically use whatever is the locally-appropriate
encoding. Sure, it's less transparent for affected users, but
it will work for them which other approaches might not; and
evidence so far is that there's not a huge number of affected
users.

Hmm, yeah I guess we could just ship a patch like what I posted
already, and let users figure out what works. I would still like to
know the answer to those questions so we can offer good advice. If
the answers are all yes then I think we can say "the file is in
encoded in ASCII; use wildcards to deal with any legacy non-ASCII
locale names on the left, and use BCP47 names on the right" and begin
expunging the bad old names from our universe.

Hi Thomas,

What questions do you have? Could you please list them clearly so that Vishwa could help to answer?

NOTE: this email is plain text format, not easy to read. And also the previous email content usually is not included in the latest email, we only could see the most latest one.

Thanks!
Haifang

-----Original Message-----
From: Thomas Munro <thomas.munro@gmail.com>
Sent: Friday, May 17, 2024 10:57 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Haifang Wang (Centific Technologies Inc) <v-haiwang@microsoft.com>; Vishwa Deepak <Vishwa.Deepak@microsoft.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

On Sat, May 18, 2024 at 5:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thomas Munro <thomas.munro@gmail.com> writes:

With answers to those questions we might be able to ship some nice
built-in translations to get users out of this jam. If there are
issues on those points, we might have to face some questions about
what the encoding is of the "Turkish_Türkiye.1254" string itself,
which is tricky for us for technical reasons, among other problems...
not sure.

TBH, my idea of how this should go was to *not* ship any built-in
translations, or indeed any translation file at all (so I didn't like
your moving some existing hacks into that file). If we approach it
like that, then individual users who hit this problem are responsible
for creating their own translation file, which will automatically use
whatever is the locally-appropriate encoding. Sure, it's less
transparent for affected users, but it will work for them which other
approaches might not; and evidence so far is that there's not a huge
number of affected users.

Hmm, yeah I guess we could just ship a patch like what I posted already, and let users figure out what works. I would still like to know the answer to those questions so we can offer good advice. If the answers are all yes then I think we can say "the file is in encoded in ASCII; use wildcards to deal with any legacy non-ASCII locale names on the left, and use BCP47 names on the right" and begin expunging the bad old names from our universe.

On Tue, May 21, 2024 at 8:17 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

What questions do you have? Could you please list them clearly so that Vishwa could help to answer?

I already did, twice, but perhaps Vishwa or others can't see the whole
thread, so here is this whole thread in our project email archive:

/messages/by-id/PH8PR21MB3902F334A3174C54058F792CE5182@PH8PR21MB3902.namprd21.prod.outlook.com

But let me ask the questions again, with some motivation/reason I want
to know in parentheses:

1. What is the oldest Windows release that can understand the "new"
BCP47 locale names, like "tr-TR" or "tr-TR.1452"? (Some PostgreSQL
versions, for example PostgreSQL 12, are expected to run on old
versions of Windows from long before Windows 10, so we might have to
consider this. However, if we go with Tom's idea that we do nothing
by default but just allow users to supply their own optional mapping
file, then this question becomes unimportant, users can figure out for
themselves whether it works, and presumably only 10+ got the update
that renamed Turkey to Türkiye. [And in reality, I hope/expect that
no one really does run old out-of-support OSes, because that's crazy,
but I'm not allowed to assume...])

2. If we translate to BCP47 locale names like "tr-TR" automatically,
should we put the ".1452" on the end? What does it mean exactly?
What does it mean if you don't put it there? (I could guess that if
you don't put it on, the encoding in "char"-based functions is the
"ACP". What I really want to know is, can it be different from the
"ACP", and if it is, which functions does it affect? For example if
the ACP is 1521 and I call _tolower_l() giving it a locale_t that I
opened with "en-US.UTF-8", what happens? I am sure this is a simple
question but we are not Windows programmers, you are the first person
to show up offering to investigate, and I personally found the docs a
bit light on the topic.)

3. Do the new BCP47 locale names give *exactly* the same results for
strcoll() and tolower() etc, as the old "Turkish*" style names? (In
other words, is it *exactly the same code and driving data*, just
using different labels? Or is it a new locale implementation that
could differ arbitrarily in behaviour? If the answer is yes, it's
just a new naming scheme, then life will be much much simpler for our
users, but if not, then indexes might be corrupted if we tell people
to switch to the new BCP47 names, and so we'd better know about that,
so we can adjust our advice to users.)

Hi Vishwa,

Could you please help with the questions below?

Thanks!
Haifang

-----Original Message-----
From: Thomas Munro <thomas.munro@gmail.com>
Sent: Monday, May 20, 2024 1:57 PM
To: Haifang Wang (Centific Technologies Inc) <v-haiwang@microsoft.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vishwa Deepak <Vishwa.Deepak@microsoft.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607

On Tue, May 21, 2024 at 8:17 AM Haifang Wang (Centific Technologies
Inc) <v-haiwang@microsoft.com> wrote:

What questions do you have? Could you please list them clearly so that Vishwa could help to answer?

I already did, twice, but perhaps Vishwa or others can't see the whole thread, so here is this whole thread in our project email archive:

/messages/by-id/PH8PR21MB3902F334A3174C54058F792CE5182@PH8PR21MB3902.namprd21.prod.outlook.com

But let me ask the questions again, with some motivation/reason I want to know in parentheses:

1. What is the oldest Windows release that can understand the "new"
BCP47 locale names, like "tr-TR" or "tr-TR.1452"? (Some PostgreSQL versions, for example PostgreSQL 12, are expected to run on old versions of Windows from long before Windows 10, so we might have to consider this. However, if we go with Tom's idea that we do nothing by default but just allow users to supply their own optional mapping file, then this question becomes unimportant, users can figure out for themselves whether it works, and presumably only 10+ got the update that renamed Turkey to Türkiye. [And in reality, I hope/expect that no one really does run old out-of-support OSes, because that's crazy, but I'm not allowed to assume...])

2. If we translate to BCP47 locale names like "tr-TR" automatically, should we put the ".1452" on the end? What does it mean exactly?
What does it mean if you don't put it there? (I could guess that if you don't put it on, the encoding in "char"-based functions is the "ACP". What I really want to know is, can it be different from the "ACP", and if it is, which functions does it affect? For example if the ACP is 1521 and I call _tolower_l() giving it a locale_t that I opened with "en-US.UTF-8", what happens? I am sure this is a simple question but we are not Windows programmers, you are the first person to show up offering to investigate, and I personally found the docs a bit light on the topic.)

3. Do the new BCP47 locale names give *exactly* the same results for
strcoll() and tolower() etc, as the old "Turkish*" style names? (In other words, is it *exactly the same code and driving data*, just using different labels? Or is it a new locale implementation that could differ arbitrarily in behaviour? If the answer is yes, it's just a new naming scheme, then life will be much much simpler for our users, but if not, then indexes might be corrupted if we tell people to switch to the new BCP47 names, and so we'd better know about that, so we can adjust our advice to users.)