From: Jeff Davis <pgsql@j-davis.com>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Joe Conway <mail@joeconway.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Daniel Verite <daniel@manitou-mail.org>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Order changes in PG16 since ICU introduction
Date: Thu, 08 Jun 2023 17:36:50 -0700
Message-ID: <73f97db9a37fd281bd5077ce16383cd8006b1f59.camel@j-davis.com> (raw)
In-Reply-To: <87sfb4gwgv.fsf@news-spur.riddles.org.uk>
References: <45f0cd77-9c85-4c5e-9396-adbccdb635e2@manitou-mail.org>
<f8d09d8f3d53daa9cdb446d021fe33d6ff7f1ee3.camel@j-davis.com>
<ab925f69-5f9d-f85e-b87c-bd2a44798659@joeconway.com>
<221408.1686079526@sss.pgh.pa.us>
<CA+Tgmob-XgUgGrDx4sEMS3t0AQFgpm4KZgRuCd58bY+TsUO1tQ@mail.gmail.com>
<225170.1686081345@sss.pgh.pa.us>
<531936a9-31cb-9734-5a66-7c210133cc1b@joeconway.com>
<87sfb4gwgv.fsf@news-spur.riddles.org.uk>
On Tue, 2023-06-06 at 21:37 +0100, Andrew Gierth wrote:
> > > > >
> I like either "internal" or "builtin" because they correctly identify
> that no external resources are used. I'm not keen on "postgresql".
"builtin" seems to be the winner. New patch series attached with doc
and test updates.
This has been a long discussion (it's a messy problem), but I think
I've addressed the most important concerns raised. If you disagree with
something, please indicate whether it's an objection, or a more minor
difference of opinion that I can weigh against other opinions. Also
please indicate if you think something is out of scope for 16.
Patches 0001, 0002:
These patches implement the built-in provider and automatically change
provider=icu to provider=builtin when the locale is C. Other approaches
were considered:
* Pretend that ICU can support the C locale, and use similar checks
throughout the code like the libc provider does: This was somewhat of a
hack, and had potential issues with upgraded clusters, and several
people seemed to reject it.
* Switch to the libc provider for the C locale: would make the libc
provider even more complicated and had some potential for confusion,
and also has catalog representation problems when --locale is specified
along with --lc-ctype.
Ultimately we need to choose one approach, and the built-in provider
seems the nicest (though most invasive). It reflects the reality that
we don't actually use libc or icu for the C locale, and it's nicer to
document. The builtin provider seemed to get the most support.
Patch 0003:
Makes LOCALE apply to all providers. The overall feel after this patch
is that "locale" now means the collation locale, and
LC_COLLATE/LC_CTYPE are for the server environment. When using libc,
LC_COLLATE and LC_CTYPE still work as they did before, but their
relationship to database collation feels more like a special case of
the libc provider. I believe most people favor this patch and I haven't
seen recent objections.
I didn't find any surprising behaviors, but there are a few that I'd
like to draw attention to:
0. If you initdb with --locale-provider=libc, and don't specify ICU at
any later point, then none of these changes should affect you and
you'll remain on libc. If someone notices otherwise, please let me
know.
1. If you specify --locale-provider=builtin at initdb time, you *must*
specify --locale=C/POSIX, otherwise you get an error.
2. Patch 0004 is possibly out of scope for 16, but it felt consistent
with the other UI changes and low risk. Please try with/without before
objecting.
3. Daniel Verite felt that we should only change the provider from ICU
to "builtin" for the C locale if the provider is defaulting to ICU; not
if it's specified as ICU. I did not differentiate between specifying
ICU and defaulting to ICU because:
a. "libc" unconditionally uses the built-in memcmp() logic for C, it
never actually uses libc
b. If a user really wants the root locale or the en-US-u-va-posix
locale, they can specify those directly
c. I don't see any plausible case where it helps a user to keep
provider=icu when locale=C.
4. Joe Conway and Peter Eisentraut both felt that C.UTF-8 with
provider=icu should not be changed to use the builtin provider, and
instead passed on to ICU. I implemented a compromise where initdb will
change C.UTF-8 to the built-in provider; but CREATE DATABASE/COLLATION
will pass it along to ICU (which may support it as en-US-u-va-posix in
some versions, or may throw an error in other versions). My reasoning
is that initdb is pulling from the environment, and we should try
harder to succeed on any reasonable environmental settings (otherwise
initdb with default settings could fail); whereas we can be more strict
with CREATE DATABASE/COLLATION.
5. For the built-in provider, initdb defaults to UTF-8 rather than
SQL_ASCII. Otherwise, you would be unable to use ICU at all later,
because ICU doesn't support SQL_ASCII.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
Attachments:download all (4 files, .tar.gz)v10-0001-Introduce-collation-provider-builtin.patch (text/x-patch, 43.3K) download
From b9a27c9536c4cc2a748a293fb095e985eec9de7b Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Mon, 1 May 2023 15:38:29 -0700
Subject: [PATCH v10 1/4] Introduce collation provider "builtin".
Only supports locale "C" (or equivalently, "POSIX"). Provides
locale-unaware semantics that are implemented as fast byte operations
in Postgres, independent of the operating system or any provider
libraries.
Equivalent (in semantics and implementation) to the libc provider with
locale "C", except that LC_COLLATE and LC_CTYPE can be set
independently.
Use provider "builtin" for built-in collation "ucs_basic" instead of
libc.
Discussion: https://postgr.es/m/ab925f69-5f9d-f85e-b87c-bd2a44798659@joeconway.com
---
doc/src/sgml/charset.sgml | 89 +++++++++++++++++----
doc/src/sgml/ref/create_collation.sgml | 11 ++-
doc/src/sgml/ref/create_database.sgml | 8 +-
doc/src/sgml/ref/createdb.sgml | 2 +-
doc/src/sgml/ref/initdb.sgml | 7 +-
src/backend/catalog/pg_collation.c | 7 +-
src/backend/commands/collationcmds.c | 103 +++++++++++++++++++++----
src/backend/commands/dbcommands.c | 69 ++++++++++++++---
src/backend/utils/adt/pg_locale.c | 27 ++++++-
src/backend/utils/init/postinit.c | 10 ++-
src/bin/initdb/initdb.c | 24 ++++--
src/bin/initdb/t/001_initdb.pl | 47 +++++++++++
src/bin/pg_dump/pg_dump.c | 49 +++++++++---
src/bin/pg_upgrade/t/002_pg_upgrade.pl | 35 +++++++--
src/bin/psql/describe.c | 4 +-
src/bin/scripts/createdb.c | 2 +-
src/bin/scripts/t/020_createdb.pl | 56 ++++++++++++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_collation.dat | 3 +-
src/include/catalog/pg_collation.h | 3 +
src/test/icu/t/010_database.pl | 9 +++
src/test/regress/expected/collate.out | 25 +++++-
src/test/regress/sql/collate.sql | 10 +++
23 files changed, 519 insertions(+), 83 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgmlindex ed84465996..b38cf82f83 100644--- a/doc/src/sgml/charset.sgml+++ b/doc/src/sgml/charset.sgml@@ -342,22 +342,14 @@ initdb --locale=sv_SE
<title>Locale Providers</title>
<para>
- <productname>PostgreSQL</productname> supports multiple <firstterm>locale- providers</firstterm>. This specifies which library supplies the locale- data. One standard provider name is <literal>libc</literal>, which uses- the locales provided by the operating system C library. These are the- locales used by most tools provided by the operating system. Another- provider is <literal>icu</literal>, which uses the external- ICU<indexterm><primary>ICU</primary></indexterm> library. ICU locales can- only be used if support for ICU was configured when PostgreSQL was built.+ A locale provider specifies which library defines the locale behavior for+ collations and character classifications.
</para>
<para>
The commands and tools that select the locale settings, as described
- above, each have an option to select the locale provider. The examples- shown earlier all use the <literal>libc</literal> provider, which is the- default. Here is an example to initialize a database cluster using the- ICU provider:+ above, each have an option to select the locale provider. Here is an+ example to initialize a database cluster using the ICU provider:
<programlisting>
initdb --locale-provider=icu --icu-locale=en
</programlisting>
@@ -370,12 +362,75 @@ initdb --locale-provider=icu --icu-locale=en
</para>
<para>
- Which locale provider to use depends on individual requirements. For most- basic uses, either provider will give adequate results. For the libc- provider, it depends on what the operating system offers; some operating- systems are better than others. For advanced uses, ICU offers more locale- variants and customization options.+ Regardless of the locale provider, the operating system is still used to+ provide some locale-aware behavior, such as messages (see <xref+ linkend="guc-lc-messages"/>).
</para>
++ <para>+ The available locale providers are listed below.+ </para>++ <sect3 id="locale-provider-builtin">+ <title>Builtin</title>+ <para>+ The <literal>builtin</literal> provider uses simple built-in operations+ which are not locale-aware. Only the <literal>C</literal> (or+ equivalently, <literal>POSIX</literal>) locales are supported for this+ provider.+ </para>+ <para>+ The collation and character classification behavior is equivalent to+ using the <literal>libc</literal> provider with locale+ <literal>C</literal>, except that <literal>LC_COLLATE</literal> and+ <literal>LC_CTYPE</literal> can be set independently.+ </para>+ <note>+ <para>+ When using the <literal>builtin</literal> locale provider, behavior may+ depend on the database encoding.+ </para>+ </note>+ </sect3>+ <sect3 id="locale-provider-icu">+ <title>ICU</title>+ <para>+ The <literal>icu</literal> provider uses the external+ ICU<indexterm><primary>ICU</primary></indexterm>+ library. <productname>PostgreSQL</productname> must have been configured+ with support.+ </para>+ <para>+ ICU provides collation and character classification behavior that is+ independent of the operating system and database encoding, which is+ preferable if you expect to transition to other platforms without any+ change in results. <literal>LC_COLLATE</literal> and+ <literal>LC_CTYPE</literal> can be set independently of the ICU locale.+ </para>+ <note>+ <para>+ For the ICU provider, results may depend on the version of the ICU+ library used, as it is updated to reflect changes in natural language+ over time.+ </para>+ </note>+ </sect3>+ <sect3 id="locale-provider-libc">+ <title>libc</title>+ <para>+ The <literal>libc</literal> provider uses the operating system's C+ library. The collation and character classification behavior is+ controlled by the settings <literal>LC_COLLATE</literal> and+ <literal>LC_CTYPE</literal>, so they cannot be set independently.+ </para>+ <note>+ <para>+ The same locale name may have different behavior on different platforms+ when using the libc provider.+ </para>+ </note>+ </sect3>+
</sect2>
<sect2 id="icu-locales">
<title>ICU Locales</title>
diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgmlindex f6353da5c1..c63a350c1e 100644--- a/doc/src/sgml/ref/create_collation.sgml+++ b/doc/src/sgml/ref/create_collation.sgml@@ -89,6 +89,11 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
and <symbol>LC_CTYPE</symbol> at once. If you specify this,
you cannot specify either of those parameters.
</para>
+ <para>+ If <replaceable>provider</replaceable> is <literal>builtin</literal>,+ then <replaceable>locale</replaceable> must be specified and set to+ either <literal>C</literal> or <literal>POSIX</literal>.+ </para>
</listitem>
</varlistentry>
@@ -120,9 +125,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
<listitem>
<para>
Specifies the provider to use for locale services associated with this
- collation. Possible values are- <literal>icu</literal><indexterm><primary>ICU</primary></indexterm>- (if the server was built with ICU support) or <literal>libc</literal>.+ collation. Possible values are <literal>builtin</literal>,+ <literal>icu</literal><indexterm><primary>ICU</primary></indexterm> (if+ the server was built with ICU support) or <literal>libc</literal>.
<literal>libc</literal> is the default. See <xref
linkend="locale-providers"/> for details.
</para>
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgmlindex 13793bb6b7..5655d6c823 100644--- a/doc/src/sgml/ref/create_database.sgml+++ b/doc/src/sgml/ref/create_database.sgml@@ -148,6 +148,12 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
This is a shortcut for setting <symbol>LC_COLLATE</symbol>
and <symbol>LC_CTYPE</symbol> at once.
</para>
+ <para>+ If <xref linkend="create-database-locale-provider"/> is+ <literal>builtin</literal>, then <replaceable>locale</replaceable>+ must be specified and set to either <literal>C</literal> or+ <literal>POSIX</literal>.+ </para>
<tip>
<para>
The other locale settings <xref linkend="guc-lc-messages"/>, <xref
@@ -212,7 +218,7 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<listitem>
<para>
Specifies the provider to use for the default collation in this
- database. Possible values are+ database. Possible values are <literal>builtin</literal>,
<literal>icu</literal><indexterm><primary>ICU</primary></indexterm>
(if the server was built with ICU support) or <literal>libc</literal>.
By default, the provider is the same as that of the <xref
diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgmlindex e23419ba6c..ee4644818d 100644--- a/doc/src/sgml/ref/createdb.sgml+++ b/doc/src/sgml/ref/createdb.sgml@@ -168,7 +168,7 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
- <term><option>--locale-provider={<literal>libc</literal>|<literal>icu</literal>}</option></term>+ <term><option>--locale-provider={<literal>builtin</literal>|<literal>libc</literal>|<literal>icu</literal>}</option></term>
<listitem>
<para>
Specifies the locale provider for the database's default collation.
diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgmlindex 87945b4b62..96f84dc8ca 100644--- a/doc/src/sgml/ref/initdb.sgml+++ b/doc/src/sgml/ref/initdb.sgml@@ -294,6 +294,11 @@ PostgreSQL documentation
environment that <command>initdb</command> runs in. Locale
support is described in <xref linkend="locale"/>.
</para>
+ <para>+ If <option>--locale-provider</option> is <literal>builtin</literal>,+ <option>--locale</option> must be specified and set to+ <literal>C</literal> (or equivalently, <literal>POSIX</literal>).+ </para>
</listitem>
</varlistentry>
@@ -323,7 +328,7 @@ PostgreSQL documentation
</varlistentry>
<varlistentry id="app-initdb-option-locale-provider">
- <term><option>--locale-provider={<literal>libc</literal>|<literal>icu</literal>}</option></term>+ <term><option>--locale-provider={<literal>builtin</literal>|<literal>libc</literal>|<literal>icu</literal>}</option></term>
<listitem>
<para>
This option sets the locale provider for databases created in the new
diff --git a/src/backend/catalog/pg_collation.c b/src/backend/catalog/pg_collation.cindex fd022e6fc2..0b1ba359b6 100644--- a/src/backend/catalog/pg_collation.c+++ b/src/backend/catalog/pg_collation.c@@ -68,7 +68,12 @@ CollationCreate(const char *collname, Oid collnamespace,
Assert(collname);
Assert(collnamespace);
Assert(collowner);
- Assert((collcollate && collctype) || colliculocale);+ Assert((collprovider == COLLPROVIDER_BUILTIN &&+ !collcollate && !collctype && !colliculocale) ||+ (collprovider == COLLPROVIDER_LIBC &&+ collcollate && collctype && !colliculocale) ||+ (collprovider == COLLPROVIDER_ICU &&+ !collcollate && !collctype && colliculocale));
/*
* Make sure there is no existing collation of same name & encoding.
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.cindex 2969a2bb21..4748946499 100644--- a/src/backend/commands/collationcmds.c+++ b/src/backend/commands/collationcmds.c@@ -66,6 +66,7 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
DefElem *deterministicEl = NULL;
DefElem *rulesEl = NULL;
DefElem *versionEl = NULL;
+ char *builtin_locale = NULL;
char *collcollate;
char *collctype;
char *colliculocale;
@@ -215,7 +216,9 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
if (collproviderstr)
{
- if (pg_strcasecmp(collproviderstr, "icu") == 0)+ if (pg_strcasecmp(collproviderstr, "builtin") == 0)+ collprovider = COLLPROVIDER_BUILTIN;+ else if (pg_strcasecmp(collproviderstr, "icu") == 0)
collprovider = COLLPROVIDER_ICU;
else if (pg_strcasecmp(collproviderstr, "libc") == 0)
collprovider = COLLPROVIDER_LIBC;
@@ -230,7 +233,11 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
if (localeEl)
{
- if (collprovider == COLLPROVIDER_LIBC)+ if (collprovider == COLLPROVIDER_BUILTIN)+ {+ builtin_locale = defGetString(localeEl);+ }+ else if (collprovider == COLLPROVIDER_LIBC)
{
collcollate = defGetString(localeEl);
collctype = defGetString(localeEl);
@@ -245,7 +252,22 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
if (lcctypeEl)
collctype = defGetString(lcctypeEl);
- if (collprovider == COLLPROVIDER_LIBC)+ if (collprovider == COLLPROVIDER_BUILTIN)+ {+ if (!builtin_locale)+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),+ errmsg("parameter \"locale\" must be specified")));++ if (strcmp(builtin_locale, "C") != 0 &&+ strcmp(builtin_locale, "POSIX") != 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("collation provider \"builtin\" does not support locale \"%s\"",+ builtin_locale),+ errhint("The built-in collation provider only supports the \"C\" and \"POSIX\" locales.")));+ }+ else if (collprovider == COLLPROVIDER_LIBC)
{
if (!collcollate)
ereport(ERROR,
@@ -302,7 +324,17 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("ICU rules cannot be specified unless locale provider is ICU")));
- if (collprovider == COLLPROVIDER_ICU)+ if (collprovider == COLLPROVIDER_BUILTIN)+ {+ /*+ * Behavior may be different in different encodings, so set+ * collencoding to the current database encoding. No validation is+ * required, because the "builtin" provider is compatible with any+ * encoding.+ */+ collencoding = GetDatabaseEncoding();+ }+ else if (collprovider == COLLPROVIDER_ICU)
{
#ifdef USE_ICU
/*
@@ -331,7 +363,18 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
}
if (!collversion)
- collversion = get_collation_actual_version(collprovider, collprovider == COLLPROVIDER_ICU ? colliculocale : collcollate);+ {+ char *locale;++ if (collprovider == COLLPROVIDER_ICU)+ locale = colliculocale;+ else if (collprovider == COLLPROVIDER_LIBC)+ locale = collcollate;+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ collversion = get_collation_actual_version(collprovider, locale);+ }
newoid = CollationCreate(collName,
collNamespace,
@@ -406,6 +449,7 @@ AlterCollation(AlterCollationStmt *stmt)
Form_pg_collation collForm;
Datum datum;
bool isnull;
+ char *locale;
char *oldversion;
char *newversion;
ObjectAddress address;
@@ -430,8 +474,20 @@ AlterCollation(AlterCollationStmt *stmt)
datum = SysCacheGetAttr(COLLOID, tup, Anum_pg_collation_collversion, &isnull);
oldversion = isnull ? NULL : TextDatumGetCString(datum);
- datum = SysCacheGetAttrNotNull(COLLOID, tup, collForm->collprovider == COLLPROVIDER_ICU ? Anum_pg_collation_colliculocale : Anum_pg_collation_collcollate);- newversion = get_collation_actual_version(collForm->collprovider, TextDatumGetCString(datum));+ if (collForm->collprovider == COLLPROVIDER_ICU)+ {+ datum = SysCacheGetAttrNotNull(COLLOID, tup, Anum_pg_collation_colliculocale);+ locale = TextDatumGetCString(datum);+ }+ else if (collForm->collprovider == COLLPROVIDER_LIBC)+ {+ datum = SysCacheGetAttrNotNull(COLLOID, tup, Anum_pg_collation_collcollate);+ locale = TextDatumGetCString(datum);+ }+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ newversion = get_collation_actual_version(collForm->collprovider, locale);
/* cannot change from NULL to non-NULL or vice versa */
if ((!oldversion && newversion) || (oldversion && !newversion))
@@ -495,11 +551,18 @@ pg_collation_actual_version(PG_FUNCTION_ARGS)
provider = ((Form_pg_database) GETSTRUCT(dbtup))->datlocprovider;
- datum = SysCacheGetAttrNotNull(DATABASEOID, dbtup,- provider == COLLPROVIDER_ICU ?- Anum_pg_database_daticulocale : Anum_pg_database_datcollate);-- locale = TextDatumGetCString(datum);+ if (provider == COLLPROVIDER_ICU)+ {+ datum = SysCacheGetAttrNotNull(DATABASEOID, dbtup, Anum_pg_database_daticulocale);+ locale = TextDatumGetCString(datum);+ }+ else if (provider == COLLPROVIDER_LIBC)+ {+ datum = SysCacheGetAttrNotNull(DATABASEOID, dbtup, Anum_pg_database_datcollate);+ locale = TextDatumGetCString(datum);+ }+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */
ReleaseSysCache(dbtup);
}
@@ -516,11 +579,19 @@ pg_collation_actual_version(PG_FUNCTION_ARGS)
provider = ((Form_pg_collation) GETSTRUCT(colltp))->collprovider;
Assert(provider != COLLPROVIDER_DEFAULT);
- datum = SysCacheGetAttrNotNull(COLLOID, colltp,- provider == COLLPROVIDER_ICU ?- Anum_pg_collation_colliculocale : Anum_pg_collation_collcollate);- locale = TextDatumGetCString(datum);+ if (provider == COLLPROVIDER_ICU)+ {+ datum = SysCacheGetAttrNotNull(COLLOID, colltp, Anum_pg_collation_colliculocale);+ locale = TextDatumGetCString(datum);+ }+ else if (provider == COLLPROVIDER_LIBC)+ {+ datum = SysCacheGetAttrNotNull(COLLOID, colltp, Anum_pg_collation_collcollate);+ locale = TextDatumGetCString(datum);+ }+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */
ReleaseSysCache(colltp);
}
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.cindex 99d4080ea9..016852644f 100644--- a/src/backend/commands/dbcommands.c+++ b/src/backend/commands/dbcommands.c@@ -909,7 +909,9 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
{
char *locproviderstr = defGetString(dlocprovider);
- if (pg_strcasecmp(locproviderstr, "icu") == 0)+ if (pg_strcasecmp(locproviderstr, "builtin") == 0)+ dblocprovider = COLLPROVIDER_BUILTIN;+ else if (pg_strcasecmp(locproviderstr, "icu") == 0)
dblocprovider = COLLPROVIDER_ICU;
else if (pg_strcasecmp(locproviderstr, "libc") == 0)
dblocprovider = COLLPROVIDER_LIBC;
@@ -1177,9 +1179,17 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
*/
if (src_collversion && !dcollversion)
{
- char *actual_versionstr;+ char *actual_versionstr;+ char *locale;- actual_versionstr = get_collation_actual_version(dblocprovider, dblocprovider == COLLPROVIDER_ICU ? dbiculocale : dbcollate);+ if (dblocprovider == COLLPROVIDER_ICU)+ locale = dbiculocale;+ else if (dblocprovider == COLLPROVIDER_LIBC)+ locale = dbcollate;+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ actual_versionstr = get_collation_actual_version(dblocprovider, locale);
if (!actual_versionstr)
ereport(ERROR,
(errmsg("template database \"%s\" has a collation version, but no actual collation version could be determined",
@@ -1207,7 +1217,18 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
* collation version, which is normally only the case for template0.
*/
if (dbcollversion == NULL)
- dbcollversion = get_collation_actual_version(dblocprovider, dblocprovider == COLLPROVIDER_ICU ? dbiculocale : dbcollate);+ {+ char *locale;++ if (dblocprovider == COLLPROVIDER_ICU)+ locale = dbiculocale;+ else if (dblocprovider == COLLPROVIDER_LIBC)+ locale = dbcollate;+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ dbcollversion = get_collation_actual_version(dblocprovider, locale);+ }
/* Resolve default tablespace for new database */
if (dtablespacename && dtablespacename->arg)
@@ -2403,6 +2424,7 @@ AlterDatabaseRefreshColl(AlterDatabaseRefreshCollStmt *stmt)
ObjectAddress address;
Datum datum;
bool isnull;
+ char *locale;
char *oldversion;
char *newversion;
@@ -2429,10 +2451,24 @@ AlterDatabaseRefreshColl(AlterDatabaseRefreshCollStmt *stmt)
datum = heap_getattr(tuple, Anum_pg_database_datcollversion, RelationGetDescr(rel), &isnull);
oldversion = isnull ? NULL : TextDatumGetCString(datum);
- datum = heap_getattr(tuple, datForm->datlocprovider == COLLPROVIDER_ICU ? Anum_pg_database_daticulocale : Anum_pg_database_datcollate, RelationGetDescr(rel), &isnull);- if (isnull)- elog(ERROR, "unexpected null in pg_database");- newversion = get_collation_actual_version(datForm->datlocprovider, TextDatumGetCString(datum));+ if (datForm->datlocprovider == COLLPROVIDER_ICU)+ {+ datum = heap_getattr(tuple, Anum_pg_database_daticulocale, RelationGetDescr(rel), &isnull);+ if (isnull)+ elog(ERROR, "unexpected null in pg_database");+ locale = TextDatumGetCString(datum);+ }+ else if (datForm->datlocprovider == COLLPROVIDER_LIBC)+ {+ datum = heap_getattr(tuple, Anum_pg_database_datcollate, RelationGetDescr(rel), &isnull);+ if (isnull)+ elog(ERROR, "unexpected null in pg_database");+ locale = TextDatumGetCString(datum);+ }+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ newversion = get_collation_actual_version(datForm->datlocprovider, locale);
/* cannot change from NULL to non-NULL or vice versa */
if ((!oldversion && newversion) || (oldversion && !newversion))
@@ -2617,6 +2653,7 @@ pg_database_collation_actual_version(PG_FUNCTION_ARGS)
HeapTuple tp;
char datlocprovider;
Datum datum;
+ char *locale;
char *version;
tp = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
@@ -2627,8 +2664,20 @@ pg_database_collation_actual_version(PG_FUNCTION_ARGS)
datlocprovider = ((Form_pg_database) GETSTRUCT(tp))->datlocprovider;
- datum = SysCacheGetAttrNotNull(DATABASEOID, tp, datlocprovider == COLLPROVIDER_ICU ? Anum_pg_database_daticulocale : Anum_pg_database_datcollate);- version = get_collation_actual_version(datlocprovider, TextDatumGetCString(datum));+ if (datlocprovider == COLLPROVIDER_ICU)+ {+ datum = SysCacheGetAttrNotNull(DATABASEOID, tp, Anum_pg_database_daticulocale);+ locale = TextDatumGetCString(datum);+ }+ else if (datlocprovider == COLLPROVIDER_LIBC)+ {+ datum = SysCacheGetAttrNotNull(DATABASEOID, tp, Anum_pg_database_datcollate);+ locale = TextDatumGetCString(datum);+ }+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ version = get_collation_actual_version(datlocprovider, locale);
ReleaseSysCache(tp);
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.cindex 31e3b16ae0..d4d7affba6 100644--- a/src/backend/utils/adt/pg_locale.c+++ b/src/backend/utils/adt/pg_locale.c@@ -1228,7 +1228,12 @@ lookup_collation_cache(Oid collation, bool set_flags)
elog(ERROR, "cache lookup failed for collation %u", collation);
collform = (Form_pg_collation) GETSTRUCT(tp);
- if (collform->collprovider == COLLPROVIDER_LIBC)+ if (collform->collprovider == COLLPROVIDER_BUILTIN)+ {+ cache_entry->collate_is_c = true;+ cache_entry->ctype_is_c = true;+ }+ else if (collform->collprovider == COLLPROVIDER_LIBC)
{
Datum datum;
const char *collcollate;
@@ -1281,6 +1286,9 @@ lc_collate_is_c(Oid collation)
static int result = -1;
char *localeptr;
+ if (default_locale.provider == COLLPROVIDER_BUILTIN)+ return true;+
if (default_locale.provider == COLLPROVIDER_ICU)
return false;
@@ -1334,6 +1342,9 @@ lc_ctype_is_c(Oid collation)
static int result = -1;
char *localeptr;
+ if (default_locale.provider == COLLPROVIDER_BUILTIN)+ return true;+
if (default_locale.provider == COLLPROVIDER_ICU)
return false;
@@ -1487,8 +1498,10 @@ pg_newlocale_from_collation(Oid collid)
{
if (default_locale.provider == COLLPROVIDER_ICU)
return &default_locale;
- else+ else if (default_locale.provider == COLLPROVIDER_LIBC)
return (pg_locale_t) 0;
+ else+ elog(ERROR, "cannot open collation with provider \"builtin\"");
}
cache_entry = lookup_collation_cache(collid, false);
@@ -1513,7 +1526,11 @@ pg_newlocale_from_collation(Oid collid)
result.provider = collform->collprovider;
result.deterministic = collform->collisdeterministic;
- if (collform->collprovider == COLLPROVIDER_LIBC)+ if (collform->collprovider == COLLPROVIDER_BUILTIN)+ {+ elog(ERROR, "cannot open collation with provider \"builtin\"");+ }+ else if (collform->collprovider == COLLPROVIDER_LIBC)
{
#ifdef HAVE_LOCALE_T
const char *collcollate;
@@ -1599,6 +1616,7 @@ pg_newlocale_from_collation(Oid collid)
collversionstr = TextDatumGetCString(datum);
+ Assert(collform->collprovider != COLLPROVIDER_BUILTIN);
datum = SysCacheGetAttrNotNull(COLLOID, tp, collform->collprovider == COLLPROVIDER_ICU ? Anum_pg_collation_colliculocale : Anum_pg_collation_collcollate);
actual_versionstr = get_collation_actual_version(collform->collprovider,
@@ -1650,6 +1668,9 @@ get_collation_actual_version(char collprovider, const char *collcollate)
{
char *collversion = NULL;
+ if (collprovider == COLLPROVIDER_BUILTIN)+ return NULL;+
#ifdef USE_ICU
if (collprovider == COLLPROVIDER_ICU)
{
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.cindex 561bd13ed2..12c36d12e6 100644--- a/src/backend/utils/init/postinit.c+++ b/src/backend/utils/init/postinit.c@@ -461,10 +461,18 @@ CheckMyDatabase(const char *name, bool am_superuser, bool override_allow_connect
{
char *actual_versionstr;
char *collversionstr;
+ char *locale;
collversionstr = TextDatumGetCString(datum);
- actual_versionstr = get_collation_actual_version(dbform->datlocprovider, dbform->datlocprovider == COLLPROVIDER_ICU ? iculocale : collate);+ if (dbform->datlocprovider == COLLPROVIDER_ICU)+ locale = iculocale;+ else if (dbform->datlocprovider == COLLPROVIDER_LIBC)+ locale = collate;+ else+ locale = NULL; /* COLLPROVIDER_BUILTIN */++ actual_versionstr = get_collation_actual_version(dbform->datlocprovider, locale);
if (!actual_versionstr)
/* should not happen */
elog(WARNING,
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.cindex 09a5c98cc0..6fc19c8d64 100644--- a/src/bin/initdb/initdb.c+++ b/src/bin/initdb/initdb.c@@ -2472,7 +2472,7 @@ usage(const char *progname)
" set default locale in the respective category for\n"
" new databases (default taken from environment)\n"));
printf(_(" --no-locale equivalent to --locale=C\n"));
- printf(_(" --locale-provider={libc|icu}\n"+ printf(_(" --locale-provider={builtin|libc|icu}\n"
" set default locale provider for new databases\n"));
printf(_(" --pwfile=FILE read password for the new superuser from file\n"));
printf(_(" -T, --text-search-config=CFG\n"
@@ -2622,7 +2622,15 @@ setup_locale_encoding(void)
{
setlocales();
- if (locale_provider == COLLPROVIDER_LIBC &&+ if (locale_provider == COLLPROVIDER_BUILTIN &&+ strcmp(lc_ctype, "C") == 0 &&+ strcmp(lc_collate, "C") == 0 &&+ strcmp(lc_time, "C") == 0 &&+ strcmp(lc_numeric, "C") == 0 &&+ strcmp(lc_monetary, "C") == 0 &&+ strcmp(lc_messages, "C") == 0)+ printf(_("The database cluster will be initialized with no locale.\n"));+ else if (locale_provider == COLLPROVIDER_LIBC &&
strcmp(lc_ctype, lc_collate) == 0 &&
strcmp(lc_ctype, lc_time) == 0 &&
strcmp(lc_ctype, lc_numeric) == 0 &&
@@ -2633,9 +2641,11 @@ setup_locale_encoding(void)
else
{
printf(_("The database cluster will be initialized with this locale configuration:\n"));
- printf(_(" provider: %s\n"), collprovider_name(locale_provider));- if (icu_locale)- printf(_(" ICU locale: %s\n"), icu_locale);+ printf(_(" default collation provider: %s\n"), collprovider_name(locale_provider));+ if (locale_provider == COLLPROVIDER_BUILTIN)+ printf(_(" default collation locale: %s\n"), "C");+ else if (locale_provider == COLLPROVIDER_ICU)+ printf(_(" default collation locale: %s\n"), icu_locale);
printf(_(" LC_COLLATE: %s\n"
" LC_CTYPE: %s\n"
" LC_MESSAGES: %s\n"
@@ -3296,7 +3306,9 @@ main(int argc, char *argv[])
"-c debug_discard_caches=1");
break;
case 15:
- if (strcmp(optarg, "icu") == 0)+ if (strcmp(optarg, "builtin") == 0)+ locale_provider = COLLPROVIDER_BUILTIN;+ else if (strcmp(optarg, "icu") == 0)
locale_provider = COLLPROVIDER_ICU;
else if (strcmp(optarg, "libc") == 0)
locale_provider = COLLPROVIDER_LIBC;
diff --git a/src/bin/initdb/t/001_initdb.pl b/src/bin/initdb/t/001_initdb.plindex fa00bb3dab..157d6acfd4 100644--- a/src/bin/initdb/t/001_initdb.pl+++ b/src/bin/initdb/t/001_initdb.pl@@ -154,6 +154,53 @@ else
'locale provider ICU fails since no ICU support');
}
+command_ok(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', "$tempdir/data6"+ ],+ 'locale provider builtin'+);++command_ok(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',+ "$tempdir/data7"+ ],+ 'locale provider builtin with --locale'+);++command_ok(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', '--lc-collate=C',+ "$tempdir/data8"+ ],+ 'locale provider builtin with --lc-collate'+);++command_ok(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', '--lc-ctype=C',+ "$tempdir/data9"+ ],+ 'locale provider builtin with --lc-ctype'+);++command_fails(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', '--icu-locale=en',+ "$tempdir/dataX"+ ],+ 'fails for locale provider builtin with ICU locale'+);++command_fails(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', '--icu-rules=""',+ "$tempdir/dataX"+ ],+ 'fails for locale provider builtin with ICU rules'+);+
command_fails(
[ 'initdb', '--no-sync', '--locale-provider=xyz', "$tempdir/dataX" ],
'fails for invalid locale provider');
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex 5dab1ba9ea..c75818c3a4 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -3070,7 +3070,9 @@ dumpDatabase(Archive *fout)
}
appendPQExpBufferStr(creaQry, " LOCALE_PROVIDER = ");
- if (datlocprovider[0] == 'c')+ if (datlocprovider[0] == 'b')+ appendPQExpBufferStr(creaQry, "builtin");+ else if (datlocprovider[0] == 'c')
appendPQExpBufferStr(creaQry, "libc");
else if (datlocprovider[0] == 'i')
appendPQExpBufferStr(creaQry, "icu");
@@ -13432,7 +13434,9 @@ dumpCollation(Archive *fout, const CollInfo *collinfo)
fmtQualifiedDumpable(collinfo));
appendPQExpBufferStr(q, "provider = ");
- if (collprovider[0] == 'c')+ if (collprovider[0] == 'b')+ appendPQExpBufferStr(q, "builtin");+ else if (collprovider[0] == 'c')
appendPQExpBufferStr(q, "libc");
else if (collprovider[0] == 'i')
appendPQExpBufferStr(q, "icu");
@@ -13446,13 +13450,42 @@ dumpCollation(Archive *fout, const CollInfo *collinfo)
if (strcmp(PQgetvalue(res, 0, i_collisdeterministic), "f") == 0)
appendPQExpBufferStr(q, ", deterministic = false");
- if (colliculocale != NULL)+ if (collprovider[0] == 'd')
{
+ Assert(colliculocale == NULL);+ Assert(collicurules == NULL);+ Assert(collcollate == NULL);+ Assert(collctype == NULL);++ /* no locale -- cannot be reloaded anyway */+ }+ else if (collprovider[0] == 'b')+ {+ Assert(colliculocale == NULL);+ Assert(collicurules == NULL);+ Assert(collcollate == NULL);+ Assert(collctype == NULL);+ appendPQExpBufferStr(q, ", locale = 'C'");+ }+ else if (collprovider[0] == 'i')+ {+ Assert(colliculocale != NULL);+ Assert(collcollate == NULL);+ Assert(collctype == NULL);+
appendPQExpBufferStr(q, ", locale = ");
appendStringLiteralAH(q, colliculocale, fout);
++ if (collicurules)+ {+ appendPQExpBufferStr(q, ", rules = ");+ appendStringLiteralAH(q, collicurules, fout);+ }
}
- else+ else if (collprovider[0] == 'c')
{
+ Assert(colliculocale == NULL);+ Assert(collicurules == NULL);
Assert(collcollate != NULL);
Assert(collctype != NULL);
@@ -13469,12 +13502,8 @@ dumpCollation(Archive *fout, const CollInfo *collinfo)
appendStringLiteralAH(q, collctype, fout);
}
}
-- if (collicurules)- {- appendPQExpBufferStr(q, ", rules = ");- appendStringLiteralAH(q, collicurules, fout);- }+ else+ pg_fatal("unrecognized collation provider '%c'", collprovider[0]);
/*
* For binary upgrade, carry over the collation version. For normal
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.plindex 41fce089d6..22d9fc10a0 100644--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl@@ -114,22 +114,45 @@ my $original_locale = "C";
my $original_iculocale = "";
my $provider_field = "'c' AS datlocprovider";
my $iculocale_field = "NULL AS daticulocale";
-if ($oldnode->pg_version >= 15 && $ENV{with_icu} eq 'yes')+if ($oldnode->pg_version >= 15)
{
$provider_field = "datlocprovider";
$iculocale_field = "daticulocale";
- $original_provider = "i";- $original_iculocale = "fr-CA";++ if ($ENV{with_icu} eq 'yes')+ {+ $original_provider = "i";+ $original_iculocale = "fr-CA";+ }+}++# use builtin provider instead of libc, if supported+if ($oldnode->pg_version >= 16 && $ENV{with_icu} ne 'yes')+{+ $original_provider = "b";
}
my @initdb_params = @custom_opts;
push @initdb_params, ('--encoding', 'UTF-8');
push @initdb_params, ('--locale', $original_locale);
-if ($original_provider eq "i")++# add --locale-provider, if supported+if ($oldnode->pg_version >= 15)
{
- push @initdb_params, ('--locale-provider', 'icu');- push @initdb_params, ('--icu-locale', 'fr-CA');+ if ($original_provider eq "b")+ {+ push @initdb_params, ('--locale-provider', 'builtin');+ }+ elsif ($original_provider eq "i")+ {+ push @initdb_params, ('--locale-provider', 'icu');+ push @initdb_params, ('--icu-locale', 'fr-CA');+ }+ elsif ($original_provider eq "c")+ {+ push @initdb_params, ('--locale-provider', 'libc');+ }
}
$node_params{extra} = \@initdb_params;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.cindex 9325a46b8f..5642638dfb 100644--- a/src/bin/psql/describe.c+++ b/src/bin/psql/describe.c@@ -932,7 +932,7 @@ listAllDbs(const char *pattern, bool verbose)
gettext_noop("Encoding"));
if (pset.sversion >= 150000)
appendPQExpBuffer(&buf,
- " CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\",\n",+ " CASE d.datlocprovider WHEN 'b' THEN 'builtin' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\",\n",
gettext_noop("Locale Provider"));
else
appendPQExpBuffer(&buf,
@@ -4873,7 +4873,7 @@ listCollations(const char *pattern, bool verbose, bool showSystem)
if (pset.sversion >= 100000)
appendPQExpBuffer(&buf,
- " CASE c.collprovider WHEN 'd' THEN 'default' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\",\n",+ " CASE c.collprovider WHEN 'd' THEN 'default' WHEN 'b' THEN 'builtin' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\",\n",
gettext_noop("Provider"));
else
appendPQExpBuffer(&buf,
diff --git a/src/bin/scripts/createdb.c b/src/bin/scripts/createdb.cindex b4205c4fa5..41a8de659e 100644--- a/src/bin/scripts/createdb.c+++ b/src/bin/scripts/createdb.c@@ -299,7 +299,7 @@ help(const char *progname)
printf(_(" --lc-ctype=LOCALE LC_CTYPE setting for the database\n"));
printf(_(" --icu-locale=LOCALE ICU locale setting for the database\n"));
printf(_(" --icu-rules=RULES ICU rules setting for the database\n"));
- printf(_(" --locale-provider={libc|icu}\n"+ printf(_(" --locale-provider={builtin|libc|icu}\n"
" locale provider for the database's default collation\n"));
printf(_(" -O, --owner=OWNER database user to own the new database\n"));
printf(_(" -S, --strategy=STRATEGY database creation strategy wal_log or file_copy\n"));
diff --git a/src/bin/scripts/t/020_createdb.pl b/src/bin/scripts/t/020_createdb.plindex d0830a4a1d..f1d6db0f48 100644--- a/src/bin/scripts/t/020_createdb.pl+++ b/src/bin/scripts/t/020_createdb.pl@@ -94,6 +94,62 @@ else
'create database with ICU fails since no ICU support');
}
+$node->command_ok(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ 'tbuiltin1'+ ],+ 'create database with provider "builtin"'+);++$node->command_ok(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ '--locale=C', 'tbuiltin2'+ ],+ 'create database with provider "builtin" and locale "C"'+);++$node->command_ok(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ '--lc-collate=C', 'tbuiltin3'+ ],+ 'create database with provider "builtin" and LC_COLLATE=C'+);++$node->command_ok(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ '--lc-ctype=C', 'tbuiltin4'+ ],+ 'create database with provider "builtin" and LC_CTYPE=C'+);++$node->command_fails(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ '--icu-locale=en', 'tbuiltin5'+ ],+ 'create database with provider "builtin" and ICU_LOCALE="en"'+);++$node->command_fails(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ '--icu-rules=""', 'tbuiltin6'+ ],+ 'create database with provider "builtin" and ICU_RULES=""'+);++$node->command_fails(+ [+ 'createdb', '-T', 'template1', '--locale-provider=builtin',+ '--locale=C', 'tbuiltin7'+ ],+ 'create database with provider "builtin" not matching template'+);+
$node->command_fails([ 'createdb', 'foobar1' ],
'fails if database already exists');
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.hindex c784937a0e..52e17baafa 100644--- a/src/include/catalog/catversion.h+++ b/src/include/catalog/catversion.h@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202305211+#define CATALOG_VERSION_NO 202306081
#endif
diff --git a/src/include/catalog/pg_collation.dat b/src/include/catalog/pg_collation.datindex b6a69d1d42..cfb53807ed 100644--- a/src/include/catalog/pg_collation.dat+++ b/src/include/catalog/pg_collation.dat@@ -24,8 +24,7 @@
collname => 'POSIX', collprovider => 'c', collencoding => '-1',
collcollate => 'POSIX', collctype => 'POSIX' },
{ oid => '962', descr => 'sorts by Unicode code point',
- collname => 'ucs_basic', collprovider => 'c', collencoding => '6',- collcollate => 'C', collctype => 'C' },+ collname => 'ucs_basic', collprovider => 'b', collencoding => '6' },
{ oid => '963',
descr => 'sorts using the Unicode Collation Algorithm with default settings',
collname => 'unicode', collprovider => 'i', collencoding => '-1',
diff --git a/src/include/catalog/pg_collation.h b/src/include/catalog/pg_collation.hindex bfa3568451..4009c4ec93 100644--- a/src/include/catalog/pg_collation.h+++ b/src/include/catalog/pg_collation.h@@ -65,6 +65,7 @@ DECLARE_UNIQUE_INDEX_PKEY(pg_collation_oid_index, 3085, CollationOidIndexId, on
#ifdef EXPOSE_TO_CLIENT_CODE
#define COLLPROVIDER_DEFAULT 'd'
+#define COLLPROVIDER_BUILTIN 'b'
#define COLLPROVIDER_ICU 'i'
#define COLLPROVIDER_LIBC 'c'
@@ -73,6 +74,8 @@ collprovider_name(char c)
{
switch (c)
{
+ case COLLPROVIDER_BUILTIN:+ return "builtin";
case COLLPROVIDER_ICU:
return "icu";
case COLLPROVIDER_LIBC:
diff --git a/src/test/icu/t/010_database.pl b/src/test/icu/t/010_database.plindex d3901f5d3f..26f71e1155 100644--- a/src/test/icu/t/010_database.pl+++ b/src/test/icu/t/010_database.pl@@ -63,5 +63,14 @@ like(
qr/ERROR: ICU locale must be specified/,
"ICU locale must be specified for ICU provider: error message");
+my ($ret, $stdout, $stderr) = $node1->psql('postgres',+ q{CREATE DATABASE dbicu LOCALE_PROVIDER builtin LOCALE 'C' TEMPLATE dbicu}+);+isnt($ret, 0,+ "locale provider must match template: exit code not 0");+like(+ $stderr,+ qr/ERROR: new locale provider \(builtin\) does not match locale provider of the template database \(icu\)/,+ "locale provider must match template: error message");
done_testing();
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.outindex 0649564485..5b28de1b47 100644--- a/src/test/regress/expected/collate.out+++ b/src/test/regress/expected/collate.out@@ -650,6 +650,27 @@ EXPLAIN (COSTS OFF)
(3 rows)
-- CREATE/DROP COLLATION
+CREATE COLLATION builtin_c ( PROVIDER = builtin, LOCALE = "C" );+CREATE COLLATION builtin_posix ( PROVIDER = builtin, LOCALE = "POSIX" );+SELECT b FROM collate_test1 ORDER BY b COLLATE builtin_c;+ b +-----+ ABD+ Abc+ abc+ bbc+(4 rows)++CREATE COLLATION builtin2 ( PROVIDER = builtin ); -- fails+ERROR: parameter "locale" must be specified+CREATE COLLATION builtin2 ( PROVIDER = builtin, LOCALE = "en_US" ); -- fails+ERROR: collation provider "builtin" does not support locale "en_US"+HINT: The built-in collation provider only supports the "C" and "POSIX" locales.+CREATE COLLATION builtin2 ( PROVIDER = builtin, LC_CTYPE = "C", LC_COLLATE = "C" ); -- fails+ERROR: parameter "locale" must be specified+CREATE COLLATION builtin2 ( PROVIDER = builtin, LOCALE = "POSIX", LC_CTYPE = "POSIX" ); -- fails+ERROR: conflicting or redundant options+DETAIL: LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.
CREATE COLLATION mycoll1 FROM "C";
CREATE COLLATION mycoll2 ( LC_COLLATE = "POSIX", LC_CTYPE = "POSIX" );
CREATE COLLATION mycoll3 FROM "default"; -- intentionally unsupported
@@ -754,7 +775,7 @@ DETAIL: FROM cannot be specified together with any other options.
-- must get rid of them.
--
DROP SCHEMA collate_tests CASCADE;
-NOTICE: drop cascades to 19 other objects+NOTICE: drop cascades to 21 other objects
DETAIL: drop cascades to table collate_test1
drop cascades to table collate_test_like
drop cascades to table collate_test2
@@ -771,6 +792,8 @@ drop cascades to function dup(anyelement)
drop cascades to table collate_test20
drop cascades to table collate_test21
drop cascades to table collate_test22
+drop cascades to collation builtin_c+drop cascades to collation builtin_posix
drop cascades to collation mycoll2
drop cascades to table collate_test23
drop cascades to view collate_on_int
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sqlindex c3d40fc195..01d5c69fe4 100644--- a/src/test/regress/sql/collate.sql+++ b/src/test/regress/sql/collate.sql@@ -244,6 +244,16 @@ EXPLAIN (COSTS OFF)
-- CREATE/DROP COLLATION
+CREATE COLLATION builtin_c ( PROVIDER = builtin, LOCALE = "C" );+CREATE COLLATION builtin_posix ( PROVIDER = builtin, LOCALE = "POSIX" );++SELECT b FROM collate_test1 ORDER BY b COLLATE builtin_c;++CREATE COLLATION builtin2 ( PROVIDER = builtin ); -- fails+CREATE COLLATION builtin2 ( PROVIDER = builtin, LOCALE = "en_US" ); -- fails+CREATE COLLATION builtin2 ( PROVIDER = builtin, LC_CTYPE = "C", LC_COLLATE = "C" ); -- fails+CREATE COLLATION builtin2 ( PROVIDER = builtin, LOCALE = "POSIX", LC_CTYPE = "POSIX" ); -- fails+
CREATE COLLATION mycoll1 FROM "C";
CREATE COLLATION mycoll2 ( LC_COLLATE = "POSIX", LC_CTYPE = "POSIX" );
CREATE COLLATION mycoll3 FROM "default"; -- intentionally unsupported
--
2.34.1
v10-0002-ICU-for-locale-C-automatically-use-builtin-provi.patch (text/x-patch, 14.4K) download
From c48d91ab80e151093dbae0ada6a9691a2f112c5a Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Mon, 8 May 2023 13:48:01 -0700
Subject: [PATCH v10 2/4] ICU: for locale "C", automatically use "builtin"
provider instead.
Postgres expects locale C to be optimizable to simple locale-unaware
byte operations; while ICU does not recognize the locale "C" at all,
and falls back to the root locale.
If the user specifies locale "C" when creating a new collation or a
new database with the ICU provider, automatically switch it to the
"builtin" provider.
If provider is libc, behavior is unchanged.
Discussion: https://postgr.es/m/ab925f69-5f9d-f85e-b87c-bd2a44798659@joeconway.com
---
doc/src/sgml/charset.sgml | 6 +++
doc/src/sgml/ref/create_collation.sgml | 6 +++
doc/src/sgml/ref/create_database.sgml | 5 +++
doc/src/sgml/ref/createdb.sgml | 5 +++
doc/src/sgml/ref/initdb.sgml | 23 ++++++-----
src/backend/commands/collationcmds.c | 20 ++++++++++
src/backend/commands/dbcommands.c | 21 ++++++++++
src/bin/initdb/initdb.c | 17 ++++++++
src/bin/initdb/t/001_initdb.pl | 39 +++++++++++++++++++
src/bin/scripts/t/020_createdb.pl | 18 +++++++++
.../regress/expected/collate.icu.utf8.out | 14 +++++--
src/test/regress/sql/collate.icu.utf8.sql | 6 +++
12 files changed, 168 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgmlindex b38cf82f83..9a7f9263ec 100644--- a/doc/src/sgml/charset.sgml+++ b/doc/src/sgml/charset.sgml@@ -407,6 +407,12 @@ initdb --locale-provider=icu --icu-locale=en
change in results. <literal>LC_COLLATE</literal> and
<literal>LC_CTYPE</literal> can be set independently of the ICU locale.
</para>
+ <para>+ The ICU provider does not accept the <literal>C</literal>+ locale. Commands that create collations or database with the+ <literal>icu</literal> provider and ICU locale <literal>C</literal> use+ the provider <literal>builtin</literal> instead.+ </para>
<note>
<para>
For the ICU provider, results may depend on the version of the ICU
diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgmlindex c63a350c1e..67ea247b39 100644--- a/doc/src/sgml/ref/create_collation.sgml+++ b/doc/src/sgml/ref/create_collation.sgml@@ -131,6 +131,12 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
<literal>libc</literal> is the default. See <xref
linkend="locale-providers"/> for details.
</para>
+ <para>+ If the provider is <literal>icu</literal> and the locale is+ <literal>C</literal> or <literal>POSIX</literal>, the provider is+ automatically set to <literal>builtin</literal>; as the ICU provider+ doesn't support an ICU locale of <literal>C</literal>.+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgmlindex 5655d6c823..17550c19bb 100644--- a/doc/src/sgml/ref/create_database.sgml+++ b/doc/src/sgml/ref/create_database.sgml@@ -196,6 +196,11 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<para>
Specifies the ICU locale ID if the ICU locale provider is used.
</para>
+ <para>+ If specified as <literal>C</literal> or <literal>POSIX</literal>, the+ provider is automatically set to <literal>builtin</literal>, as the+ ICU provider doesn't support an ICU locale of <literal>C</literal>.+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgmlindex ee4644818d..5352cce744 100644--- a/doc/src/sgml/ref/createdb.sgml+++ b/doc/src/sgml/ref/createdb.sgml@@ -154,6 +154,11 @@ PostgreSQL documentation
Specifies the ICU locale ID to be used in this database, if the
ICU locale provider is selected.
</para>
+ <para>+ If specified as <literal>C</literal> or <literal>POSIX</literal>, the+ provider is automatically set to <literal>builtin</literal>, as the+ ICU provider doesn't support an ICU locale of <literal>C</literal>.+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgmlindex 96f84dc8ca..d9620e5931 100644--- a/doc/src/sgml/ref/initdb.sgml+++ b/doc/src/sgml/ref/initdb.sgml@@ -94,13 +94,14 @@ PostgreSQL documentation
<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.+ locale services if the server was built with ICU support. 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. If+ <literal>--icu-locale</literal> is <literal>C</literal> (or equivalently+ <literal>POSIX</literal>), the <literal>builtin</literal> provider will be+ selected instead of ICU.
</para>
<para>
@@ -109,8 +110,7 @@ PostgreSQL documentation
<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.+ settings.
</para>
<para>
@@ -250,6 +250,11 @@ PostgreSQL documentation
Specifies the ICU locale when the ICU provider is used. Locale support
is described in <xref linkend="locale"/>.
</para>
+ <para>+ If specified as <literal>C</literal> or <literal>POSIX</literal>, the+ provider is automatically set to <literal>builtin</literal>, as the+ ICU provider doesn't support an ICU locale of <literal>C</literal>.+ </para>
<para>
If this option is not specified, the locale is inherited from the
environment in which <command>initdb</command> runs. The environment's
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.cindex 4748946499..3b2ba38250 100644--- a/src/backend/commands/collationcmds.c+++ b/src/backend/commands/collationcmds.c@@ -252,6 +252,26 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
if (lcctypeEl)
collctype = defGetString(lcctypeEl);
+ /*+ * Postgres defines the "C" (and equivalently, "POSIX") locales to be+ * optimizable to byte operations (memcmp(), pg_ascii_tolower(),+ * etc.); use the builtin provider instead of ICU if the locale is C.+ *+ * Don't change the provider during binary upgrade.+ */+ if (!IsBinaryUpgrade && collprovider == COLLPROVIDER_ICU &&+ colliculocale &&+ (strcmp(colliculocale, "C") == 0 ||+ strcmp(colliculocale, "POSIX") == 0))+ {+ ereport(NOTICE,+ (errmsg("using locale provider \"builtin\" for ICU locale \"%s\"",+ colliculocale)));+ builtin_locale = colliculocale;+ colliculocale = NULL;+ collprovider = COLLPROVIDER_BUILTIN;+ }+
if (collprovider == COLLPROVIDER_BUILTIN)
{
if (!builtin_locale)
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.cindex 016852644f..5c8caeac2d 100644--- a/src/backend/commands/dbcommands.c+++ b/src/backend/commands/dbcommands.c@@ -1043,6 +1043,27 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
check_encoding_locale_matches(encoding, dbcollate, dbctype);
+ /*+ * Postgres defines the "C" (and equivalently, "POSIX") locales to be+ * optimizable to byte operations (memcmp(), pg_ascii_tolower(), etc.);+ * use the builtin provider instead of ICU if the locale is C.+ *+ * Don't change the provider during binary upgrade or when both the+ * provider and ICU locale are unchanged from the template.+ */+ if (!IsBinaryUpgrade && dblocprovider == COLLPROVIDER_ICU &&+ dbiculocale &&+ (dblocprovider != src_locprovider ||+ strcmp(dbiculocale, src_iculocale) != 0) &&+ (strcmp(dbiculocale, "C") == 0 || strcmp(dbiculocale, "POSIX") == 0))+ {+ ereport(NOTICE,+ (errmsg("using locale provider \"builtin\" for ICU locale \"%s\"",+ dbiculocale)));+ dbiculocale = NULL;+ dblocprovider = COLLPROVIDER_BUILTIN;+ }+
if (dblocprovider == COLLPROVIDER_ICU)
{
if (!(is_encoding_supported_by_icu(encoding)))
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.cindex 6fc19c8d64..897521cf35 100644--- a/src/bin/initdb/initdb.c+++ b/src/bin/initdb/initdb.c@@ -2394,6 +2394,23 @@ setlocales(void)
lc_messages = locale;
}
+ /*+ * Postgres defines the "C" (and equivalently, "POSIX") locales to be+ * optimizable to byte operations (memcmp(), pg_ascii_tolower(), etc.);+ * use the builtin provider instead of ICU if the locale is C.+ */+ if (icu_locale && locale_provider == COLLPROVIDER_ICU &&+ (strcmp(icu_locale, "C") == 0 ||+ strcmp(icu_locale, "POSIX") == 0 ||+ strncmp(icu_locale, "C.", 2) == 0 ||+ strncmp(icu_locale, "POSIX.", 6) == 0))+ {+ pg_log_info("using locale provider \"builtin\" for ICU locale \"%s\"",+ icu_locale);+ icu_locale = NULL;+ locale_provider = COLLPROVIDER_BUILTIN;+ }+
/*
* canonicalize locale names, and obtain any missing values from our
* current environment
diff --git a/src/bin/initdb/t/001_initdb.pl b/src/bin/initdb/t/001_initdb.plindex 157d6acfd4..f3f832a413 100644--- a/src/bin/initdb/t/001_initdb.pl+++ b/src/bin/initdb/t/001_initdb.pl@@ -111,6 +111,45 @@ if ($ENV{with_icu} eq 'yes')
],
'option --icu-locale');
+ # transformed to provider=builtin+ command_ok(+ [+ 'initdb', '--no-sync',+ '--locale-provider=icu', '--icu-locale=C',+ "$tempdir/data4a"+ ],+ 'option --icu-locale=C');++ # transformed to provider=builtin+ command_ok(+ [+ 'initdb', '--no-sync',+ '--locale-provider=icu', '--icu-locale=C',+ '--locale=C',+ "$tempdir/data4b"+ ],+ 'option --icu-locale=C --locale=C');++ # transformed to provider=builtin+ command_ok(+ [+ 'initdb', '--no-sync',+ '--locale-provider=icu', '--icu-locale=C',+ '--lc-collate=C',+ "$tempdir/data4c"+ ],+ 'option --icu-locale=C --lc-collate=C');++ # transformed to provider=builtin+ command_ok(+ [+ 'initdb', '--no-sync',+ '--locale-provider=icu', '--icu-locale=C',+ '--lc-ctype=C',+ "$tempdir/data4d"+ ],+ 'option --icu-locale=C --lc-ctype=C');+
command_fails_like(
[
'initdb', '--no-sync',
diff --git a/src/bin/scripts/t/020_createdb.pl b/src/bin/scripts/t/020_createdb.plindex f1d6db0f48..121ad5112b 100644--- a/src/bin/scripts/t/020_createdb.pl+++ b/src/bin/scripts/t/020_createdb.pl@@ -86,6 +86,24 @@ if ($ENV{with_icu} eq 'yes')
],
'create database with icu locale from template database with icu provider'
);
++ # transformed into provider "builtin"+ $node->command_ok(+ [+ 'createdb', '-T', 'template0', '--locale-provider=icu',+ '--icu-locale=C', 'test_builtin_icu1'+ ],+ 'create database with provider "icu" and ICU_LOCALE="C"'+ );++ # transformed into provider "builtin"+ $node->command_ok(+ [+ 'createdb', '-T', 'template0', '--locale-provider=icu',+ '--icu-locale=C', '--lc-ctype=C', 'test_builtin_icu2'+ ],+ 'create database with provider "icu" and ICU_LOCALE="C" and LC_CTYPE=C'+ );
}
else
{
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.outindex 00dee24549..5ba8f75558 100644--- a/src/test/regress/expected/collate.icu.utf8.out+++ b/src/test/regress/expected/collate.icu.utf8.out@@ -1035,6 +1035,9 @@ BEGIN
END
$$;
RESET client_min_messages;
+-- uses "builtin" provider instead+CREATE COLLATION testc (provider = icu, locale='C');+NOTICE: using locale provider "builtin" for ICU locale "C"
CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, needs "locale"
ERROR: parameter "locale" must be specified
SET icu_validation_level = ERROR;
@@ -1058,8 +1061,11 @@ SELECT collname FROM pg_collation WHERE collname LIKE 'test%' ORDER BY 1;
test0
test1
test5
-(3 rows)+ testc+(4 rows)+DROP COLLATION test1;+CREATE COLLATION test1 (provider = icu, locale = 'und');
ALTER COLLATION test1 RENAME TO test11;
ALTER COLLATION test0 RENAME TO test11; -- fail
ERROR: collation "test11" already exists in schema "collate_tests"
@@ -1079,7 +1085,8 @@ SELECT collname, nspname, obj_description(pg_collation.oid, 'pg_collation')
test0 | collate_tests | US English
test11 | test_schema |
test5 | collate_tests |
-(3 rows)+ testc | collate_tests | +(4 rows)
DROP COLLATION test0, test_schema.test11, test5;
DROP COLLATION test0; -- fail
@@ -1089,7 +1096,8 @@ NOTICE: collation "test0" does not exist, skipping
SELECT collname FROM pg_collation WHERE collname LIKE 'test%';
collname
----------
-(0 rows)+ testc+(1 row)
DROP SCHEMA test_schema;
DROP ROLE regress_test_role;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sqlindex a8001b4b8e..122c166966 100644--- a/src/test/regress/sql/collate.icu.utf8.sql+++ b/src/test/regress/sql/collate.icu.utf8.sql@@ -375,6 +375,9 @@ $$;
RESET client_min_messages;
+-- uses "builtin" provider instead+CREATE COLLATION testc (provider = icu, locale='C');+
CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, needs "locale"
SET icu_validation_level = ERROR;
CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); -- fails
@@ -388,6 +391,9 @@ CREATE COLLATION test5 FROM test0;
SELECT collname FROM pg_collation WHERE collname LIKE 'test%' ORDER BY 1;
+DROP COLLATION test1;+CREATE COLLATION test1 (provider = icu, locale = 'und');+
ALTER COLLATION test1 RENAME TO test11;
ALTER COLLATION test0 RENAME TO test11; -- fail
ALTER COLLATION test1 RENAME TO test22; -- fail
--
2.34.1
v10-0003-CREATE-DATABASE-make-LOCALE-apply-to-all-collati.patch (text/x-patch, 30.5K) download
From 7ee9bfc7ded5906f5b27390061513bbab3d87555 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Tue, 25 Apr 2023 15:01:55 -0700
Subject: [PATCH v10 3/4] CREATE DATABASE: make LOCALE apply to all collation
providers.
Previously, LOCALE applied only to LC_COLLATE and LC_CTYPE. That could
lead to confusion when the provider is implicit, such as when it is
inherited from the template database, or when ICU was made default at
initdb time in commit 27b62377b4.
Reverts incomplete fix 5cd1a5af4d.
Discussion: https://postgr.es/m/3391932.1682107209@sss.pgh.pa.us
---
doc/src/sgml/ref/create_collation.sgml | 23 +++++---
doc/src/sgml/ref/create_database.sgml | 57 +++++++++++++++----
doc/src/sgml/ref/createdb.sgml | 5 +-
doc/src/sgml/ref/initdb.sgml | 7 ++-
src/backend/commands/collationcmds.c | 2 +-
src/backend/commands/dbcommands.c | 51 +++++++++++++++--
src/bin/initdb/initdb.c | 27 +++++++--
src/bin/initdb/t/001_initdb.pl | 28 +++++----
src/bin/pg_dump/pg_dump.c | 55 ++++++++++++++----
src/bin/scripts/createdb.c | 13 ++---
src/bin/scripts/t/020_createdb.pl | 28 +++++++--
src/test/icu/t/010_database.pl | 22 ++++---
.../regress/expected/collate.icu.utf8.out | 22 +++----
13 files changed, 251 insertions(+), 89 deletions(-)
diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgmlindex 67ea247b39..48b62f77cb 100644--- a/doc/src/sgml/ref/create_collation.sgml+++ b/doc/src/sgml/ref/create_collation.sgml@@ -85,9 +85,16 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
<listitem>
<para>
- This is a shortcut for setting <symbol>LC_COLLATE</symbol>- and <symbol>LC_CTYPE</symbol> at once. If you specify this,- you cannot specify either of those parameters.+ The locale name for this collation. See <xref+ linkend="collation-managing-create-libc"/> and <xref+ linkend="collation-managing-create-icu"/> for details.+ </para>+ <para>+ If <replaceable>provider</replaceable> is <literal>libc</literal>, this+ is a shortcut for setting <symbol>LC_COLLATE</symbol> and+ <symbol>LC_CTYPE</symbol> at once. If you specify+ <replaceable>locale</replaceable>, you cannot specify either of those+ parameters.
</para>
<para>
If <replaceable>provider</replaceable> is <literal>builtin</literal>,
@@ -102,8 +109,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
<listitem>
<para>
- Use the specified operating system locale for- the <symbol>LC_COLLATE</symbol> locale category.+ If <replaceable>provider</replaceable> is <literal>libc</literal>, use+ the specified operating system locale for the+ <symbol>LC_COLLATE</symbol> locale category.
</para>
</listitem>
</varlistentry>
@@ -113,8 +121,9 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
<listitem>
<para>
- Use the specified operating system locale for- the <symbol>LC_CTYPE</symbol> locale category.+ If <replaceable>provider</replaceable> is <literal>libc</literal>, use+ the specified operating system locale for the <symbol>LC_CTYPE</symbol>+ locale category.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgmlindex 17550c19bb..9f20c93267 100644--- a/doc/src/sgml/ref/create_database.sgml+++ b/doc/src/sgml/ref/create_database.sgml@@ -145,8 +145,22 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<term><replaceable class="parameter">locale</replaceable></term>
<listitem>
<para>
- This is a shortcut for setting <symbol>LC_COLLATE</symbol>- and <symbol>LC_CTYPE</symbol> at once.+ Sets the default collation order and character classification in the+ new database. Collation affects the sort order applied to strings,+ e.g., in queries with ORDER BY, as well as the order used in indexes+ on text columns. Character classification affects the categorization+ of characters, e.g., lower, - upper and digit. Also sets the+ associated aspects of the operating system environment,+ <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>. The+ default is the same setting as the template database. See <xref+ linkend="collation-managing-create-libc"/> and <xref+ linkend="collation-managing-create-icu"/> for details.+ </para>+ <para>+ Can be overridden by setting <xref+ linkend="create-database-lc-collate"/>, <xref+ linkend="create-database-lc-ctype"/>, or <xref+ linkend="create-database-icu-locale"/> individually.
</para>
<para>
If <xref linkend="create-database-locale-provider"/> is
@@ -170,11 +184,17 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<term><replaceable class="parameter">lc_collate</replaceable></term>
<listitem>
<para>
- Collation order (<literal>LC_COLLATE</literal>) to use in the new database.- This affects the sort order applied to strings, e.g., in queries with- ORDER BY, as well as the order used in indexes on text columns.- The default is to use the collation order of the template database.- See below for additional restrictions.+ Sets <literal>LC_COLLATE</literal> in the database server's operating+ system environment. The default is the setting of <xref+ linkend="create-database-locale"/> if specified; otherwise the same+ setting as the template database. See below for additional+ restrictions.+ </para>+ <para>+ If <xref linkend="create-database-locale-provider"/> is+ <literal>libc</literal>, also sets the default collation order to use+ in the new database, overriding the setting <xref+ linkend="create-database-locale"/>.
</para>
</listitem>
</varlistentry>
@@ -182,10 +202,17 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<term><replaceable class="parameter">lc_ctype</replaceable></term>
<listitem>
<para>
- Character classification (<literal>LC_CTYPE</literal>) to use in the new- database. This affects the categorization of characters, e.g., lower,- upper and digit. The default is to use the character classification of- the template database. See below for additional restrictions.+ Sets <literal>LC_CTYPE</literal> in the database server's operating+ system environment. The default is the setting of <xref+ linkend="create-database-locale"/> if specified; otherwise the same+ setting as the template database. See below for additional+ restrictions.+ </para>+ <para>+ If <xref linkend="create-database-locale-provider"/> is+ <literal>libc</literal>, also sets the default character+ classification to use in the new database, overriding the setting+ <xref linkend="create-database-locale"/>.
</para>
</listitem>
</varlistentry>
@@ -194,7 +221,13 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<term><replaceable class="parameter">icu_locale</replaceable></term>
<listitem>
<para>
- Specifies the ICU locale ID if the ICU locale provider is used.+ Specifies the ICU locale (see <xref+ linkend="collation-managing-create-icu"/>) for the database default+ collation order and character classification, overriding the setting+ <xref linkend="create-database-locale"/>. The <xref+ linkend="create-database-locale-provider"/> must be ICU. The default+ is the setting of <xref linkend="create-database-locale"/> if+ specified; otherwise the same setting as the template database.
</para>
<para>
If specified as <literal>C</literal> or <literal>POSIX</literal>, the
diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgmlindex 5352cce744..94937e1d6d 100644--- a/doc/src/sgml/ref/createdb.sgml+++ b/doc/src/sgml/ref/createdb.sgml@@ -124,7 +124,10 @@ PostgreSQL documentation
<listitem>
<para>
Specifies the locale to be used in this database. This is equivalent
- to specifying both <option>--lc-collate</option> and <option>--lc-ctype</option>.+ to specifying <option>--lc-collate</option>,+ <option>--lc-ctype</option>, and <option>--icu-locale</option> to the+ same value. Some locales are only valid for ICU and must be set with+ <option>--icu-locale</option>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgmlindex d9620e5931..5eabaaec3a 100644--- a/doc/src/sgml/ref/initdb.sgml+++ b/doc/src/sgml/ref/initdb.sgml@@ -116,9 +116,10 @@ PostgreSQL documentation
<para>
To choose a different locale for the cluster, use the option
<option>--locale</option>. There are also individual options
- <option>--lc-*</option> (see below) to set values for the individual locale- categories. Note that inconsistent settings for different locale- categories can give nonsensical results, so this should be used with care.+ <option>--lc-*</option> and <option>--icu-locale</option> (see below) to+ set values for the individual locale categories. Note that inconsistent+ settings for different locale categories can give nonsensical results, so+ this should be used with care.
</para>
<para>
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.cindex 3b2ba38250..58316ec19e 100644--- a/src/backend/commands/collationcmds.c+++ b/src/backend/commands/collationcmds.c@@ -318,7 +318,7 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
if (langtag && strcmp(colliculocale, langtag) != 0)
{
ereport(NOTICE,
- (errmsg("using standard form \"%s\" for locale \"%s\"",+ (errmsg("using standard form \"%s\" for ICU locale \"%s\"",
langtag, colliculocale)));
colliculocale = langtag;
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.cindex 5c8caeac2d..3d53e29c7e 100644--- a/src/backend/commands/dbcommands.c+++ b/src/backend/commands/dbcommands.c@@ -710,6 +710,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
char *dbname = stmt->dbname;
char *dbowner = NULL;
const char *dbtemplate = NULL;
+ char *builtin_locale = NULL;
char *dbcollate = NULL;
char *dbctype = NULL;
char *dbiculocale = NULL;
@@ -894,6 +895,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
}
if (dlocale && dlocale->arg)
{
+ builtin_locale = defGetString(dlocale);
dbcollate = defGetString(dlocale);
dbctype = defGetString(dlocale);
}
@@ -1018,8 +1020,16 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
dbctype = src_ctype;
if (dblocprovider == '\0')
dblocprovider = src_locprovider;
+ if (builtin_locale == NULL && dblocprovider == COLLPROVIDER_BUILTIN &&+ src_locprovider == COLLPROVIDER_BUILTIN)+ builtin_locale = "C";
if (dbiculocale == NULL && dblocprovider == COLLPROVIDER_ICU)
- dbiculocale = src_iculocale;+ {+ if (dlocale && dlocale->arg)+ dbiculocale = defGetString(dlocale);+ else+ dbiculocale = src_iculocale;+ }
if (dbicurules == NULL && dblocprovider == COLLPROVIDER_ICU)
dbicurules = src_icurules;
@@ -1033,12 +1043,14 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
if (!check_locale(LC_COLLATE, dbcollate, &canonname))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("invalid locale name: \"%s\"", dbcollate)));+ errmsg("invalid LC_COLLATE locale name: \"%s\"", dbcollate),+ errhint("If the locale name is specific to ICU, use ICU_LOCALE.")));
dbcollate = canonname;
if (!check_locale(LC_CTYPE, dbctype, &canonname))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("invalid locale name: \"%s\"", dbctype)));+ errmsg("invalid LC_CTYPE locale name: \"%s\"", dbctype),+ errhint("If the locale name is specific to ICU, use ICU_LOCALE.")));
dbctype = canonname;
check_encoding_locale_matches(encoding, dbcollate, dbctype);
@@ -1060,11 +1072,38 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
ereport(NOTICE,
(errmsg("using locale provider \"builtin\" for ICU locale \"%s\"",
dbiculocale)));
+ builtin_locale = dbiculocale;
dbiculocale = NULL;
dblocprovider = COLLPROVIDER_BUILTIN;
}
- if (dblocprovider == COLLPROVIDER_ICU)+ if (dblocprovider == COLLPROVIDER_BUILTIN)+ {+ /* can happen if template is a different provider */+ if (builtin_locale == NULL)+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("LOCALE must be specified")));++ if (dbiculocale)+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),+ errmsg("ICU locale cannot be specified unless locale provider is ICU")));++ if (dbicurules)+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),+ errmsg("ICU rules cannot be specified unless locale provider is ICU")));++ if (strcmp(builtin_locale, "C") != 0 &&+ strcmp(builtin_locale, "POSIX") != 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("locale provider \"builtin\" does not support locale \"%s\"",+ builtin_locale),+ errhint("The built-in locale provider only supports the \"C\" and \"POSIX\" locales.")));+ }+ else if (dblocprovider == COLLPROVIDER_ICU)
{
if (!(is_encoding_supported_by_icu(encoding)))
ereport(ERROR,
@@ -1079,7 +1118,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
if (!dbiculocale)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("ICU locale must be specified")));+ errmsg("LOCALE or ICU_LOCALE must be specified")));
/*
* During binary upgrade, or when the locale came from the template
@@ -1094,7 +1133,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
if (langtag && strcmp(dbiculocale, langtag) != 0)
{
ereport(NOTICE,
- (errmsg("using standard form \"%s\" for locale \"%s\"",+ (errmsg("using standard form \"%s\" for ICU locale \"%s\"",
langtag, dbiculocale)));
dbiculocale = langtag;
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.cindex 897521cf35..0a9307df2b 100644--- a/src/bin/initdb/initdb.c+++ b/src/bin/initdb/initdb.c@@ -2163,7 +2163,11 @@ check_locale_name(int category, const char *locale, char **canonname)
if (res == NULL)
{
if (*locale)
- pg_fatal("invalid locale name \"%s\"", locale);+ {+ pg_log_error("invalid locale name \"%s\"", locale);+ pg_log_error_hint("If the locale name is specific to ICU, use --icu-locale.");+ exit(1);+ }
else
{
/*
@@ -2375,8 +2379,9 @@ static void
setlocales(void)
{
char *canonname;
+ char *builtin_locale = locale;- /* set empty lc_* values to locale config if set */+ /* set empty lc_* and iculocale values to locale config if set */
if (locale)
{
@@ -2392,6 +2397,8 @@ setlocales(void)
lc_monetary = locale;
if (!lc_messages)
lc_messages = locale;
+ if (!icu_locale && locale_provider == COLLPROVIDER_ICU)+ icu_locale = locale;
}
/*
@@ -2407,6 +2414,7 @@ setlocales(void)
{
pg_log_info("using locale provider \"builtin\" for ICU locale \"%s\"",
icu_locale);
+ builtin_locale = "C";
icu_locale = NULL;
locale_provider = COLLPROVIDER_BUILTIN;
}
@@ -2434,7 +2442,15 @@ setlocales(void)
lc_messages = canonname;
#endif
- if (locale_provider == COLLPROVIDER_ICU)+ if (locale_provider == COLLPROVIDER_BUILTIN)+ {+ if (!builtin_locale)+ pg_fatal("The parameter --locale must be specified for the built-in provider.");+ if (strcmp(builtin_locale, "C") != 0 &&+ strcmp(builtin_locale, "POSIX") != 0)+ pg_fatal("The built-in locale provider only supports the \"C\" and \"POSIX\" locales.");+ }+ else if (locale_provider == COLLPROVIDER_ICU)
{
char *langtag;
@@ -2687,7 +2703,9 @@ setup_locale_encoding(void)
* If ctype_enc=SQL_ASCII, it's compatible with any encoding. ICU does
* not support SQL_ASCII, so select UTF-8 instead.
*/
- if (locale_provider == COLLPROVIDER_ICU && ctype_enc == PG_SQL_ASCII)+ if ((locale_provider == COLLPROVIDER_BUILTIN ||+ locale_provider == COLLPROVIDER_ICU)+ && ctype_enc == PG_SQL_ASCII)
ctype_enc = PG_UTF8;
if (ctype_enc == -1)
@@ -3294,7 +3312,6 @@ main(int argc, char *argv[])
break;
case 8:
locale = "C";
- locale_provider = COLLPROVIDER_LIBC;
break;
case 9:
pwfilename = pg_strdup(optarg);
diff --git a/src/bin/initdb/t/001_initdb.pl b/src/bin/initdb/t/001_initdb.plindex f3f832a413..9b0c2202a3 100644--- a/src/bin/initdb/t/001_initdb.pl+++ b/src/bin/initdb/t/001_initdb.pl@@ -193,11 +193,19 @@ else
'locale provider ICU fails since no ICU support');
}
-command_ok(+command_fails(
[
'initdb', '--no-sync', '--locale-provider=builtin', "$tempdir/data6"
],
- 'locale provider builtin'+ 'locale provider builtin without --locale'+);++command_fails(+ [+ 'initdb', '--no-sync', '--locale-provider=builtin', '--locale=en',+ "$tempdir/data6"+ ],+ 'locale provider builtin with invalid --locale'
);
command_ok(
@@ -210,32 +218,32 @@ command_ok(
command_ok(
[
- 'initdb', '--no-sync', '--locale-provider=builtin', '--lc-collate=C',- "$tempdir/data8"+ 'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',+ '--lc-collate=C', "$tempdir/data8"
],
'locale provider builtin with --lc-collate'
);
command_ok(
[
- 'initdb', '--no-sync', '--locale-provider=builtin', '--lc-ctype=C',- "$tempdir/data9"+ 'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',+ '--lc-ctype=C', "$tempdir/data9"
],
'locale provider builtin with --lc-ctype'
);
command_fails(
[
- 'initdb', '--no-sync', '--locale-provider=builtin', '--icu-locale=en',- "$tempdir/dataX"+ 'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',+ '--icu-locale=en', "$tempdir/dataX"
],
'fails for locale provider builtin with ICU locale'
);
command_fails(
[
- 'initdb', '--no-sync', '--locale-provider=builtin', '--icu-rules=""',- "$tempdir/dataX"+ 'initdb', '--no-sync', '--locale-provider=builtin', '--locale=C',+ '--icu-rules=""', "$tempdir/dataX"
],
'fails for locale provider builtin with ICU rules'
);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex c75818c3a4..8dbbd87610 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -3080,13 +3080,10 @@ dumpDatabase(Archive *fout)
pg_fatal("unrecognized locale provider: %s",
datlocprovider);
- if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)- {- appendPQExpBufferStr(creaQry, " LOCALE = ");- appendStringLiteralAH(creaQry, collate, fout);- }- else+ if (datlocprovider[0] == 'b')
{
+ appendPQExpBufferStr(creaQry, " LOCALE = 'C'");+
if (strlen(collate) > 0)
{
appendPQExpBufferStr(creaQry, " LC_COLLATE = ");
@@ -3098,15 +3095,51 @@ dumpDatabase(Archive *fout)
appendStringLiteralAH(creaQry, ctype, fout);
}
}
- if (iculocale)+ else if (datlocprovider[0] == 'i')
{
- appendPQExpBufferStr(creaQry, " ICU_LOCALE = ");+ Assert(iculocale);++ appendPQExpBufferStr(creaQry, " LOCALE = ");
appendStringLiteralAH(creaQry, iculocale, fout);
++ if (strlen(collate) > 0)+ {+ appendPQExpBufferStr(creaQry, " LC_COLLATE = ");+ appendStringLiteralAH(creaQry, collate, fout);+ }+ if (strlen(ctype) > 0)+ {+ appendPQExpBufferStr(creaQry, " LC_CTYPE = ");+ appendStringLiteralAH(creaQry, ctype, fout);+ }+ if (icurules)+ {+ appendPQExpBufferStr(creaQry, " ICU_RULES = ");+ appendStringLiteralAH(creaQry, icurules, fout);+ }
}
- if (icurules)+ else if (datlocprovider[0] == 'c')
{
- appendPQExpBufferStr(creaQry, " ICU_RULES = ");- appendStringLiteralAH(creaQry, icurules, fout);+ Assert(!iculocale);++ if (strlen(collate) > 0 && strcmp(collate, ctype) == 0)+ {+ appendPQExpBufferStr(creaQry, " LOCALE = ");+ appendStringLiteralAH(creaQry, collate, fout);+ }+ else+ {+ if (strlen(collate) > 0)+ {+ appendPQExpBufferStr(creaQry, " LC_COLLATE = ");+ appendStringLiteralAH(creaQry, collate, fout);+ }+ if (strlen(ctype) > 0)+ {+ appendPQExpBufferStr(creaQry, " LC_CTYPE = ");+ appendStringLiteralAH(creaQry, ctype, fout);+ }+ }
}
/*
diff --git a/src/bin/scripts/createdb.c b/src/bin/scripts/createdb.cindex 41a8de659e..8f8995964c 100644--- a/src/bin/scripts/createdb.c+++ b/src/bin/scripts/createdb.c@@ -164,14 +164,6 @@ main(int argc, char *argv[])
exit(1);
}
- if (locale)- {- if (!lc_ctype)- lc_ctype = locale;- if (!lc_collate)- lc_collate = locale;- }-
if (encoding)
{
if (pg_char_to_encoding(encoding) < 0)
@@ -219,6 +211,11 @@ main(int argc, char *argv[])
appendPQExpBuffer(&sql, " STRATEGY %s", fmtId(strategy));
if (template)
appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
+ if (locale)+ {+ appendPQExpBufferStr(&sql, " LOCALE ");+ appendStringLiteralConn(&sql, locale, conn);+ }
if (lc_collate)
{
appendPQExpBufferStr(&sql, " LC_COLLATE ");
diff --git a/src/bin/scripts/t/020_createdb.pl b/src/bin/scripts/t/020_createdb.plindex 121ad5112b..3f5719c913 100644--- a/src/bin/scripts/t/020_createdb.pl+++ b/src/bin/scripts/t/020_createdb.pl@@ -115,7 +115,7 @@ else
$node->command_ok(
[
'createdb', '-T', 'template0', '--locale-provider=builtin',
- 'tbuiltin1'+ '--locale=C', 'tbuiltin1'
],
'create database with provider "builtin"'
);
@@ -131,7 +131,7 @@ $node->command_ok(
$node->command_ok(
[
'createdb', '-T', 'template0', '--locale-provider=builtin',
- '--lc-collate=C', 'tbuiltin3'+ '--locale=C', '--lc-collate=C', 'tbuiltin3'
],
'create database with provider "builtin" and LC_COLLATE=C'
);
@@ -139,7 +139,7 @@ $node->command_ok(
$node->command_ok(
[
'createdb', '-T', 'template0', '--locale-provider=builtin',
- '--lc-ctype=C', 'tbuiltin4'+ '--locale=C', '--lc-ctype=C', 'tbuiltin4'
],
'create database with provider "builtin" and LC_CTYPE=C'
);
@@ -168,6 +168,22 @@ $node->command_fails(
'create database with provider "builtin" not matching template'
);
+$node->command_fails(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ 'tbuiltin8'+ ],+ 'create database with provider "builtin" and locale unspecified'+);++$node->command_fails(+ [+ 'createdb', '-T', 'template0', '--locale-provider=builtin',+ '--locale=en', 'tbuiltin8'+ ],+ 'create database with provider "builtin" and locale=en'+);+
$node->command_fails([ 'createdb', 'foobar1' ],
'fails if database already exists');
@@ -184,7 +200,7 @@ ALTER TABLE tab_foobar owner to role_foobar;
CREATE POLICY pol_foobar ON tab_foobar FOR ALL TO role_foobar;');
$node->issues_sql_like(
[ 'createdb', '-l', 'C', '-T', 'foobar2', 'foobar3' ],
- qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2/,+ qr/statement: CREATE DATABASE foobar3 TEMPLATE foobar2 LOCALE 'C'/,
'create database with template');
($ret, $stdout, $stderr) = $node->psql(
'foobar3',
@@ -211,7 +227,7 @@ $node->command_checks_all(
1,
[qr/^$/],
[
- qr/^createdb: error: database creation failed: ERROR: invalid locale name|^createdb: error: database creation failed: ERROR: new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s+ qr/^createdb: error: database creation failed: ERROR: invalid LC_COLLATE locale name|^createdb: error: database creation failed: ERROR: new collation \(foo'; SELECT '1\) is incompatible with the collation of the template database/s
],
'createdb with incorrect --lc-collate');
$node->command_checks_all(
@@ -219,7 +235,7 @@ $node->command_checks_all(
1,
[qr/^$/],
[
- qr/^createdb: error: database creation failed: ERROR: invalid locale name|^createdb: error: database creation failed: ERROR: new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s+ qr/^createdb: error: database creation failed: ERROR: invalid LC_CTYPE locale name|^createdb: error: database creation failed: ERROR: new LC_CTYPE \(foo'; SELECT '1\) is incompatible with the LC_CTYPE of the template database/s
],
'createdb with incorrect --lc-ctype');
diff --git a/src/test/icu/t/010_database.pl b/src/test/icu/t/010_database.plindex 26f71e1155..6d19488afc 100644--- a/src/test/icu/t/010_database.pl+++ b/src/test/icu/t/010_database.pl@@ -51,17 +51,23 @@ b),
'sort by explicit collation upper first');
-# Test error cases in CREATE DATABASE involving locale-related options--my ($ret, $stdout, $stderr) = $node1->psql('postgres',- q{CREATE DATABASE dbicu LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8}+# Test that LOCALE='C' works for ICU+my $ret1 = $node1->psql('postgres',+ q{CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE 'C' TEMPLATE template0 ENCODING UTF8}
);
-isnt($ret, 0,- "ICU locale must be specified for ICU provider: exit code not 0");+is($ret1, 0,+ "C locale works for ICU");++# Test that ICU-specific locale string must be specified with ICU_LOCALE,+# not LOCALE+my ($ret2, $stdout, $stderr) = $node1->psql('postgres',+ q{CREATE DATABASE dbicu3 LOCALE_PROVIDER icu LOCALE '@colStrength=primary' TEMPLATE template0 ENCODING UTF8});+isnt($ret2, 0,+ "ICU-specific locale must be specified with ICU_LOCALE: exit code not 0");
like(
$stderr,
- qr/ERROR: ICU locale must be specified/,- "ICU locale must be specified for ICU provider: error message");+ qr/ERROR: invalid LC_COLLATE locale name/,+ "ICU-specific locale must be specified with ICU_LOCALE: error message");
my ($ret, $stdout, $stderr) = $node1->psql('postgres',
q{CREATE DATABASE dbicu LOCALE_PROVIDER builtin LOCALE 'C' TEMPLATE dbicu}
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.outindex 5ba8f75558..9e49c0d9a7 100644--- a/src/test/regress/expected/collate.icu.utf8.out+++ b/src/test/regress/expected/collate.icu.utf8.out@@ -1202,9 +1202,9 @@ SELECT 'coté' < 'côte' COLLATE "und-x-icu", 'coté' > 'côte' COLLATE testcoll
(1 row)
CREATE COLLATION testcoll_lower_first (provider = icu, locale = '@colCaseFirst=lower');
-NOTICE: using standard form "und-u-kf-lower" for locale "@colCaseFirst=lower"+NOTICE: using standard form "und-u-kf-lower" for ICU locale "@colCaseFirst=lower"
CREATE COLLATION testcoll_upper_first (provider = icu, locale = '@colCaseFirst=upper');
-NOTICE: using standard form "und-u-kf-upper" for locale "@colCaseFirst=upper"+NOTICE: using standard form "und-u-kf-upper" for ICU locale "@colCaseFirst=upper"
SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcoll_upper_first;
?column? | ?column?
----------+----------
@@ -1212,7 +1212,7 @@ SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcol
(1 row)
CREATE COLLATION testcoll_shifted (provider = icu, locale = '@colAlternate=shifted');
-NOTICE: using standard form "und-u-ka-shifted" for locale "@colAlternate=shifted"+NOTICE: using standard form "und-u-ka-shifted" for ICU locale "@colAlternate=shifted"
SELECT 'de-luge' < 'deanza' COLLATE "und-x-icu", 'de-luge' > 'deanza' COLLATE testcoll_shifted;
?column? | ?column?
----------+----------
@@ -1229,12 +1229,12 @@ SELECT 'A-21' > 'A-123' COLLATE "und-x-icu", 'A-21' < 'A-123' COLLATE testcoll_n
(1 row)
CREATE COLLATION testcoll_error1 (provider = icu, locale = '@colNumeric=lower');
-NOTICE: using standard form "und-u-kn-lower" for locale "@colNumeric=lower"+NOTICE: using standard form "und-u-kn-lower" for ICU locale "@colNumeric=lower"
ERROR: could not open collator for locale "und-u-kn-lower": U_ILLEGAL_ARGUMENT_ERROR
-- test that attributes not handled by icu_set_collation_attributes()
-- (handled by ucol_open() directly) also work
CREATE COLLATION testcoll_de_phonebook (provider = icu, locale = 'de@collation=phonebook');
-NOTICE: using standard form "de-u-co-phonebk" for locale "de@collation=phonebook"+NOTICE: using standard form "de-u-co-phonebk" for ICU locale "de@collation=phonebook"
SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE testcoll_de_phonebook;
?column? | ?column?
----------+----------
@@ -1243,7 +1243,7 @@ SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE tes
-- rules
CREATE COLLATION testcoll_rules1 (provider = icu, locale = '', rules = '&a < g');
-NOTICE: using standard form "und" for locale ""+NOTICE: using standard form "und" for ICU locale ""
CREATE TABLE test7 (a text);
-- example from https://unicode-org.github.io/icu/userguide/collation/customization/#syntax
INSERT INTO test7 VALUES ('Abernathy'), ('apple'), ('bird'), ('Boston'), ('Graham'), ('green');
@@ -1271,13 +1271,13 @@ SELECT * FROM test7 ORDER BY a COLLATE testcoll_rules1;
DROP TABLE test7;
CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!');
-NOTICE: using standard form "und" for locale ""+NOTICE: using standard form "und" for ICU locale ""
ERROR: could not open collator for locale "und" with rules "!!wrong!!": U_INVALID_FORMAT_ERROR
-- nondeterministic collations
CREATE COLLATION ctest_det (provider = icu, locale = '', deterministic = true);
-NOTICE: using standard form "und" for locale ""+NOTICE: using standard form "und" for ICU locale ""
CREATE COLLATION ctest_nondet (provider = icu, locale = '', deterministic = false);
-NOTICE: using standard form "und" for locale ""+NOTICE: using standard form "und" for ICU locale ""
CREATE TABLE test6 (a int, b text);
-- same string in different normal forms
INSERT INTO test6 VALUES (1, U&'\00E4bc');
@@ -1327,9 +1327,9 @@ SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet;
(2 rows)
CREATE COLLATION case_sensitive (provider = icu, locale = '');
-NOTICE: using standard form "und" for locale ""+NOTICE: using standard form "und" for ICU locale ""
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
-NOTICE: using standard form "und-u-ks-level2" for locale "@colStrength=secondary"+NOTICE: using standard form "und-u-ks-level2" for ICU locale "@colStrength=secondary"
SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
?column? | ?column?
----------+----------
--
2.34.1
v10-0004-CREATE-COLLATION-default-provider.patch (text/x-patch, 7.9K) download
From ea72d9f8baae263af0f7ce1b80bf624e0496a571 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Wed, 24 May 2023 09:53:02 -0700
Subject: [PATCH v10 4/4] CREATE COLLATION default provider.
If the LC_COLLATE or LC_CTYPE are specified for a new collation, the
default provider is libc. Otherwise, the default provider is the same
as the provider for the database default collation.
Previously, the default provider was always libc.
---
contrib/citext/expected/create_index_acl.out | 2 +-
contrib/citext/sql/create_index_acl.sql | 2 +-
doc/src/sgml/ref/create_collation.sgml | 12 +++++++++---
src/backend/commands/collationcmds.c | 7 ++++++-
src/test/regress/expected/collate.linux.utf8.out | 10 +++++-----
src/test/regress/sql/collate.linux.utf8.sql | 10 +++++-----
6 files changed, 27 insertions(+), 16 deletions(-)
diff --git a/contrib/citext/expected/create_index_acl.out b/contrib/citext/expected/create_index_acl.outindex 33be13a92d..765867d36d 100644--- a/contrib/citext/expected/create_index_acl.out+++ b/contrib/citext/expected/create_index_acl.out@@ -43,7 +43,7 @@ REVOKE ALL ON FUNCTION s.index_row_if FROM PUBLIC;
-- Even for an empty table, CREATE INDEX checks ii_Predicate permissions.
GRANT EXECUTE ON FUNCTION s.index_row_if TO regress_minimal;
-- Non-extension, non-function objects.
-CREATE COLLATION s.coll (LOCALE="C");+CREATE COLLATION s.coll (PROVIDER=libc, LOCALE="C");
CREATE TABLE s.x (y s.citext);
ALTER TABLE s.x OWNER TO regress_minimal;
-- Empty-table DefineIndex()
diff --git a/contrib/citext/sql/create_index_acl.sql b/contrib/citext/sql/create_index_acl.sqlindex 10b5225569..e338ac8799 100644--- a/contrib/citext/sql/create_index_acl.sql+++ b/contrib/citext/sql/create_index_acl.sql@@ -45,7 +45,7 @@ REVOKE ALL ON FUNCTION s.index_row_if FROM PUBLIC;
-- Even for an empty table, CREATE INDEX checks ii_Predicate permissions.
GRANT EXECUTE ON FUNCTION s.index_row_if TO regress_minimal;
-- Non-extension, non-function objects.
-CREATE COLLATION s.coll (LOCALE="C");+CREATE COLLATION s.coll (PROVIDER=libc, LOCALE="C");
CREATE TABLE s.x (y s.citext);
ALTER TABLE s.x OWNER TO regress_minimal;
-- Empty-table DefineIndex()
diff --git a/doc/src/sgml/ref/create_collation.sgml b/doc/src/sgml/ref/create_collation.sgmlindex 48b62f77cb..2b25eff44e 100644--- a/doc/src/sgml/ref/create_collation.sgml+++ b/doc/src/sgml/ref/create_collation.sgml@@ -136,9 +136,15 @@ CREATE COLLATION [ IF NOT EXISTS ] <replaceable>name</replaceable> FROM <replace
Specifies the provider to use for locale services associated with this
collation. Possible values are <literal>builtin</literal>,
<literal>icu</literal><indexterm><primary>ICU</primary></indexterm> (if
- the server was built with ICU support) or <literal>libc</literal>.- <literal>libc</literal> is the default. See <xref- linkend="locale-providers"/> for details.+ the server was built with ICU support) or <literal>libc</literal>. See+ <xref linkend="locale-providers"/> for details.+ </para>+ <para>+ If <replaceable>lc_colllate</replaceable> or+ <replaceable>lc_ctype</replaceable> is specified, the default is+ <literal>libc</literal>; otherwise, the default is the same as the+ provider for the database default collation (see <xref+ linkend="sql-createdatabase"/>).
</para>
<para>
If the provider is <literal>icu</literal> and the locale is
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.cindex 58316ec19e..f2264000cb 100644--- a/src/backend/commands/collationcmds.c+++ b/src/backend/commands/collationcmds.c@@ -229,7 +229,12 @@ DefineCollation(ParseState *pstate, List *names, List *parameters, bool if_not_e
collproviderstr)));
}
else
- collprovider = COLLPROVIDER_LIBC;+ {+ if (lccollateEl || lcctypeEl)+ collprovider = COLLPROVIDER_LIBC;+ else+ collprovider = default_locale.provider;+ }
if (localeEl)
{
diff --git a/src/test/regress/expected/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.outindex 01664f7c1b..588198d13e 100644--- a/src/test/regress/expected/collate.linux.utf8.out+++ b/src/test/regress/expected/collate.linux.utf8.out@@ -1026,7 +1026,7 @@ CREATE SCHEMA test_schema;
-- We need to do this this way to cope with varying names for encodings:
do $$
BEGIN
- EXECUTE 'CREATE COLLATION test0 (locale = ' ||+ EXECUTE 'CREATE COLLATION test0 (provider = libc, locale = ' ||
quote_literal((SELECT datcollate FROM pg_database WHERE datname = current_database())) || ');';
END
$$;
@@ -1034,7 +1034,7 @@ CREATE COLLATION test0 FROM "C"; -- fail, duplicate name
ERROR: collation "test0" already exists
CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped
NOTICE: collation "test0" already exists, skipping
-CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped+CREATE COLLATION IF NOT EXISTS test0 (provider = libc, locale = 'foo'); -- ok, skipped
NOTICE: collation "test0" for encoding "UTF8" already exists, skipping
do $$
BEGIN
@@ -1046,7 +1046,7 @@ END
$$;
CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype
ERROR: parameter "lc_ctype" must be specified
-CREATE COLLATION testx (locale = 'nonsense'); -- fail+CREATE COLLATION testx (provider = libc, locale = 'nonsense'); -- fail
ERROR: could not create locale "nonsense": No such file or directory
DETAIL: The operating system could not find any locale data for the locale name "nonsense".
CREATE COLLATION test4 FROM nonsense;
@@ -1166,8 +1166,8 @@ SELECT * FROM collate_test2 ORDER BY b COLLATE UCS_BASIC;
-- nondeterministic collations
-- (not supported with libc provider)
-CREATE COLLATION ctest_det (locale = 'en_US.utf8', deterministic = true);-CREATE COLLATION ctest_nondet (locale = 'en_US.utf8', deterministic = false);+CREATE COLLATION ctest_det (provider = libc, locale = 'en_US.utf8', deterministic = true);+CREATE COLLATION ctest_nondet (provider = libc, locale = 'en_US.utf8', deterministic = false);
ERROR: nondeterministic collations not supported with this provider
-- cleanup
SET client_min_messages TO warning;
diff --git a/src/test/regress/sql/collate.linux.utf8.sql b/src/test/regress/sql/collate.linux.utf8.sqlindex 132d13af0a..2d031293d1 100644--- a/src/test/regress/sql/collate.linux.utf8.sql+++ b/src/test/regress/sql/collate.linux.utf8.sql@@ -358,13 +358,13 @@ CREATE SCHEMA test_schema;
-- We need to do this this way to cope with varying names for encodings:
do $$
BEGIN
- EXECUTE 'CREATE COLLATION test0 (locale = ' ||+ EXECUTE 'CREATE COLLATION test0 (provider = libc, locale = ' ||
quote_literal((SELECT datcollate FROM pg_database WHERE datname = current_database())) || ');';
END
$$;
CREATE COLLATION test0 FROM "C"; -- fail, duplicate name
CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped
-CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped+CREATE COLLATION IF NOT EXISTS test0 (provider = libc, locale = 'foo'); -- ok, skipped
do $$
BEGIN
EXECUTE 'CREATE COLLATION test1 (lc_collate = ' ||
@@ -374,7 +374,7 @@ BEGIN
END
$$;
CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype
-CREATE COLLATION testx (locale = 'nonsense'); -- fail+CREATE COLLATION testx (provider = libc, locale = 'nonsense'); -- fail
CREATE COLLATION test4 FROM nonsense;
CREATE COLLATION test5 FROM test0;
@@ -455,8 +455,8 @@ SELECT * FROM collate_test2 ORDER BY b COLLATE UCS_BASIC;
-- nondeterministic collations
-- (not supported with libc provider)
-CREATE COLLATION ctest_det (locale = 'en_US.utf8', deterministic = true);-CREATE COLLATION ctest_nondet (locale = 'en_US.utf8', deterministic = false);+CREATE COLLATION ctest_det (provider = libc, locale = 'en_US.utf8', deterministic = true);+CREATE COLLATION ctest_nondet (provider = libc, locale = 'en_US.utf8', deterministic = false);
-- cleanup
--
2.34.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pgsql@j-davis.com, andrew@tao11.riddles.org.uk, mail@joeconway.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, daniel@manitou-mail.org, peter.eisentraut@enterprisedb.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Order changes in PG16 since ICU introduction
In-Reply-To: <73f97db9a37fd281bd5077ce16383cd8006b1f59.camel@j-davis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox