Subject: Re: Windows installation problem at post-install step

From: Thomas Munro <[email-masked]>
Date: Tue, 6 Aug 2024 17:26:25 +1200

On Mon, Aug 5, 2024 at 8:50 PM Sandeep Thakkar
<[email-masked]> wrote:
> This issue is seen only on v16 and not the back branches (tested on 15 and 14) and also confirmed by @Ertan Küçükoglu at https://github.com/EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442

Does that mean you can reproduce the problem with initdb.exe directly
in a shell?  That is, remove the EDB installer from the picture and
compare v15 and v16 with the exact command line options that
initcluster.vbs is using, or perhaps just:

initdb.exe --locale="Turkish,Türkiye" --encoding=UTF-8 -D pgdata

. o O (Why does that locale name have a comma?)  If v15 works and v16
breaks, perhaps you could try comparing the output with the attached
patch?  It will give a hex dump of the contents of the locale name at
various points in the program, to see if/where it was corrupted, which
might also be a bit less confusing than looking at script output via
email (I don't even know how many onion layers of transcoding are
involved...)
From b97fe5a55e50a447d41a439412922ffe3f7e168b Mon Sep 17 00:00:00 2001
From: Thomas Munro <[email-masked]>
Date: Tue, 6 Aug 2024 16:06:29 +1200
Subject: [PATCH 1/3] xxx debug

---
 src/bin/initdb/initdb.c | 34 +++++++++++++++++++++++++++++++++-
 1 file changed, 33 insertions(+), 1 deletion(-)

diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 458dc1137a3..abe5983ab16 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -336,6 +336,33 @@ do { \
 		output_failed = true, output_errno = errno; \
 } while (0)
 
+static void
+xxx_debug(const char *prefix, const char *s)
+{
+	const unsigned char *us;
+
+	if (s == NULL)
+	{
+		printf("XXX debug: %s  = NULL\n", prefix);
+		return;
+	}
+
+	printf("XXX debug raw: %s  = \"%s\"\n", prefix, s);
+	printf("XXX debug hex: %s  = { ", prefix);
+	for (us = (const unsigned char *) s; *us; us++)
+		printf("%02x ", *us);
+	printf("}\n");
+	printf("XXX debug txt: %s  = { ", prefix);
+	for (us = (const unsigned char *) s; *us; us++)
+	{
+		if (*us >= 0x20 && *us < 0x80)
+			printf("%c  ", *us);
+		else
+			printf("?  ");
+	}
+	printf("}\n");
+}
+
 /*
  * Escape single quotes and backslashes, suitably for insertions into
  * configuration files or SQL E'' strings.
@@ -2369,8 +2396,10 @@ setlocales(void)
 	 * canonicalize locale names, and obtain any missing values from our
 	 * current environment
 	 */
+	xxx_debug("setlocales lc_ctype", lc_ctype);
 	check_locale_name(LC_CTYPE, lc_ctype, &canonname);
 	lc_ctype = canonname;
+	xxx_debug("setlocales cannonname", lc_ctype);
 	check_locale_name(LC_COLLATE, lc_collate, &canonname);
 	lc_collate = canonname;
 	check_locale_name(LC_NUMERIC, lc_numeric, &canonname);
@@ -2597,7 +2626,10 @@ setup_locale_encoding(void)
 		strcmp(lc_ctype, lc_monetary) == 0 &&
 		strcmp(lc_ctype, lc_messages) == 0 &&
 		(!icu_locale || strcmp(lc_ctype, icu_locale) == 0))
+	{
+		xxx_debug("setup_locale_encoding", lc_ctype);
 		printf(_("The database cluster will be initialized with locale \"%s\".\n"), lc_ctype);
+	}
 	else
 	{
 		printf(_("The database cluster will be initialized with this locale configuration:\n"));
@@ -3056,7 +3088,6 @@ initialize_data_directory(void)
 	check_ok();
 }
 
-
 int
 main(int argc, char *argv[])
 {
@@ -3214,6 +3245,7 @@ main(int argc, char *argv[])
 				break;
 			case 1:
 				locale = pg_strdup(optarg);
+				xxx_debug("getopt optarg", locale);
 				break;
 			case 2:
 				lc_collate = pg_strdup(optarg);
-- 
2.46.0

---

From: Sandeep Thakkar <[email-masked]>
Date: Tue, 6 Aug 2024 16:06:51 +0530

On Tue, Aug 6, 2024 at 10:57 AM Thomas Munro <[email-masked]> wrote:

> > This issue is seen only on v16 and not the back branches (tested on 15
> and 14) and also confirmed by @Ertan Küçükoglu at
> https://github.com/EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442
>
> yes, here is the output:

> c:\Program Files\PostgreSQL\16\bin>initdb.exe --encoding=UTF-8 -A
> scram-sha-256 -U postgres -D "c:\Program Files\PostgreSQL\16\data" --locale
> "Turkish,Türkiye" -W
> The files belonging to this database system will be owned by user
> "sandeep".
> This user must also own the server process.
>
> The database cluster will be initialized with locale
> "Turkish_Türkiye.1254".
> The default text search configuration will be set to "turkish".
>
> Data page checksums are disabled.
>
> Enter new superuser password:
> Enter it again:
>
> fixing permissions on existing directory c:/Program
> Files/PostgreSQL/16/data ... ok
> creating subdirectories ... ok
> selecting dynamic shared memory implementation ... windows
> selecting default max_connections ... 100
> selecting default shared_buffers ... 128MB
> selecting default time zone ... UTC
> creating configuration files ... ok
> running bootstrap script ... ok
> performing post-bootstrap initialization ... child process was terminated
> by exception 0xC0000409
> initdb: removing contents of data directory "c:/Program
> Files/PostgreSQL/16/data"
>

> email (I don't even know how many onion layers of transcoding are

here is the output:
v15:

c:\Program Files\PostgreSQL\15\bin>initdb.exe --encoding=UTF-8 -A
> scram-sha-256 -U postgres -D "c:\Program Files\PostgreSQL\15\data" --locale
> "Turkish,Türkiye" -W
> XXX debug raw: getopt optarg  = "Turkish,Türkiye"
> XXX debug hex: getopt optarg  = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69
> 79 65 }
> XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i  y
>  e  }
> XXX debug raw: getopt optarg  = "Turkish,Türkiye"
> XXX debug hex: getopt optarg  = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69
> 79 65 }
> XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i  y
>  e  }
> The files belonging to this database system will be owned by user
> "sandeep".
> This user must also own the server process.
>
> XXX debug raw: setlocales lc_ctype  = "Turkish,Türkiye"
> XXX debug hex: setlocales lc_ctype  = { 54 75 72 6b 69 73 68 2c 54 fc 72
> 6b 69 79 65 }
> XXX debug txt: setlocales lc_ctype  = { T  u  r  k  i  s  h  ,  T  ?  r  k
>  i  y  e  }
> XXX debug raw: setlocales cannonname  = "Turkish_Türkiye.1254"
> XXX debug hex: setlocales cannonname  = { 54 75 72 6b 69 73 68 5f 54 fc 72
> 6b 69 79 65 2e 31 32 35 34 }
> XXX debug txt: setlocales cannonname  = { T  u  r  k  i  s  h  _  T  ?  r
>  k  i  y  e  .  1  2  5  4  }
> XXX debug raw: setup_locale_encoding  = "Turkish_Türkiye.1254"
> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc 72
> 6b 69 79 65 2e 31 32 35 34 }
> XXX debug txt: setup_locale_encoding  = { T  u  r  k  i  s  h  _  T  ?  r
>  k  i  y  e  .  1  2  5  4  }
> The database cluster will be initialized with locale
> "Turkish_Türkiye.1254".
> The default text search configuration will be set to "turkish".
>
> Data page checksums are disabled.
>
> Enter new superuser password:
> Enter it again:
>
> fixing permissions on existing directory c:/Program
> Files/PostgreSQL/15/data ... ok
> creating subdirectories ... ok
> selecting dynamic shared memory implementation ... windows
> selecting default max_connections ... 100
> selecting default shared_buffers ... 128MB
> selecting default time zone ... UTC
> creating configuration files ... ok
> running bootstrap script ... ok
> performing post-bootstrap initialization ... ok
> syncing data to disk ... ok
>
> Success. You can now start the database server using:
>
>     pg_ctl -D ^"c^:^\Program^ Files^\PostgreSQL^\15^\data^" -l logfile
> start
>

 v16:

> C:\Program Files\PostgreSQL\16\bin>initdb.exe --encoding=UTF-8 -A
> scram-sha-256 -U postgres -D "c:\Program Files\PostgreSQL\16\data" --locale
> "Turkish,Türkiye" -W XXX debug raw: getopt optarg = "Turkish,Türkiye" XXX
> debug hex: getopt optarg = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69 79 65 }
> XXX debug txt: getopt optarg = { T u r k i s h , T ? r k i y e } XXX debug
> raw: getopt optarg = "Turkish,Türkiye" XXX debug hex: getopt optarg = { 54
> 75 72 6b 69 73 68 2c 54 fc 72 6b 69 79 65 } XXX debug txt: getopt optarg =
> { T u r k i s h , T ? r k i y e } The files belonging to this database
> system will be owned by user "Administrator". This user must also own the
> server process. XXX debug raw: setlocales lc_ctype = "Turkish,Türkiye" XXX
> debug hex: setlocales lc_ctype = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69
> 79 65 } XXX debug txt: setlocales lc_ctype = { T u r k i s h , T ? r k i y
> e } XXX debug raw: setlocales cannonname = "Turkish_Türkiye.1254" XXX debug
> hex: setlocales cannonname = { 54 75 72 6b 69 73 68 5f 54 fc 72 6b 69 79 65
> 2e 31 32 35 34 } XXX debug txt: setlocales cannonname = { T u r k i s h _ T
> ? r k i y e . 1 2 5 4 } XXX debug raw: setup_locale_encoding =
> "Turkish_Türkiye.1254" XXX debug hex: setup_locale_encoding = { 54 75 72 6b
> 69 73 68 5f 54 fc 72 6b 69 79 65 2e 31 32 35 34 } XXX debug txt:
> setup_locale_encoding = { T u r k i s h _ T ? r k i y e . 1 2 5 4 } The
> database cluster will be initialized with locale "Turkish_Türkiye.1254".
> The default text search configuration will be set to "turkish". Data page
> checksums are disabled. Enter new superuser password: Enter it again:
> fixing permissions on existing directory c:/Program
> Files/PostgreSQL/16/data ... ok creating subdirectories ... ok selecting
> dynamic shared memory implementation ... windows selecting default
> max_connections ... 100 selecting default shared_buffers ... 128MB
> selecting default time zone ... UTC creating configuration files ... ok
> running bootstrap script ... ok performing post-bootstrap initialization
> ... child process was terminated by exception 0xC0000409 initdb: removing
> contents of data directory "c:/Program Files/PostgreSQL/16/data"
>

-- 
Sandeep Thakkar

---

From: Sandeep Thakkar <[email-masked]>
Date: Tue, 6 Aug 2024 16:08:37 +0530

On Tue, Aug 6, 2024 at 4:06 PM Sandeep Thakkar <
[email-masked]> wrote:

>> XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i
>>  y  e  }
>> XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i
>>  y  e  }
>> XXX debug txt: setlocales lc_ctype  = { T  u  r  k  i  s  h  ,  T  ?  r
>>  k  i  y  e  }
>> XXX debug hex: setlocales cannonname  = { 54 75 72 6b 69 73 68 5f 54 fc
>> 72 6b 69 79 65 2e 31 32 35 34 }
>> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc
>> 72 6b 69 79 65 2e 31 32 35 34 }
>> XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i
>>  y  e  }
>> XXX debug txt: getopt optarg  = { T  u  r  k  i  s  h  ,  T  ?  r  k  i
>>  y  e  }
>> "Administrator".
>> XXX debug txt: setlocales lc_ctype  = { T  u  r  k  i  s  h  ,  T  ?  r
>>  k  i  y  e  }
>> XXX debug hex: setlocales cannonname  = { 54 75 72 6b 69 73 68 5f 54 fc
>> 72 6b 69 79 65 2e 31 32 35 34 }
>> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc
>> 72 6b 69 79 65 2e 31 32 35 34 }

-- 
Sandeep Thakkar

---

From: "Peter J. Holzer" <[email-masked]>
Date: Tue, 6 Aug 2024 13:44:05 +0200

On 2024-08-06 16:06:51 +0530, Sandeep Thakkar wrote:
>     and 14) and also confirmed by @Ertan Küçükoglu at https://github.com/
>     EnterpriseDB/edb-installers/issues/127#issuecomment-2268371442
> 

I assume that "1254" here is the code page.
But you specified --encoding=UTF-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 "ü" in the locale name).

>     XXX debug hex: getopt optarg  = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69 79
>     65 }

This also looks like window-1254 (or at least some ISO-8859 variant) to
me.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Thomas Munro <[email-masked]>
Date: Wed, 7 Aug 2024 07:54:12 +1200

On Tue, Aug 6, 2024 at 10:38 PM Sandeep Thakkar
<[email-masked]> wrote:
> On Tue, Aug 6, 2024 at 4:06 PM Sandeep Thakkar <[email-masked]> wrote:

[v15]

>>> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc 72 6b 69 79 65 2e 31 32 35 34 }
>>> XXX debug txt: setup_locale_encoding  = { T  u  r  k  i  s  h  _  T  ?  r  k  i  y  e  .  1  2  5  4  }
>>> The database cluster will be initialized with locale "Turkish_Türkiye.1254".

[v16]

>>> XXX debug hex: setup_locale_encoding  = { 54 75 72 6b 69 73 68 5f 54 fc 72 6b 69 79 65 2e 31 32 35 34 }
>>> XXX debug txt: setup_locale_encoding  = { T  u  r  k  i  s  h  _  T  ?  r  k  i  y  e  .  1  2  5  4  }
>>> The database cluster will be initialized with locale "Turkish_Türkiye.1254".

