Subject: Re: Move defaults toward ICU in 16?

From: Robert Haas <[email-masked]>
Date: Thu, 2 Feb 2023 08:44:58 -0500

On Thu, Feb 2, 2023 at 8:13 AM Jeff Davis <[email-masked]> wrote:
> If we don't want to nudge users toward ICU, is it because we are
> waiting for something, or is there a lack of consensus that ICU is
> actually better?

Do you think it's better?

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Jeff Davis <[email-masked]>
Date: Thu, 02 Feb 2023 08:31:46 -0800

> Do you think it's better?

Yes:

  * ICU more featureful: e.g. supports case-insensitive collations (the
citext docs suggest looking at ICU instead).
  * It's faster: a simple non-contrived sort is something like 70%
faster[1] than one using glibc.
  * It can provide consistent semantics across platforms.

I believe the above reasons are enough to call ICU "better", but it
also seems like a better path for addressing/mitigating collator
versioning problems:

  * Easier for users to control what library version is available on
their system. We can also ask packagers to keep some old versions of
ICU available for an extended period of time.
  * If one of the ICU multilib patches makes it in, it will be easier
for users to select which of the library versions Postgres will use.
  * Reports versions for indiividual collators, distinct from the
library version.

The biggest disadvantage (rather, the flip side of its advantages) is
that it's a separate dependency. Will ICU still be maintained in 10
years or will we end up stuck maintaining it ourselves? Then again,
we've already been shipping it, so I don't know if we can avoid that
problem entirely now even if we wanted to.

I don't mean that ICU solves all of our problems -- far from it. But
you asked if I think it's better, and my answer is yes.

Regards,
	Jeff Davis

[1]
https://postgr.es/m/[email-masked]

---

From: Thomas Munro <[email-masked]>
Date: Fri, 3 Feb 2023 11:14:40 +1300

>   * It can provide consistent semantics across platforms.

+1

>   * Reports versions for indiividual collators, distinct from the

+1

> problem entirely now even if we wanted to.

It has a pretty special status, with an absolutely enormous amount of
technology depending on it.

http://blog.unicode.org/2016/05/icu-joins-unicode-consortium.html
https://unicode.org/consortium/consort.html
https://home.unicode.org/membership/members/
https://home.unicode.org/about-unicode/

I mean, who knows what the future holds, but ultimately what we're
doing here is taking the de facto reference implementation of the
Unicode collation algorithm.  Are Unicode and the consortium still
going to be here in 10 years?  We're all in on Unicode, and it's also
tangled up with ISO standards, as are parts of the collation stuff.
Sure, there could be a clean-room implementation that replaces it in
some sense (just as there is a Java implementation) but it would very
likely be "the same" because the real thing we're buying here is the
set of algorithms and data maintenance that the whole industry has
agreed on.

Unless Britain decides to exit the Latin alphabet, terminate
membership of ISO and revert to anglo-saxon runes with a sort order
that is defined in the new constitution as "the opposite of whatever
Unicode says", it's hard to see obstacles to ICU's long term universal
applicability.

It's still important to have libc support as an option, though,
because it's a totally reasonable thing to want sort order to agree
with the "sort" command on the same host, and you are willing to deal
with all the complexities that we're trying to escape.

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 2 Feb 2023 14:48:05 -0800

> set of algorithms and data maintenance that the whole industry has

I don't think that a clean room implementation is implausible. They
seem to already exist, and be explicitly provided for by CLDR, which
is not joined at the hip to ICU:

https://github.com/elixir-cldr/cldr

Most of the value that we tend to think of as coming from ICU actually
comes from CLDR itself, as well as related Unicode Consortium and IETF
standards/RFCs such as BCP-47.

> Unicode says", it's hard to see obstacles to ICU's long term universal

It would have to literally be defined as "not unicode" for it to
present a real problem. A key goal of Unicode is to accommodate
political and cultural shifts, since even countries can come and go.
In principle Unicode should be able to accommodate just about any
change in preferences, except when there is an irreconcilable
difference of opinion among people that are from the same natural
language group. For example it can accommodate relatively minor
differences of opinion about how text should be sorted among groups
that each speak a regional dialect of the same language. Hardly
anybody even notices this.

Accommodating these variations can only come from making a huge
investment. Most of the work is actually done by natural language
scholars, not technologists. That effort is very unlikely to be
duplicated by some other group with its own conflicting goals. AFAICT
there is no great need for any schisms, since differences of opinion
can usually be accommodated under the umbrella of Unicode.

-- 
Peter Geoghegan

---

From: Tom Lane <[email-masked]>
Date: Thu, 02 Feb 2023 18:10:49 -0500

> with all the complexities that we're trying to escape.

Yeah.  I would be resistant to making ICU a required dependency,
but it doesn't seem unreasonable to start moving towards it being
our default collation support.

			regards, tom lane

---

From: Jeff Davis <[email-masked]>
Date: Wed, 08 Feb 2023 12:16:46 -0800

> our default collation support.

Patch attached.

To get the default locale, the patch initializes a UCollator with NULL
for the locale name, and then queries it for the locale name. Then it's
converted to a language tag, which is consistent with the initial
collation import. I'm not sure that's the best way, but it seems
reasonable.

If it's a user-provided locale (--icu-locale=), then the patch leaves
it as-is, and does not convert it to a language tag (consistent with
CREATE COLLATION and CREATE DATABASE).

I opened another discussion about whether we want to try harder to
validate or canonicalize the locale name:

https://www.postgresql.org/message-id/[email-masked]

-- 
Jeff Davis
PostgreSQL Contributor Team - AWS

From 1b7d940c0f12062185b8b42bf8d3c0a6f05a74d4 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email-masked]>
Date: Wed, 8 Feb 2023 12:06:26 -0800
Subject: [PATCH v1] Use ICU by default at initdb time.

If the ICU locale is not specified, initialize the default collator
and retrieve the locale name from that.

