MIME-Version: 1.0
References: <CAH2i4yfBEYmOzsvsT=D+ySpLpsEW2O1VfUwx9Ckd69du67YY_w@mail.gmail.com>
 <44b44ece-dce6-4b4f-b751-8787a5a071e0@aklaver.com> <CA+hUKGJA4pGEeZtfUUEuQmNNO7x16wZ+RWOeXP+tq3Hf4iu8oQ@mail.gmail.com>
 <CANFyU95Le+trZbtB1D4Y1FdUu4y_OJLxvJMi1Zb9WOWuf8KpqA@mail.gmail.com>
 <CAH2i4ycX3Q79uv431CxZ9dcyM5jkL=sJ8Z8rbNaNuKSK=Bprfg@mail.gmail.com>
 <CANFyU95q_WDM+utY3Mnrf1pwgASN3ZR1r6Fb+Zs7kXsPEDQQ2A@mail.gmail.com>
 <CANFyU94yoUCYovEZ9By2cpRbHsziRqndfPZ5qiTRD24CM57cEg@mail.gmail.com>
 <CANFyU959RhJvRAY7=8vgN-1f+qKFuK80uFvrR1kMoQMQqzKPQg@mail.gmail.com>
 <CA+hUKGK_LqmzEbR6YjyuQ9eAaecMfogv1e+E-dZVw=qULXtyGQ@mail.gmail.com>
 <CANFyU95vLi3K1FTZO82kYTAGX3B7TPkbWe8LAecaQtndH+EC9Q@mail.gmail.com> <20240806114405.qdwrok7ppfangowo@hjp.at>
In-Reply-To: <20240806114405.qdwrok7ppfangowo@hjp.at>
From: Thomas Munro <thomas.munro@gmail.com>
Date: Wed, 7 Aug 2024 09:08:44 +1200
Message-ID: <CA+hUKGLqXSbk9FrfWVvA-fXP7p1+r6WLnY9Xr_EpYasOaBf9dA@mail.gmail.com>
Subject: Re: Windows installation problem at post-install step
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BhUKGLqXSbk9FrfWVvA-fXP7p1%2Br6WLnY9Xr_EpYasOaBf9dA%40mail.gmail.com>
Precedence: bulk

On Tue, Aug 6, 2024 at 11:44=E2=80=AFPM Peter J. Holzer <hjp-pgsql@hjp.at> =
wrote:
> I assume that "1254" here is the code page.
> But you specified --encoding=3DUTF-8 above, so your default locale uses a
> different encoding than the template databases. I would expect that to
> cause problems if the template databases contain any charecters where
> the encodings differ (such as "=C3=BC" in the locale name).

It's weird, but on Windows, PostgreSQL allows UTF-8 encoding with any
locale, and thus apparent contradictions:

    /* See notes in createdb() to understand these tests */
    if (!(locale_enc =3D=3D user_enc ||
          locale_enc =3D=3D PG_SQL_ASCII ||
          locale_enc =3D=3D -1 ||
#ifdef WIN32
          user_enc =3D=3D PG_UTF8 ||
#endif
          user_enc =3D=3D PG_SQL_ASCII))
    {
        pg_log_error("encoding mismatch");

... and createdb's comments say that is acceptable because:

 * 3. selected encoding is UTF8 and platform is win32. This is because
 * UTF8 is a pseudo codepage that is supported in all locales since it's
 * converted to UTF16 before being used.

At the time PostgreSQL was ported to Windows, UTF-8 was not a
supported encoding in "char"-based system interfaces like strcoll_l(),
and the port had to convert to "wchar_t" interfaces and call (in that
example) wcscoll_l().  On modern Windows it is, and there are two
locale names, with and without ".UTF-8" suffix (cf. glibc systems that
have "en_US" and "en_US.UTF-8" where the suffix-less version uses
whatever traditional encoding was used for that language before UTF-8
ate the world).

If we were doing the Windows port today, we'd probably not have that
special case for Windows, and we wouldn't have the wchar_t
conversions.  Then I think we'd allow only:

--locale=3Dtr-TR (defaults to --encoding=3DWIN1254)
--locale=3Dtr-TR --encoding=3DWIN1254
--locale-tr-TR.UTF-8
--locale=3Dtr-TR.UTF-8 --encoding=3DUTF-8

If we come up with an automated (or even manual but documented) way to
perform the "Turkish_T=C3=BCrkiye.1254" -> "tr-TR" upgrade as Dave was
suggesting upthread, we'll probably want to be careful to tidy up
these contradictory settings.  For example I guess that American
databases initialised by EDB's installer must be using
--locale=3D"English_United States.1252" and --encoding=3DUTF-8, and should
be changed to "en-US.UTF-8", while those initialised by letting
initdb.exe pick the encoding must be using --locale=3D"English_United
States.1252" and --encoding=3DWIN1252 (implicit) and should be changed
to "en-US" to match the WIN1252 encoding.

Only if we did that update would we be able to consider removing the
extra UTF-16 conversions that are happening very frequently inside
PostgreSQL code, which is a waste of CPU cycles and programmer sanity.
(But that's all just speculation from studying the locale code -- I've
never really used Windows.)