OK so we see that the "Turkish,Türkiye" -> "Turkish_Türkiye.1254"
transformation happens in the "cannonname" step, and then the final
values are identical between the versions.

>>> performing post-bootstrap initialization ... child process was terminated by exception 0xC0000409

If I understand correctly that's abort(), so can you please run it
with -d so we can maybe see some information about that?  Also -n
might be useful to stop it from deleting the data directory at the end
in case something useful for diagnosis is in there.

---

From: Thomas Munro <[email-masked]>
Date: Wed, 7 Aug 2024 09:08:44 +1200

> the encodings differ (such as "ü" 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 == user_enc ||
          locale_enc == PG_SQL_ASCII ||
          locale_enc == -1 ||
#ifdef WIN32
          user_enc == PG_UTF8 ||
#endif
          user_enc == 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=tr-TR (defaults to --encoding=WIN1254)
--locale=tr-TR --encoding=WIN1254
--locale-tr-TR.UTF-8
--locale=tr-TR.UTF-8 --encoding=UTF-8

If we come up with an automated (or even manual but documented) way to
perform the "Turkish_Türkiye.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="English_United States.1252" and --encoding=UTF-8, and should
be changed to "en-US.UTF-8", while those initialised by letting
initdb.exe pick the encoding must be using --locale="English_United
States.1252" and --encoding=WIN1252 (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.)

---

From: Thomas Munro <[email-masked]>
Date: Thu, 8 Aug 2024 12:41:29 +1200

Thanks.  The log didn't offer any more clues, and my colleague David R
has Windows and knows how to work its debugger so we sat down together
and chased this down (thanks David!).

1.  It is indeed calling abort(), but it's not a PANIC or Assert() in
PostgreSQL, it's an assertion inside Windows' own setlocale():

minkernel\crts\ucrt\src\appcrt\convert\mbstowcs.cpp(245) : Assertion
failed: (pwcs == nullptr && sizeInWords == 0) || (pwcs != nullptr &&
sizeInWords > 0)

2.  It is indeed confused about the encoding of the string
"Turkish_Türkiye.1254" itself, and works fine if you use "tr-TR".

3.  It doesn't happen on 15, because 16 added a key ingredient:

commit bf03cfd162176d543da79f9398131abc251ddbb9
Author: Peter Eisentraut <[email-masked]>
Date:   Tue Jan 3 14:21:40 2023 +0100

    Windows support in pg_import_system_collations

That causes it to spin through a bunch of system locales and switch to
them, and the first one is "aa".  After it calls:

    setlocale(2, "aa");

... then the next call to restore the previous locale is something like:

    setlocale(2, "Turkish_T\252rkiye.1254");

(That \252 == 0xfc probably depends on your system's default
encoding.)  It doesn't like that name anymore, and aborts.  A minimal
program with just those two lines shows that.

It appears that after switching to "aa", it interprets the string
passed to the next call to setlocale() as some other encoding
(probably UTF-8, I dunno).  I don't know why it doesn't fail and
return NULL, but there is a more general point that it's a bit bonkers
to use non-ASCII byte sequences in the library calls that are used to
control how non-ASCII byte sequences are interpreted.  Maybe it can be
done if you're careful, but in particular a naive save-and-restore
sequence just won't work.

I guess a save-and-restore done with wsetlocale() could fix that.  But
I decline to work on that, we need less Windows kludgery in the tree,
not more.  I think a better answer is "don't do that".

Really, we *have* to chase all these non-BCP-47 locales out of the
installer (I hope you can work on that?), out of PostgreSQL (testers
wanted[1]), and out of the world's existing clusters (maybe with
Dave's pg_upgrade idea, someone would need to write a patch, or maybe
someone could write a stand-alone locale migration program that just
connects to a cluster and (using some authoritative source, that's the
key bit to research) and replaces bad old names with nice new ones).

[1] https://www.postgresql.org/message-id/flat/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=[email-masked]

---

From: Sandeep Thakkar <[email-masked]>
Date: Thu, 8 Aug 2024 10:53:27 +0530

On Thu, Aug 8, 2024 at 6:10 AM Thomas Munro <[email-masked]> wrote:

> installer (I hope you can work on that?),

yeah, It seems getlocales.cpp needs to be changed to achieve it.
I'll look into it

out of PostgreSQL (testers
> [1]
> https://www.postgresql.org/message-id/flat/CA+hUKGJ=XThErgAQRoqfCy1bKPxXVuF0=[email-masked]
>

-- 
Sandeep Thakkar