Discussion: https://postgr.es/m/[email-masked]
---
 src/bin/initdb/initdb.c | 74 +++++++++++++++++++++++++++++++++++++++--
 1 file changed, 72 insertions(+), 2 deletions(-)

diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 7a58c33ace..7321652db3 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -53,6 +53,7 @@
 #include <netdb.h>
 #include <sys/socket.h>
 #include <sys/stat.h>
+#include <unicode/ucol.h>
 #include <unistd.h>
 #include <signal.h>
 #include <time.h>
@@ -133,7 +134,11 @@ static char *lc_monetary = NULL;
 static char *lc_numeric = NULL;
 static char *lc_time = NULL;
 static char *lc_messages = NULL;
+#ifdef USE_ICU
+static char locale_provider = COLLPROVIDER_ICU;
+#else
 static char locale_provider = COLLPROVIDER_LIBC;
+#endif
 static char *icu_locale = NULL;
 static const char *default_text_search_config = NULL;
 static char *username = NULL;
@@ -2024,6 +2029,72 @@ check_icu_locale_encoding(int user_enc)
 	return true;
 }
 
+/*
+ * Check that ICU accepts the locale name; or if not specified, retrieve the
+ * default ICU locale.
+ */
+static void
+check_icu_locale()
+{
+#ifdef USE_ICU
+	UCollator	*collator;
+	UErrorCode   status;
+
+	status = U_ZERO_ERROR;
+	collator = ucol_open(icu_locale, &status);
+	if (U_FAILURE(status))
+	{
+		if (icu_locale)
+			pg_fatal("ICU locale \"%s\" could not be opened: %s",
+					 icu_locale, u_errorName(status));
+		else
+			pg_fatal("default ICU locale could not be opened: %s",
+					 u_errorName(status));
+	}
+
+	/* if not specified, get locale from default collator */
+	if (icu_locale == NULL)
+	{
+		const char	*default_locale;
+
+		status = U_ZERO_ERROR;
+		default_locale = ucol_getLocaleByType(collator, ULOC_VALID_LOCALE,
+											  &status);
+		if (U_FAILURE(status))
+		{
+			ucol_close(collator);
+			pg_fatal("could not determine default ICU locale");
+		}
+
+		if (U_ICU_VERSION_MAJOR_NUM >= 54)
+		{
+			const bool	 strict = true;
+			char		*langtag;
+			int			 len;
+
+			len = uloc_toLanguageTag(default_locale, NULL, 0, strict, &status);
+			langtag = pg_malloc(len + 1);
+			status = U_ZERO_ERROR;
+			uloc_toLanguageTag(default_locale, langtag, len + 1, strict,
+							   &status);
+
+			if (U_FAILURE(status))
+			{
+				ucol_close(collator);
+				pg_fatal("could not determine language tag for default locale \"%s\": %s",
+						 default_locale, u_errorName(status));
+			}
+
+			icu_locale = langtag;
+		}
+		else
+			icu_locale = pg_strdup(default_locale);
+	}
+
+	ucol_close(collator);
+#endif
+}
+
 /*
  * set up the locale variables
  *
@@ -2077,8 +2148,7 @@ setlocales(void)
 
 	if (locale_provider == COLLPROVIDER_ICU)
 	{
-		if (!icu_locale)
-			pg_fatal("ICU locale must be specified");
+		check_icu_locale();
 
 		/*
 		 * In supported builds, the ICU locale ID will be checked by the
-- 
2.34.1

---

From: Andres Freund <[email-masked]>
Date: Wed, 8 Feb 2023 18:22:34 -0800

> > our default collation support.

Unfortunately this fails widely on CI, with both compile time and runtime
issues:
https://cirrus-ci.com/build/5116408950947840

---

From: Jeff Davis <[email-masked]>
Date: Fri, 10 Feb 2023 16:17:00 -0800

> Unfortunately this fails widely on CI, with both compile time and

New patches attached.

  0001: build defaults to requiring ICU
  0002: initdb defaults to using ICU (if server built with ICU)

One CI test is failing: "Windows - Server 2019, VS 2019 - Meson &
ninja"; if I apply Andres patch (
https://github.com/anarazel/postgres/commit/dde7c68 ), then it works.

I ran into one annoyance with pg_upgrade, which is that a v15 cluster
initialized with the defaults requires that the v16 cluster is
initialized with --locale-provider=libc, because otherwise the old and
new cluster will have mismatching template databases. Simple to fix
once you see the error, but I wonder how many initdb scripts might be
broken? I suppose it's just the cost of changing a default? Would an
environment variable help for cases where it's difficult to pass that
extra option down through a script?

I also considered posting another patch to change the default for
CREATE COLLATION, but there are a few issues I'm not sure about. Should
the default be based on whether ICU support is available? Or the
datlocprovider for the current database? And/or some kind of
compatibility GUC?

Notes on the tests I needed to fix, in case they are interesting or
point to some kind of larger problem:

 * ecpg has a test that involves setting the client_encoding to LATIN1
which required a compatible server encoding so it was setting
ENCODING=SQL_ASCII, which ICU doesn't support. The ecpg test did not
look particularly sensitive to the locale, so I changed it to use
client_encoding=SQL_ASCII instead, so that the server encoding doesn't
matter.
 * citext has a test involving Turkish characters, which works for all
libc locales, but in ICU the test only works in Turkish locales. I skip
the test if datlocprovider='i', because citext doesn't seem very
important in an ICU world.
 * unaccent is broken if the database provider is ICU and LC_CTYPE=C,
because the t_isspace() (etc.) functions do not properly handle ICU.
Probably some other things are broken with that combination, but only
this test seems to exercise it. I just skipped the test for that broken
combination, but perhaps it should be fixed in the future.
 * initdb was being built with ICU as a dependency in meson, but not
autoconf. I assume it's fine to link ICU into initdb, so I changed the
Makefile.
 * I changed a couple tests to initialize with --locale-provider=libc.
They were testing that creating a database with the ICU provider but no
ICU locale fails, and that's easiest to test if the template is libc.
 * The CI test CompilerWarnings:mingw_cross_warning was failing because
ICU is not available. I added --without-icu in the .cirrus.yml file and
it works.

From b1772b12be3c47a00a3723d2937421cb5bbfe3a3 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email-masked]>
Date: Fri, 10 Feb 2023 12:08:11 -0800
Subject: [PATCH v2 1/2] Build ICU support by default.

Discussion: https://postgr.es/m/[email-masked]
---
 .cirrus.yml                    |  1 +
 configure                      | 46 ++++++++------------
 configure.ac                   |  8 +++-
 doc/src/sgml/installation.sgml | 78 +++++++++++++++++++---------------
 meson.build                    | 12 +++++-
 meson_options.txt              |  2 +-
 6 files changed, 78 insertions(+), 69 deletions(-)

diff --git a/.cirrus.yml b/.cirrus.yml
index 6b98980075..24302efea6 100644
--- a/.cirrus.yml
+++ b/.cirrus.yml
@@ -775,6 +775,7 @@ task:
       time ./configure \
         --host=x86_64-w64-mingw32 \
         --enable-cassert \
+        --without-icu \
         CC="ccache x86_64-w64-mingw32-gcc" \
         CXX="ccache x86_64-w64-mingw32-g++"
       make -s -j${BUILD_JOBS} clean
diff --git a/configure b/configure
index 5d07fd0bb9..ddb75f7f4b 100755
--- a/configure
+++ b/configure
@@ -1558,7 +1558,7 @@ Optional Packages:
                           set WAL block size in kB [8]
   --with-CC=CMD           set compiler (deprecated)
   --with-llvm             build with LLVM based JIT support
-  --with-icu              build with ICU support
+  --without-icu           build without ICU support
   --with-tcl              build Tcl modules (PL/Tcl)
   --with-tclconfig=DIR    tclConfig.sh is in DIR
   --with-perl             build Perl modules (PL/Perl)
@@ -8401,7 +8401,9 @@ $as_echo "#define USE_ICU 1" >>confdefs.h
   esac
 
 else
-  with_icu=no
+  with_icu=yes
+
+$as_echo "#define USE_ICU 1" >>confdefs.h
 
 fi
 
@@ -8470,31 +8472,17 @@ fi
 	# Put the nasty error message in config.log where it belongs
 	echo "$ICU_PKG_ERRORS" >&5
 
-	as_fn_error $? "Package requirements (icu-uc icu-i18n) were not met:
-
-$ICU_PKG_ERRORS
-
-Consider adjusting the PKG_CONFIG_PATH environment variable if you
-installed software in a non-standard prefix.
-
-Alternatively, you may set the environment variables ICU_CFLAGS
-and ICU_LIBS to avoid the need to call pkg-config.
-See the pkg-config man page for more details." "$LINENO" 5
+	as_fn_error $? "ICU library not found
+If you have ICU already installed, see config.log for details on the
+failure.  It is possible the compiler isn't looking in the proper directory.
+Use --without-icu to disable ICU support." "$LINENO" 5
 elif test $pkg_failed = untried; then
         { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5
 $as_echo "no" >&6; }
-	{ { $as_echo "$as_me:${as_lineno-$LINENO}: error: in \`$ac_pwd':" >&5
-$as_echo "$as_me: error: in \`$ac_pwd':" >&2;}
-as_fn_error $? "The pkg-config script could not be found or is too old.  Make sure it
-is in your PATH or set the PKG_CONFIG environment variable to the full
-path to pkg-config.
-
-Alternatively, you may set the environment variables ICU_CFLAGS
-and ICU_LIBS to avoid the need to call pkg-config.
-See the pkg-config man page for more details.
-
-To get pkg-config, see <http://pkg-config.freedesktop.org/>.
-See \`config.log' for more details" "$LINENO" 5; }
+	as_fn_error $? "ICU library not found
+If you have ICU already installed, see config.log for details on the
+failure.  It is possible the compiler isn't looking in the proper directory.
+Use --without-icu to disable ICU support." "$LINENO" 5
 else
 	ICU_CFLAGS=$pkg_cv_ICU_CFLAGS
 	ICU_LIBS=$pkg_cv_ICU_LIBS
@@ -15323,7 +15311,7 @@ else
     We can't simply define LARGE_OFF_T to be 9223372036854775807,
     since some C++ compilers masquerading as C compilers
     incorrectly reject 9223372036854775807.  */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
   int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
 		       && LARGE_OFF_T % 2147483647 == 1)
 		      ? 1 : -1];
@@ -15369,7 +15357,7 @@ else
     We can't simply define LARGE_OFF_T to be 9223372036854775807,
     since some C++ compilers masquerading as C compilers
     incorrectly reject 9223372036854775807.  */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
   int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
 		       && LARGE_OFF_T % 2147483647 == 1)
 		      ? 1 : -1];
@@ -15393,7 +15381,7 @@ rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
     We can't simply define LARGE_OFF_T to be 9223372036854775807,
     since some C++ compilers masquerading as C compilers
     incorrectly reject 9223372036854775807.  */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
   int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
 		       && LARGE_OFF_T % 2147483647 == 1)
 		      ? 1 : -1];
@@ -15438,7 +15426,7 @@ else
     We can't simply define LARGE_OFF_T to be 9223372036854775807,
     since some C++ compilers masquerading as C compilers
     incorrectly reject 9223372036854775807.  */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
   int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
 		       && LARGE_OFF_T % 2147483647 == 1)
 		      ? 1 : -1];
@@ -15462,7 +15450,7 @@ rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
     We can't simply define LARGE_OFF_T to be 9223372036854775807,
     since some C++ compilers masquerading as C compilers
     incorrectly reject 9223372036854775807.  */
-#define LARGE_OFF_T (((off_t) 1 << 62) - 1 + ((off_t) 1 << 62))
+#define LARGE_OFF_T ((((off_t) 1 << 31) << 31) - 1 + (((off_t) 1 << 31) << 31))
   int off_t_is_large[(LARGE_OFF_T % 2147483629 == 721
 		       && LARGE_OFF_T % 2147483647 == 1)
 		      ? 1 : -1];
diff --git a/configure.ac b/configure.ac
index e9b74ced6c..909f5dba3c 100644
--- a/configure.ac
+++ b/configure.ac
@@ -853,13 +853,17 @@ AC_SUBST(enable_thread_safety)
 # ICU
 #
 AC_MSG_CHECKING([whether to build with ICU support])
-PGAC_ARG_BOOL(with, icu, no, [build with ICU support],
+PGAC_ARG_BOOL(with, icu, yes, [build without ICU support],
               [AC_DEFINE([USE_ICU], 1, [Define to build with ICU support. (--with-icu)])])
 AC_MSG_RESULT([$with_icu])
 AC_SUBST(with_icu)
 
 if test "$with_icu" = yes; then
-  PKG_CHECK_MODULES(ICU, icu-uc icu-i18n)
+  PKG_CHECK_MODULES(ICU, icu-uc icu-i18n, [],
+    [AC_MSG_ERROR([ICU library not found
+If you have ICU already installed, see config.log for details on the
+failure.  It is possible the compiler isn't looking in the proper directory.
+Use --without-icu to disable ICU support.])])
 fi
 
 #
diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml
index 6619e69462..b5f88d27df 100644
--- a/doc/src/sgml/installation.sgml
+++ b/doc/src/sgml/installation.sgml
@@ -146,6 +146,35 @@ documentation.  See standalone-profile.xsl for details.
       <application>pg_restore</application>.
      </para>
     </listitem>
+
+    <listitem>
+     <para>
+      The ICU locale provider (see <xref linkend="locale-providers"/>) is used by default. If you don't want to use it then you must specify the <option>--without-icu</option> option to <filename>configure</filename>. Using this option disables support for ICU collation features (see <xref linkend="collation"/>).
+     </para>
+     <para>
+      ICU support requires the <productname>ICU4C</productname> package to be
+      installed.  The minimum required version of
+      <productname>ICU4C</productname> is currently 4.2.
+     </para>
+
+     <para>
+      By default,
+      <productname>pkg-config</productname><indexterm><primary>pkg-config</primary></indexterm>
+      will be used to find the required compilation options.  This is
+      supported for <productname>ICU4C</productname> version 4.6 and later.
+      For older versions, or if <productname>pkg-config</productname> is not
+      available, the variables <envar>ICU_CFLAGS</envar> and
+      <envar>ICU_LIBS</envar> can be specified to
+      <filename>configure</filename>, like in this example:
+<programlisting>
+./configure ... ICU_CFLAGS='-I/some/where/include' ICU_LIBS='-L/some/where/lib -licui18n -licuuc -licudata'
+</programlisting>
+      (If <productname>ICU4C</productname> is in the default search path
+      for the compiler, then you still need to specify nonempty strings in
+      order to avoid use of <productname>pkg-config</productname>, for
+      example, <literal>ICU_CFLAGS=' '</literal>.)
+     </para>
+    </listitem>
    </itemizedlist>
   </para>
 
@@ -926,40 +955,6 @@ build-postgresql:
        </listitem>
       </varlistentry>
 
-      <varlistentry id="configure-option-with-icu">
-       <term><option>--with-icu</option></term>
-       <listitem>
-        <para>
-         Build with support for
-         the <productname>ICU</productname><indexterm><primary>ICU</primary></indexterm>
-         library, enabling use of ICU collation
-         features<phrase condition="standalone-ignore"> (see
-         <xref linkend="collation"/>)</phrase>.
-         This requires the <productname>ICU4C</productname> package
-         to be installed.  The minimum required version
-         of <productname>ICU4C</productname> is currently 4.2.
-        </para>
-
-        <para>
-         By default,
-         <productname>pkg-config</productname><indexterm><primary>pkg-config</primary></indexterm>
-         will be used to find the required compilation options.  This is
-         supported for <productname>ICU4C</productname> version 4.6 and later.
-         For older versions, or if <productname>pkg-config</productname> is
-         not available, the variables <envar>ICU_CFLAGS</envar>
-         and <envar>ICU_LIBS</envar> can be specified
-         to <filename>configure</filename>, like in this example:
-<programlisting>
-./configure ... --with-icu ICU_CFLAGS='-I/some/where/include' ICU_LIBS='-L/some/where/lib -licui18n -licuuc -licudata'
-</programlisting>
-         (If <productname>ICU4C</productname> is in the default search path
-         for the compiler, then you still need to specify nonempty strings in
-         order to avoid use of <productname>pkg-config</productname>, for
-         example, <literal>ICU_CFLAGS=' '</literal>.)
-        </para>
-       </listitem>
-      </varlistentry>
-
       <varlistentry id="configure-with-llvm">
        <term><option>--with-llvm</option></term>
        <listitem>
@@ -1231,6 +1226,19 @@ build-postgresql:
 
      <variablelist>
 
+      <varlistentry id="configure-option-without-icu">
+       <term><option>--without-icu</option></term>
+       <listitem>
+        <para>
+         Build without support for the
+         <productname>ICU</productname><indexterm><primary>ICU</primary></indexterm>
+         library, disabling the use of ICU collation features<phrase
+         condition="standalone-ignore"> (see <xref
+         linkend="collation"/>)</phrase>.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="configure-option-without-readline">
        <term><option>--without-readline</option></term>
        <listitem>
@@ -2419,7 +2427,7 @@ ninja install
         <productname>ICU</productname><indexterm><primary>ICU</primary></indexterm>
         library, enabling use of ICU collation features<phrase
         condition="standalone-ignore"> (see <xref
-        linkend="collation"/>)</phrase>.  Defaults to auto and requires the
+        linkend="collation"/>)</phrase>.  Defaults to enabled and requires the
         <productname>ICU4C</productname> package to be installed.  The minimum
         required version of <productname>ICU4C</productname> is currently 4.2.
        </para>
diff --git a/meson.build b/meson.build
index e379a252a5..9cd7491fda 100644
--- a/meson.build
+++ b/meson.build
@@ -721,8 +721,16 @@ endif
 
 icuopt = get_option('icu')
 if not icuopt.disabled()
-  icu = dependency('icu-uc', required: icuopt.enabled())
-  icu_i18n = dependency('icu-i18n', required: icuopt.enabled())
+  icu = dependency('icu-uc', required: false)
+  icu_i18n = dependency('icu-i18n', required: false)
+
+  icu_found = icu.found() and icu_i18n.found()
+  if icuopt.enabled() and not icu_found
+     error('''ICU library not found
+If you have ICU already installed, see meson-log/meson-log.txt for details on the
+failure. It is possible the compiler isn't looking in the proper directory.
+Use -Dicu=disabled to disable ICU support.''')
+  endif
 
   if icu.found()
     cdata.set('USE_ICU', 1)
diff --git a/meson_options.txt b/meson_options.txt
index 9d3ef4aa20..fbd4e69356 100644
--- a/meson_options.txt
+++ b/meson_options.txt
@@ -82,7 +82,7 @@ option('dtrace', type : 'feature', value: 'disabled',
 option('gssapi', type : 'feature', value: 'auto',
   description: 'GSSAPI support')
 
-option('icu', type : 'feature', value: 'auto',
+option('icu', type : 'feature', value: 'enabled',
   description: 'ICU support')
 
 option('ldap', type : 'feature', value: 'auto',
-- 
2.34.1

From 64ad3a3b0c8bcdf95df90a9db05edb255a3f18c2 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email-masked]>
Date: Wed, 8 Feb 2023 12:06:26 -0800
Subject: [PATCH v2 2/2] Use ICU by default at initdb time.

Discussion: https://postgr.es/m/[email-masked]
---
 contrib/citext/expected/citext_utf8.out       |  4 +-
 contrib/citext/expected/citext_utf8_1.out     |  4 +-
 contrib/citext/sql/citext_utf8.sql            |  4 +-
 contrib/unaccent/expected/unaccent.out        |  9 +++
 contrib/unaccent/expected/unaccent_1.out      |  8 ++
 contrib/unaccent/sql/unaccent.sql             | 11 +++
 doc/src/sgml/ref/initdb.sgml                  | 48 +++++++-----
 src/bin/initdb/Makefile                       |  4 +-
 src/bin/initdb/initdb.c                       | 76 ++++++++++++++++++-
 src/bin/initdb/t/001_initdb.pl                |  7 +-
 src/bin/pg_dump/t/002_pg_dump.pl              |  2 +-
 src/bin/scripts/t/020_createdb.pl             |  2 +-
 src/interfaces/ecpg/test/Makefile             |  3 -
 src/interfaces/ecpg/test/connect/test5.pgc    |  2 +-
 .../ecpg/test/expected/connect-test5.c        |  2 +-
 .../ecpg/test/expected/connect-test5.stderr   |  2 +-
 src/interfaces/ecpg/test/meson.build          |  1 -
 src/test/icu/t/010_database.pl                |  2 +-
 18 files changed, 149 insertions(+), 42 deletions(-)
 create mode 100644 contrib/unaccent/expected/unaccent_1.out

diff --git a/contrib/citext/expected/citext_utf8.out b/contrib/citext/expected/citext_utf8.out
index 666b07ccec..85ce9c3b64 100644
--- a/contrib/citext/expected/citext_utf8.out
+++ b/contrib/citext/expected/citext_utf8.out
@@ -3,7 +3,9 @@
  * and a Unicode-aware locale.
  */
 SELECT getdatabaseencoding() <> 'UTF8' OR
-       current_setting('lc_ctype') = 'C'
+       current_setting('lc_ctype') = 'C' OR
+       (SELECT datlocprovider='i' FROM pg_database
+        WHERE datname=current_database())
        AS skip_test \gset
 \if :skip_test
 \quit
diff --git a/contrib/citext/expected/citext_utf8_1.out b/contrib/citext/expected/citext_utf8_1.out
index 433e985349..60ddebd841 100644
--- a/contrib/citext/expected/citext_utf8_1.out
+++ b/contrib/citext/expected/citext_utf8_1.out
@@ -3,7 +3,9 @@
  * and a Unicode-aware locale.
  */
 SELECT getdatabaseencoding() <> 'UTF8' OR
-       current_setting('lc_ctype') = 'C'
+       current_setting('lc_ctype') = 'C' OR
+       (SELECT datlocprovider='i' FROM pg_database
+        WHERE datname=current_database())
        AS skip_test \gset
 \if :skip_test
 \quit
diff --git a/contrib/citext/sql/citext_utf8.sql b/contrib/citext/sql/citext_utf8.sql
index d068000b42..e9c504e764 100644
--- a/contrib/citext/sql/citext_utf8.sql
+++ b/contrib/citext/sql/citext_utf8.sql
@@ -4,7 +4,9 @@
  */
 
 SELECT getdatabaseencoding() <> 'UTF8' OR
-       current_setting('lc_ctype') = 'C'
+       current_setting('lc_ctype') = 'C' OR
+       (SELECT datlocprovider='i' FROM pg_database
+        WHERE datname=current_database())
        AS skip_test \gset
 \if :skip_test
 \quit
diff --git a/contrib/unaccent/expected/unaccent.out b/contrib/unaccent/expected/unaccent.out
index ee0ac71a1c..cef98ee60c 100644
--- a/contrib/unaccent/expected/unaccent.out
+++ b/contrib/unaccent/expected/unaccent.out
@@ -1,3 +1,12 @@
+-- unaccent is broken if the default collation is provided by ICU and
+-- LC_CTYPE=C
+SELECT current_setting('lc_ctype') = 'C' AND
+       (SELECT datlocprovider='i' FROM pg_database
+        WHERE datname=current_database())
+	AS skip_test \gset
+\if :skip_test
+\quit
+\endif
 CREATE EXTENSION unaccent;
 -- must have a UTF8 database
 SELECT getdatabaseencoding();
diff --git a/contrib/unaccent/expected/unaccent_1.out b/contrib/unaccent/expected/unaccent_1.out
new file mode 100644
index 0000000000..0a4a3838ab
--- /dev/null
+++ b/contrib/unaccent/expected/unaccent_1.out
@@ -0,0 +1,8 @@
+-- unaccent is broken if the default collation is provided by ICU and
+-- LC_CTYPE=C
+SELECT current_setting('lc_ctype') = 'C' AND
+       (SELECT datlocprovider='i' FROM pg_database
+        WHERE datname=current_database())
+	AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/contrib/unaccent/sql/unaccent.sql b/contrib/unaccent/sql/unaccent.sql
index 3fc0c706be..027dfb964a 100644
--- a/contrib/unaccent/sql/unaccent.sql
+++ b/contrib/unaccent/sql/unaccent.sql
@@ -1,3 +1,14 @@
+
+-- unaccent is broken if the default collation is provided by ICU and
+-- LC_CTYPE=C
+SELECT current_setting('lc_ctype') = 'C' AND
+       (SELECT datlocprovider='i' FROM pg_database
+        WHERE datname=current_database())
+	AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
 CREATE EXTENSION unaccent;
 
 -- must have a UTF8 database
diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml
index 5b2bdac101..4f37386ea3 100644
--- a/doc/src/sgml/ref/initdb.sgml
+++ b/doc/src/sgml/ref/initdb.sgml
@@ -89,10 +89,28 @@ PostgreSQL documentation
    and character set encoding. These can also be set separately for each
    database when it is created. <command>initdb</command> determines those
    settings for the template databases, which will serve as the default for
-   all other databases.  By default, <command>initdb</command> uses the
-   locale provider <literal>libc</literal>, takes the locale settings from
-   the environment, and determines the encoding from the locale settings.
-   This is almost always sufficient, unless there are special requirements.
+   all other databases.
+  </para>
+
+  <para>
+   By default, <command>initdb</command> uses the ICU library to provide
+   locale services if the server was built with ICU support; otherwise it uses
+   the <literal>libc</literal> locale provider (see <xref
+   linkend="locale-providers"/>). To choose the specific ICU locale ID to
+   apply, use the option <option>--icu-locale</option>.  Note that for
+   implementation reasons and to support legacy code,
+   <command>initdb</command> will still select and initialize libc locale
+   settings when the ICU locale provider is used.
+  </para>
+
+  <para>
+   Alternatively, <command>initdb</command> can use the locale provider
+   <literal>libc</literal>. To select this option, specify
+   <literal>--locale-provider=libc</literal>, or build the server without ICU
+   support. The <literal>libc</literal> locale provider takes the locale
+   settings from the environment, and determines the encoding from the locale
+   settings.  This is almost always sufficient, unless there are special
+   requirements.
   </para>
 
   <para>
@@ -103,17 +121,6 @@ PostgreSQL documentation
    categories can give nonsensical results, so this should be used with care.
   </para>
 
-  <para>
-   Alternatively, the ICU library can be used to provide locale services.
-   (Again, this only sets the default for subsequently created databases.)  To
-   select this option, specify <literal>--locale-provider=icu</literal>.
-   To choose the specific ICU locale ID to apply, use the option
-   <option>--icu-locale</option>.  Note that
-   for implementation reasons and to support legacy code,
-   <command>initdb</command> will still select and initialize libc locale
-   settings when the ICU locale provider is used.
-  </para>
-
   <para>
    When <command>initdb</command> runs, it will print out the locale settings
    it has chosen.  If you have complex requirements or specified multiple
@@ -234,7 +241,8 @@ PostgreSQL documentation
       <term><option>--icu-locale=<replaceable>locale</replaceable></option></term>
       <listitem>
        <para>
-        Specifies the ICU locale ID, if the ICU locale provider is used.
+        Specifies the ICU locale ID, if the ICU locale provider is used. By
+        default, ICU obtains the ICU locale from the ICU default collator.
        </para>
       </listitem>
      </varlistentry>
@@ -297,10 +305,12 @@ PostgreSQL documentation
       <term><option>--locale-provider={<literal>libc</literal>|<literal>icu</literal>}</option></term>
       <listitem>
        <para>
-        This option sets the locale provider for databases created in the
-        new cluster.  It can be overridden in the <command>CREATE
+        This option sets the locale provider for databases created in the new
+        cluster.  It can be overridden in the <command>CREATE
         DATABASE</command> command when new databases are subsequently
-        created.  The default is <literal>libc</literal>.
+        created.  The default is <literal>icu</literal> if the server was
+        built with ICU support; otherwise the default is
+        <literal>libc</literal> (see <xref linkend="locale-providers"/>).
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/bin/initdb/Makefile b/src/bin/initdb/Makefile
index eab89c5501..d69bd89572 100644
--- a/src/bin/initdb/Makefile
+++ b/src/bin/initdb/Makefile
@@ -16,7 +16,7 @@ subdir = src/bin/initdb
 top_builddir = ../../..
 include $(top_builddir)/src/Makefile.global
 
-override CPPFLAGS := -I$(libpq_srcdir) -I$(top_srcdir)/src/timezone $(CPPFLAGS)
+override CPPFLAGS := -I$(libpq_srcdir) -I$(top_srcdir)/src/timezone $(ICU_CFLAGS) $(CPPFLAGS)
 
 # Note: it's important that we link to encnames.o from libpgcommon, not
 # from libpq, else we have risks of version skew if we run with a libpq
@@ -24,7 +24,7 @@ override CPPFLAGS := -I$(libpq_srcdir) -I$(top_srcdir)/src/timezone $(CPPFLAGS)
 # should ensure that that happens.
 #
 # We need libpq only because fe_utils does.
-LDFLAGS_INTERNAL += -L$(top_builddir)/src/fe_utils -lpgfeutils $(libpq_pgport)
+LDFLAGS_INTERNAL += -L$(top_builddir)/src/fe_utils -lpgfeutils $(libpq_pgport) $(ICU_LIBS)
 
 # use system timezone data?
 ifneq (,$(with_system_tzdata))
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 7a58c33ace..0776294499 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -53,6 +53,9 @@
 #include <netdb.h>
 #include <sys/socket.h>
 #include <sys/stat.h>
+#ifdef USE_ICU
+#include <unicode/ucol.h>
+#endif
 #include <unistd.h>
 #include <signal.h>
 #include <time.h>
@@ -133,7 +136,11 @@ static char *lc_monetary = NULL;
 static char *lc_numeric = NULL;
 static char *lc_time = NULL;
 static char *lc_messages = NULL;
+#ifdef USE_ICU
+static char locale_provider = COLLPROVIDER_ICU;
+#else
 static char locale_provider = COLLPROVIDER_LIBC;
+#endif
 static char *icu_locale = NULL;
 static const char *default_text_search_config = NULL;
 static char *username = NULL;
@@ -2024,6 +2031,72 @@ check_icu_locale_encoding(int user_enc)
 	return true;
 }
 
+/*
+			pg_fatal("could not open collator for locale \"%s\": %s",
+			pg_fatal("could not open collator for default locale: %s",
@@ -2077,8 +2150,7 @@ setlocales(void)

 		/*
 		 * In supported builds, the ICU locale ID will be checked by the
diff --git a/src/bin/initdb/t/001_initdb.pl b/src/bin/initdb/t/001_initdb.pl
index 772769acab..e5d214e09c 100644
--- a/src/bin/initdb/t/001_initdb.pl
+++ b/src/bin/initdb/t/001_initdb.pl
@@ -97,11 +97,6 @@ SKIP:
 
 if ($ENV{with_icu} eq 'yes')
 {
-	command_fails_like(
-		[ 'initdb', '--no-sync', '--locale-provider=icu', "$tempdir/data2" ],
-		qr/initdb: error: ICU locale must be specified/,
-		'locale provider ICU requires --icu-locale');
-
 	command_ok(
 		[
 			'initdb',                '--no-sync',
@@ -116,7 +111,7 @@ if ($ENV{with_icu} eq 'yes')
 			'--locale-provider=icu', '--icu-locale=@colNumeric=lower',
 			"$tempdir/dataX"
 		],
-		qr/FATAL:  could not open collator for locale/,
+		qr/error: could not open collator for locale/,
 		'fails for invalid ICU locale');
 
 	command_fails_like(
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index d92247c915..30294f381c 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1684,7 +1684,7 @@ my %tests = (
 		create_sql =>
 		  "CREATE DATABASE dump_test2 LOCALE = 'C' TEMPLATE = template0;",
 		regexp => qr/^
-			\QCREATE DATABASE dump_test2 \E.*\QLOCALE = 'C';\E
+			\QCREATE DATABASE dump_test2 \E.*\QLOCALE = 'C'\E
 			/xm,
 		like => { pg_dumpall_dbprivs => 1, },
 	},
diff --git a/src/bin/scripts/t/020_createdb.pl b/src/bin/scripts/t/020_createdb.pl
index 3ad4fbb00c..8ec58cdd64 100644
--- a/src/bin/scripts/t/020_createdb.pl
+++ b/src/bin/scripts/t/020_createdb.pl
@@ -13,7 +13,7 @@ program_version_ok('createdb');
 program_options_handling_ok('createdb');
 
 my $node = PostgreSQL::Test::Cluster->new('main');
-$node->init;
+$node->init(extra => ['--locale-provider=libc']);
 $node->start;
 
 $node->issues_sql_like(
diff --git a/src/interfaces/ecpg/test/Makefile b/src/interfaces/ecpg/test/Makefile
index d7a7d1d1ca..cf841a3a5b 100644
--- a/src/interfaces/ecpg/test/Makefile
+++ b/src/interfaces/ecpg/test/Makefile
@@ -14,9 +14,6 @@ override CPPFLAGS := \
 	'-DSHELLPROG="$(SHELL)"' \
 	$(CPPFLAGS)
 
-# default encoding for regression tests
-ENCODING = SQL_ASCII
-
 ifneq ($(build_os),mingw32)
 abs_builddir := $(shell pwd)
 else
diff --git a/src/interfaces/ecpg/test/connect/test5.pgc b/src/interfaces/ecpg/test/connect/test5.pgc
index de29160089..d512553677 100644
--- a/src/interfaces/ecpg/test/connect/test5.pgc
+++ b/src/interfaces/ecpg/test/connect/test5.pgc
@@ -55,7 +55,7 @@ exec sql end declare section;
 	exec sql connect to 'unix:postgresql://localhost/ecpg2_regression' as main user :user USING "connectpw";
 	exec sql disconnect main;
 
-	exec sql connect to unix:postgresql://localhost/ecpg2_regression?connect_timeout=180&client_encoding=latin1 as main user regress_ecpg_user1/connectpw;
+	exec sql connect to unix:postgresql://localhost/ecpg2_regression?connect_timeout=180&client_encoding=sql_ascii as main user regress_ecpg_user1/connectpw;
 	exec sql disconnect main;
 
 	exec sql connect to "unix:postgresql://200.46.204.71/ecpg2_regression" as main user regress_ecpg_user1/connectpw;
diff --git a/src/interfaces/ecpg/test/expected/connect-test5.c b/src/interfaces/ecpg/test/expected/connect-test5.c
index c1124c627f..ec1514ed9a 100644
--- a/src/interfaces/ecpg/test/expected/connect-test5.c
+++ b/src/interfaces/ecpg/test/expected/connect-test5.c
@@ -117,7 +117,7 @@ main(void)
 #line 56 "test5.pgc"

-	{ ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/ecpg2_regression?connect_timeout=180 & client_encoding=latin1" , "regress_ecpg_user1" , "connectpw" , "main", 0); }
+	{ ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/ecpg2_regression?connect_timeout=180 & client_encoding=sql_ascii" , "regress_ecpg_user1" , "connectpw" , "main", 0); }
 #line 58 "test5.pgc"
 
 	{ ECPGdisconnect(__LINE__, "main");}
diff --git a/src/interfaces/ecpg/test/expected/connect-test5.stderr b/src/interfaces/ecpg/test/expected/connect-test5.stderr
index 01a6a0a13b..51cc18916a 100644
--- a/src/interfaces/ecpg/test/expected/connect-test5.stderr
+++ b/src/interfaces/ecpg/test/expected/connect-test5.stderr
@@ -50,7 +50,7 @@
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ecpg_finish: connection main closed
 [NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ECPGconnect: opening database ecpg2_regression on <DEFAULT> port <DEFAULT> with options connect_timeout=180 & client_encoding=latin1 for user regress_ecpg_user1
+[NO_PID]: ECPGconnect: opening database ecpg2_regression on <DEFAULT> port <DEFAULT> with options connect_timeout=180 & client_encoding=sql_ascii for user regress_ecpg_user1
 [NO_PID]: sqlca: code: 0, state: 00000
 [NO_PID]: ecpg_finish: connection main closed
 [NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/meson.build b/src/interfaces/ecpg/test/meson.build
index d0be73ccf9..04c6819a79 100644
--- a/src/interfaces/ecpg/test/meson.build
+++ b/src/interfaces/ecpg/test/meson.build
@@ -69,7 +69,6 @@ ecpg_test_files = files(
 ecpg_regress_args = [
   '--dbname=ecpg1_regression,ecpg2_regression',
   '--create-role=regress_ecpg_user1,regress_ecpg_user2',
-  '--encoding=SQL_ASCII',
 ]
 
 tests += {
diff --git a/src/test/icu/t/010_database.pl b/src/test/icu/t/010_database.pl
index 80ab1c7789..45d77c319a 100644
--- a/src/test/icu/t/010_database.pl
+++ b/src/test/icu/t/010_database.pl
@@ -12,7 +12,7 @@ if ($ENV{with_icu} ne 'yes')
 }
 
 my $node1 = PostgreSQL::Test::Cluster->new('node1');
-$node1->init;
+$node1->init(extra => ['--locale-provider=libc']);
 $node1->start;
 
 $node1->safe_psql('postgres',
-- 
2.34.1

---

From: Andres Freund <[email-masked]>
Date: Fri, 10 Feb 2023 18:00:42 -0800

Hi,

> https://github.com/anarazel/postgres/commit/dde7c68 ), then it works.

Until something like my patch above is done more generally applicable, I think
your patch should disable ICU on windows. Can't just fail to build.

Perhaps we don't need to force ICU use to on with the meson build, given that
it defaults to auto-detection?

> extra option down through a script?

That seems problematic to me.

But, shouldn't pg_upgrade be able to deal with this? As long as the databases
are created with template0, we can create the collations at that point?

>  		       && LARGE_OFF_T % 2147483647 == 1)

This stuff shouldn't be in here, it's due to a debian patched autoconf.

Greetings,

Andres Freund

---

From: Jeff Davis <[email-masked]>
Date: Tue, 14 Feb 2023 09:48:08 -0800

> it defaults to auto-detection?

Done. I changed it back to 'auto', and tests pass.

> are created with template0, we can create the collations at that

Are you saying that the upgraded cluster could have a different default
collation for the template databases than the original cluster?

That would be wrong to do, at least by default, but I could see it
being a useful option.

Or maybe I misunderstand what you're saying?

> This stuff shouldn't be in here, it's due to a debian patched

Removed, thank you.

From 0a691bdc1952871b2ec8d1c5086c90c8943d99cb Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email-masked]>
Date: Fri, 10 Feb 2023 12:08:11 -0800
Subject: [PATCH v3 1/2] Build ICU support by default.

Discussion: https://postgr.es/m/[email-masked]
---
 .cirrus.yml                    |  1 +
 configure                      | 36 ++++++----------
 configure.ac                   |  8 +++-
 doc/src/sgml/installation.sgml | 76 +++++++++++++++++++---------------
 4 files changed, 61 insertions(+), 60 deletions(-)

index f212978752..34450a9c7b 100644
@@ -751,6 +751,7 @@ task:
index 5d07fd0bb9..507ca3c983 100755
 
 else
-  with_icu=no
+  with_icu=yes
+
 
 fi

 
 
 #
index 6619e69462..bad6bc6e5e 100644

 
      <variablelist>

From 9818b4b8c5ec15aa6cbf0e6db6ae888173885dca Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email-masked]>
Date: Wed, 8 Feb 2023 12:06:26 -0800
Subject: [PATCH v3 2/2] Use ICU by default at initdb time.

 

   <para>
 
-  <para>

 
 
+/*

 		/*

 	command_fails_like(

 $node->issues_sql_like(

 

 
 tests += {

---

From: Andres Freund <[email-masked]>
Date: Tue, 14 Feb 2023 09:59:57 -0800

Hi,

> Or maybe I misunderstand what you're saying?

I am saying that pg_upgrade should be able to deal with the difference. The
details of how to implement that, don't matter that much.

FWIW, I don't think it matters much what collation template0 has, since we
allow to change the locale provider when using template0 as the template.

We could easily update template0, if we think that's necessary. But I don't
think it really is. As long as the newly created databases have the right
provider, I'd lean towards not touching template0. But whatever...

Greetings,

Andres Freund