public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v2 3/8] wip: meson: add install-{docs,doc-html,doc-man} targets
45+ messages / 11 participants
[nested] [flat]
* [PATCH v2 3/8] wip: meson: add install-{docs,doc-html,doc-man} targets
@ 2023-03-15 23:43 Andres Freund <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Andres Freund @ 2023-03-15 23:43 UTC (permalink / raw)
---
meson.build | 2 +-
doc/src/sgml/meson.build | 67 +++++++++++++++++++++++++++++++++++++++-
2 files changed, 67 insertions(+), 2 deletions(-)
diff --git a/meson.build b/meson.build
index 84fe2c3d4c3..33dd5b43ed5 100644
--- a/meson.build
+++ b/meson.build
@@ -504,7 +504,7 @@ dir_man = get_option('mandir')
# FIXME: These used to be separately configurable - worth adding?
dir_doc = get_option('datadir') / 'doc' / 'postgresql'
-dir_doc_html = dir_doc
+dir_doc_html = dir_doc / 'html'
dir_locale = get_option('localedir')
diff --git a/doc/src/sgml/meson.build b/doc/src/sgml/meson.build
index e6fe124c7bc..f24acebb0fe 100644
--- a/doc/src/sgml/meson.build
+++ b/doc/src/sgml/meson.build
@@ -1,6 +1,7 @@
# Copyright (c) 2022-2023, PostgreSQL Global Development Group
docs = []
+installdocs = []
alldocs = []
doc_generated = []
@@ -120,8 +121,21 @@ if xsltproc_bin.found()
)
alldocs += html
- # build multi-page html docs as part of docs target
+ install_doc_html = custom_target('install-html',
+ output: 'install-html', input: html,
+ command: [
+ python, install_files, '--prefix', dir_prefix,
+ '--install-dir-contents', dir_doc_html, '@INPUT@'],
+ depends: html,
+ build_always_stale: true,
+ build_by_default: false,
+ )
+ alias_target('install-doc-html', install_doc_html)
+
+ # build and install multi-page html docs as part of docs target
docs += html
+ installdocs += install_doc_html
+
htmlhelp = custom_target('htmlhelp',
input: ['stylesheet-hh.xsl', postgres_full_xml],
@@ -208,6 +222,20 @@ if xsltproc_bin.found()
build_by_default: false,
)
alldocs += man
+
+ install_doc_man = custom_target('install-man',
+ output: 'install-man', input: man,
+ command: [
+ python, install_files, '--prefix', dir_prefix,
+ '--install-dirs', dir_man, '@INPUT@'],
+ build_always_stale: true,
+ build_by_default: false,
+ )
+ alias_target('install-doc-man', install_doc_man)
+
+ # even though we don't want to build man pages as part of 'docs', we do want
+ # to install them as part of install-docs
+ installdocs += install_doc_man
endif
@@ -262,10 +290,47 @@ if pandoc.found() and xsltproc_bin.found()
alldocs += postgres_epub
endif
+
+##
+## Experimental Texinfo targets
+##
+
+db2x_xsltproc = find_program('db2x_xsltproc', native: true, required: false)
+db2x_texixml = find_program('db2x_texixml', native: true, required: false)
+makeinfo = find_program('makeinfo', native: true, required: false)
+
+if db2x_texixml.found() and db2x_xsltproc.found() and makeinfo.found()
+ postgres_texixml = custom_target('postgres.texixml',
+ input: postgres_full_xml,
+ output: 'postgres.texixml',
+ command: [db2x_xsltproc, '-s', 'texi',
+ '-g', 'output-file=postgres',
+ '@INPUT@', '-o', '@OUTPUT@'],
+ build_by_default: false,
+ )
+ postgres_texi = custom_target('postgres.texi',
+ input: postgres_texixml,
+ output: 'postgres.texi',
+ command: [db2x_texixml, '--encoding=utf-8', '@INPUT@',
+ '--output-dir=@OUTDIR@'],
+ build_by_default: false,
+ )
+ postgres_info = custom_target('postgres.info',
+ input: postgres_texi,
+ output: 'postgres.info',
+ command: [makeinfo, '--enable-encoding', '--no-split', '--no-validate',
+ '@INPUT@', '-o', '@OUTPUT0@'],
+ build_by_default: false,
+ )
+ alldocs += postgres_info
+endif
+
+
if docs.length() == 0
run_target('docs', command: [missing, 'xsltproc'])
else
alias_target('docs', docs)
+ alias_target('install-docs', installdocs)
endif
if alldocs.length() == 0
--
2.38.0
--az2jhiapsr25u55d
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v2-0004-meson-add-install-quiet-world-targets.patch"
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:09 Sandro Santilli <[email protected]>
0 siblings, 2 replies; 45+ messages in thread
From: Sandro Santilli @ 2023-04-21 17:09 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
On Fri, Apr 21, 2023 at 11:48:51AM -0400, Tom Lane wrote:
> "Regina Obe" <[email protected]> writes:
>
> > https://trac.osgeo.org/postgis/ticket/5375
>
> If they actually are using locale C, I would say this is a bug.
> That should designate memcmp sorting and nothing else.
Sounds like a bug to me. This is happening with a PostgreSQL cluster
created and served by a build of commit c04c6c5d6f :
=# select version();
PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
=# show lc_collate;
C
=# select '+' < '-';
f
=# select '+' < '-' collate "C";
t
I don't know if it should matter but also:
=# show lc_messages;
C
--strk;
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:14 Peter Eisentraut <[email protected]>
parent: Sandro Santilli <[email protected]>
1 sibling, 4 replies; 45+ messages in thread
From: Peter Eisentraut @ 2023-04-21 17:14 UTC (permalink / raw)
To: Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; +Cc: Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
On 21.04.23 19:09, Sandro Santilli wrote:
> On Fri, Apr 21, 2023 at 11:48:51AM -0400, Tom Lane wrote:
>> "Regina Obe" <[email protected]> writes:
>>
>>> https://trac.osgeo.org/postgis/ticket/5375
>>
>> If they actually are using locale C, I would say this is a bug.
>> That should designate memcmp sorting and nothing else.
>
> Sounds like a bug to me. This is happening with a PostgreSQL cluster
> created and served by a build of commit c04c6c5d6f :
>
> =# select version();
> PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
> =# show lc_collate;
> C
> =# select '+' < '-';
> f
If the database is created with locale provider ICU, then lc_collate
does not apply here, so the result might be correct (depending on what
locale you have set).
> =# select '+' < '-' collate "C";
> t
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:27 Jeff Davis <[email protected]>
parent: Sandro Santilli <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 17:27 UTC (permalink / raw)
To: Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; +Cc: Regina Obe <[email protected]>; [email protected]
On Fri, 2023-04-21 at 19:09 +0200, Sandro Santilli wrote:
> =# select version();
> PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
> =# show lc_collate;
> C
> =# select '+' < '-';
> f
What is the result of:
select datlocprovider, datcollate, daticulocale
from pg_database where datname=current_database();
> =# select '+' < '-' collate "C";
> t
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:28 Tom Lane <[email protected]>
parent: Peter Eisentraut <[email protected]>
3 siblings, 2 replies; 45+ messages in thread
From: Tom Lane @ 2023-04-21 17:28 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Sandro Santilli <[email protected]>; Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
Peter Eisentraut <[email protected]> writes:
> If the database is created with locale provider ICU, then lc_collate
> does not apply here, so the result might be correct (depending on what
> locale you have set).
FWIW, an installation created under LANG=C defaults to ICU locale
en-US-u-va-posix for me (see psql \l), and that still sorts as
expected on my RHEL8 box. We've not seen buildfarm problems either.
I am wondering however whether this doesn't mean that all our carefully
coded fast paths for C locale just went down the drain. Does the ICU
code have any of that? Has any performance testing been done to see
what impact this change had on C-locale installations? (The current
code coverage report for pg_locale.c is not encouraging.)
regards, tom lane
^ permalink raw reply [nested|flat] 45+ messages in thread
* RE: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:37 Regina Obe <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Regina Obe @ 2023-04-21 17:37 UTC (permalink / raw)
To: 'Tom Lane' <[email protected]>; 'Peter Eisentraut' <[email protected]>; +Cc: 'Sandro Santilli' <[email protected]>; [email protected]; 'Jeff Davis' <[email protected]>
> Peter Eisentraut <[email protected]> writes:
> > If the database is created with locale provider ICU, then lc_collate
> > does not apply here, so the result might be correct (depending on what
> > locale you have set).
>
> FWIW, an installation created under LANG=C defaults to ICU locale en-US-u-
> va-posix for me (see psql \l), and that still sorts as expected on my
RHEL8 box.
> We've not seen buildfarm problems either.
>
> I am wondering however whether this doesn't mean that all our carefully
> coded fast paths for C locale just went down the drain. Does the ICU code
> have any of that? Has any performance testing been done to see what
impact
> this change had on C-locale installations? (The current code coverage
report
> for pg_locale.c is not encouraging.)
>
> regards, tom lane
Just another metric.
On my mingw64 setup, I built a test database on PG16 (built with icu
support) and PG15 (no icu support)
CREATE DATABASE test TEMPLATE=template0 ENCODING = 'UTF8' LC_COLLATE = 'C'
LC_CTYPE = 'C';
I think the above is the similar setup we have when testing.
On PG15
SELECT '+' < '-' ; returns true
On PG 16 returns false
For PG 16, to strk's point, you have to do: to get a true
SELECT '+' COLLATE "C" < '-' COLLATE "C";
I would expect since I'm initializing my db in collate C they would both
behave the same
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:46 Tom Lane <[email protected]>
parent: Regina Obe <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Tom Lane @ 2023-04-21 17:46 UTC (permalink / raw)
To: Regina Obe <[email protected]>; +Cc: 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]; 'Jeff Davis' <[email protected]>
"Regina Obe" <[email protected]> writes:
> On my mingw64 setup, I built a test database on PG16 (built with icu
> support) and PG15 (no icu support)
> CREATE DATABASE test TEMPLATE=template0 ENCODING = 'UTF8' LC_COLLATE = 'C'
> LC_CTYPE = 'C';
As has been pointed out already, setting LC_COLLATE/LC_CTYPE is
meaningless when the locale provider is ICU. You need to look
at what ICU locale is being chosen, or force it with LOCALE = 'C'.
regards, tom lane
^ permalink raw reply [nested|flat] 45+ messages in thread
* RE: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:56 Regina Obe <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Regina Obe @ 2023-04-21 17:56 UTC (permalink / raw)
To: 'Tom Lane' <[email protected]>; +Cc: 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]; 'Jeff Davis' <[email protected]>
> > CREATE DATABASE test TEMPLATE=template0 ENCODING = 'UTF8'
> LC_COLLATE = 'C'
> > LC_CTYPE = 'C';
>
> As has been pointed out already, setting LC_COLLATE/LC_CTYPE is
> meaningless when the locale provider is ICU. You need to look at what ICU
> locale is being chosen, or force it with LOCALE = 'C'.
>
> regards, tom lane
Okay got it was on IRC with RhodiumToad and he suggested:
CREATE DATABASE test2 TEMPLATE=template0 ENCODING = 'UTF8' LC_COLLATE = 'C'
LC_CTYPE = 'C' ICU_LOCALE='C';
Which gives expected result:
SELECT '+' < '-' ; -- true
but gives me a notice:
NOTICE: using standard form "en-US-u-va-posix" for locale "C"
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 17:59 Tom Lane <[email protected]>
parent: Regina Obe <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Tom Lane @ 2023-04-21 17:59 UTC (permalink / raw)
To: Regina Obe <[email protected]>; +Cc: 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]; 'Jeff Davis' <[email protected]>
"Regina Obe" <[email protected]> writes:
> Okay got it was on IRC with RhodiumToad and he suggested:
> CREATE DATABASE test2 TEMPLATE=template0 ENCODING = 'UTF8' LC_COLLATE = 'C'
> LC_CTYPE = 'C' ICU_LOCALE='C';
> Which gives expected result:
> SELECT '+' < '-' ; -- true
> but gives me a notice:
> NOTICE: using standard form "en-US-u-va-posix" for locale "C"
Yeah. My recommendation is just LOCALE:
regression=# CREATE DATABASE test1 TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C';
CREATE DATABASE
regression=# CREATE DATABASE test2 TEMPLATE=template0 ENCODING = 'UTF8' ICU_LOCALE = 'C';
NOTICE: using standard form "en-US-u-va-posix" for locale "C"
CREATE DATABASE
I think it's probably intentional that ICU_LOCALE is stricter
about being given a real ICU locale name, but I didn't write
any of that code.
regards, tom lane
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 18:00 Andrew Gierth <[email protected]>
parent: Peter Eisentraut <[email protected]>
3 siblings, 2 replies; 45+ messages in thread
From: Andrew Gierth @ 2023-04-21 18:00 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
>>>>> "Peter" == Peter Eisentraut <[email protected]> writes:
Peter> If the database is created with locale provider ICU, then
Peter> lc_collate does not apply here,
Having lc_collate return a value which is silently being ignored seems
to me rather hugely confusing.
Also, somewhere along the line someone broke initdb --no-locale, which
should result in C locale being the default everywhere, but when I just
tested it it picked 'en' for an ICU locale, which is not the right
thing.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 18:06 Tom Lane <[email protected]>
parent: Andrew Gierth <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Tom Lane @ 2023-04-21 18:06 UTC (permalink / raw)
To: Andrew Gierth <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
Andrew Gierth <[email protected]> writes:
> "Peter" == Peter Eisentraut <[email protected]> writes:
> Peter> If the database is created with locale provider ICU, then
> Peter> lc_collate does not apply here,
> Having lc_collate return a value which is silently being ignored seems
> to me rather hugely confusing.
It's not *completely* ignored --- there are bits of code that are not
yet ICU-ified and will still use the libc facilities. So we can't
get rid of those options yet, even in an ICU-based database.
> Also, somewhere along the line someone broke initdb --no-locale, which
> should result in C locale being the default everywhere, but when I just
> tested it it picked 'en' for an ICU locale, which is not the right
> thing.
Confirmed:
$ LANG=en_US.utf8 initdb --no-locale
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Using default ICU locale "en_US".
Using language tag "en-US" for ICU locale "en_US".
The database cluster will be initialized with this locale configuration:
provider: icu
ICU locale: en-US
LC_COLLATE: C
LC_CTYPE: C
...
That needs to be fixed: --no-locale should prevent any consideration
of initdb's LANG/LC_foo environment.
regards, tom lane
^ permalink raw reply [nested|flat] 45+ messages in thread
* RE: Order changes in PG16 since ICU introduction
@ 2023-04-21 18:13 Regina Obe <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Regina Obe @ 2023-04-21 18:13 UTC (permalink / raw)
To: 'Tom Lane' <[email protected]>; +Cc: 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]; 'Jeff Davis' <[email protected]>
> Yeah. My recommendation is just LOCALE:
>
> regression=# CREATE DATABASE test1 TEMPLATE=template0 ENCODING =
> 'UTF8' LOCALE = 'C'; CREATE DATABASE regression=# CREATE DATABASE test2
> TEMPLATE=template0 ENCODING = 'UTF8' ICU_LOCALE = 'C';
> NOTICE: using standard form "en-US-u-va-posix" for locale "C"
> CREATE DATABASE
>
> I think it's probably intentional that ICU_LOCALE is stricter about being
given
> a real ICU locale name, but I didn't write any of that code.
>
> regards, tom lane
CREATE DATABASE test1 TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C';
Doesn't seem to work at least not under mingw64 anyway.
SELECT '+' < '-' ;
Returns false
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 18:23 Tom Lane <[email protected]>
parent: Regina Obe <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Tom Lane @ 2023-04-21 18:23 UTC (permalink / raw)
To: Regina Obe <[email protected]>; +Cc: 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]; 'Jeff Davis' <[email protected]>
"Regina Obe" <[email protected]> writes:
> CREATE DATABASE test1 TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C';
> Doesn't seem to work at least not under mingw64 anyway.
Hmm, doesn't work for me either:
$ LANG=en_US.utf8 initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Using default ICU locale "en_US".
Using language tag "en-US" for ICU locale "en_US".
The database cluster will be initialized with this locale configuration:
provider: icu
ICU locale: en-US
LC_COLLATE: en_US.utf8
LC_CTYPE: en_US.utf8
LC_MESSAGES: en_US.utf8
LC_MONETARY: en_US.utf8
LC_NUMERIC: en_US.utf8
LC_TIME: en_US.utf8
...
$ psql postgres
psql (16devel)
Type "help" for help.
postgres=# SELECT '+' < '-' ;
?column?
----------
f
(1 row)
(as expected, so far)
postgres=# CREATE DATABASE test1 TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C';
CREATE DATABASE
postgres=# \c test1
You are now connected to database "test1" as user "postgres".
test1=# SELECT '+' < '-' ;
?column?
----------
f
(1 row)
(wrong!)
test1=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | icu | en_US.utf8 | en_US.utf8 | en-US | |
template0 | postgres | UTF8 | icu | en_US.utf8 | en_US.utf8 | en-US | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | icu | en_US.utf8 | en_US.utf8 | en-US | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | icu | C | C | en-US | |
(4 rows)
Looks like the "pick en-US even when told not to" problem exists here too.
regards, tom lane
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 19:14 Sandro Santilli <[email protected]>
parent: Peter Eisentraut <[email protected]>
3 siblings, 1 reply; 45+ messages in thread
From: Sandro Santilli @ 2023-04-21 19:14 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
On Fri, Apr 21, 2023 at 07:14:13PM +0200, Peter Eisentraut wrote:
> On 21.04.23 19:09, Sandro Santilli wrote:
> > On Fri, Apr 21, 2023 at 11:48:51AM -0400, Tom Lane wrote:
> > > "Regina Obe" <[email protected]> writes:
> > >
> > > > https://trac.osgeo.org/postgis/ticket/5375
> > >
> > > If they actually are using locale C, I would say this is a bug.
> > > That should designate memcmp sorting and nothing else.
> >
> > Sounds like a bug to me. This is happening with a PostgreSQL cluster
> > created and served by a build of commit c04c6c5d6f :
> >
> > =# select version();
> > PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
> > =# show lc_collate;
> > C
> > =# select '+' < '-';
> > f
>
> If the database is created with locale provider ICU, then lc_collate does
> not apply here, so the result might be correct (depending on what locale you
> have set).
The database is created by a perl script which starts like this:
$ENV{"LC_ALL"} = "C";
$ENV{"LANG"} = "C";
And then runs:
createdb --encoding=UTF-8 --template=template0 --lc-collate=C
Should we tweak anything else to make the results predictable ?
--strk;
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 19:14 Andrew Gierth <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Andrew Gierth @ 2023-04-21 19:14 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
>>>>> "Tom" == Tom Lane <[email protected]> writes:
>> Also, somewhere along the line someone broke initdb --no-locale,
>> which should result in C locale being the default everywhere, but
>> when I just tested it it picked 'en' for an ICU locale, which is not
>> the right thing.
Tom> Confirmed:
Tom> $ LANG=en_US.utf8 initdb --no-locale
Tom> The files belonging to this database system will be owned by user "postgres".
Tom> This user must also own the server process.
Tom> Using default ICU locale "en_US".
Tom> Using language tag "en-US" for ICU locale "en_US".
Tom> The database cluster will be initialized with this locale configuration:
Tom> provider: icu
Tom> ICU locale: en-US
Tom> LC_COLLATE: C
Tom> LC_CTYPE: C
Tom> ...
Tom> That needs to be fixed: --no-locale should prevent any
Tom> consideration of initdb's LANG/LC_foo environment.
Would it also not make sense to also take into account any --locale and
--lc-* options before choosing an ICU default locale? Right now if you
do, say, initdb --locale=fr_FR you get an ICU locale based on the
environment but lc_* settings based on the option, which seems maximally
confusing.
Also, what happens now to lc_collate_is_c() when the provider is ICU? Am
I missing something, or is it never true now, even if you specified C /
POSIX / en-US-u-va-posix as the ICU locale? This seems like it could be
an important pessimization.
Also also, we now have the problem that it is much harder to create a
'C' collation database within an existing cluster (e.g. for testing)
without knowing whether the default provider is ICU. In the past one
would have done:
CREATE DATABASE test TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C';
but now that creates a database that uses the same ICU locale as
template0 by default. If instead one tries:
CREATE DATABASE test TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C' ICU_LOCALE='C';
then one gets an error if the default locale provider is _not_ ICU. The
only option now seems to be:
CREATE DATABASE test TEMPLATE=template0 ENCODING = 'UTF8' LOCALE = 'C' LOCALE_PROVIDER = 'libc';
which of course doesn't work in older pg versions.
--
Andrew.
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 19:17 Sandro Santilli <[email protected]>
parent: Jeff Davis <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Sandro Santilli @ 2023-04-21 19:17 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]
On Fri, Apr 21, 2023 at 10:27:49AM -0700, Jeff Davis wrote:
> On Fri, 2023-04-21 at 19:09 +0200, Sandro Santilli wrote:
> > =# select version();
> > PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> > 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
> > =# show lc_collate;
> > C
> > =# select '+' < '-';
> > f
>
> What is the result of:
>
> select datlocprovider, datcollate, daticulocale
> from pg_database where datname=current_database();
datlocprovider | i
datcollate | C
daticulocale | en-US
--strk;
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 19:25 Jeff Davis <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 2 replies; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 19:25 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Regina Obe <[email protected]>; +Cc: 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]
On Fri, 2023-04-21 at 14:23 -0400, Tom Lane wrote:
> postgres=# CREATE DATABASE test1 TEMPLATE=template0 ENCODING = 'UTF8'
> LOCALE = 'C';
...
> test1 | postgres | UTF8 | icu | C |
> C | en-US | |
> (4 rows)
>
> Looks like the "pick en-US even when told not to" problem exists here
> too.
Both provider (ICU) and the icu locale (en-US) are inherited from
template0. The LOCALE parameter to CREATE DATABASE doesn't affect
either of those things, because there's a separate parameter
ICU_LOCALE.
This happens the same way in v15, and although it matches the
documentation technically, it is not a great user experience.
I have a couple ideas:
1. Introduce a "none" provider to separate the concept of C/POSIX
locales from the libc provider. It's not really using a provider
anyway, it's just using memcmp(), and I think it causes confusion to
combine them. Saying "LOCALE_PROVIDER=none" is less error-prone than
"LOCALE_PROVIDER=libc LOCALE='C'".
2. Change the CREATE DATABASE syntax to catch these errors better at
the possible expense of backwards compatibility.
I am also having second thoughts about accepting "C" or "POSIX" as an
ICU locale and transforming it to "en-US-u-va-posix" in v16. It's not
terribly useful (why not just use memcmp()?), it's not fast in my
measurements (en-US is faster), so maybe it's better to just throw an
error and tell the user to use C (or provider=none as I suggest
above)?
Obviously the user could manually type "en-US-u-va-posix" if that's the
locale they want. Throwing an error would be a backwards-compatibility
issue, but in v15 an ICU locale of "C" just gives the root locale
anyway, which is probably not what they want.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 20:00 Tom Lane <[email protected]>
parent: Jeff Davis <[email protected]>
1 sibling, 2 replies; 45+ messages in thread
From: Tom Lane @ 2023-04-21 20:00 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Regina Obe <[email protected]>; 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]
Jeff Davis <[email protected]> writes:
> I have a couple ideas:
> 1. Introduce a "none" provider to separate the concept of C/POSIX
> locales from the libc provider. It's not really using a provider
> anyway, it's just using memcmp(), and I think it causes confusion to
> combine them. Saying "LOCALE_PROVIDER=none" is less error-prone than
> "LOCALE_PROVIDER=libc LOCALE='C'".
I think I might like this idea, except for one thing: you're imagining
that the locale doesn't control anything except string comparisons.
What about to_upper/to_lower, character classifications in regexes, etc?
(I'm not sure whether those operations can get redirected to ICU today
or whether they still always go to libc, but we'll surely want to fix
it eventually if the latter is still true.)
In any case, that seems somewhat orthogonal to what we're on about here,
which is making the behavior of CREATE DATABASE less surprising and more
backwards-compatible. I'm not sure that provider=none can help with that.
Aside from the user-surprise issues discussed up to now, pg_dump scripts
emitted by pre-v15 pg_dump are not going to contain LOCALE_PROVIDER
clauses in CREATE DATABASE, and people are going to be very unhappy
if that means they suddenly get totally different locale semantics
after restoring into a new DB. I think we need some plan for mapping
libc-style locale specs into ICU locales so that we can make that
more nearly transparent.
> 2. Change the CREATE DATABASE syntax to catch these errors better at
> the possible expense of backwards compatibility.
That is the exact opposite of what I think we need. Backwards
compatibility isn't optional.
Maybe this means we are not ready to do ICU-by-default in v16.
It certainly feels like there might be more here than we want to
start designing post-feature-freeze.
regards, tom lane
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 20:03 Jeff Davis <[email protected]>
parent: Sandro Santilli <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 20:03 UTC (permalink / raw)
To: Sandro Santilli <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]
On Fri, 2023-04-21 at 21:14 +0200, Sandro Santilli wrote:
> And then runs:
>
> createdb --encoding=UTF-8 --template=template0 --lc-collate=C
>
> Should we tweak anything else to make the results predictable ?
You can specify --locale-provider=libc
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 20:13 Jeff Davis <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 20:13 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: Sandro Santilli <[email protected]>; Regina Obe <[email protected]>; [email protected]
On Fri, 2023-04-21 at 13:28 -0400, Tom Lane wrote:
> I am wondering however whether this doesn't mean that all our
> carefully
> coded fast paths for C locale just went down the drain.
The code still exists. You can test it by using the built-in collation
"C" which is correctly specified with collprovider=libc and
collcollate=C.
For my test dataset, ICU 72, glibc 2.35:
-- ~07s
explain analyze select t from a order by t collate "C";
-- ~15s
explain analyze select t from a order by t collate "en-US-x-icu";
-- ~21s
explain analyze select t from a order by t collate "en-US-u-va-posix-
x-icu";
-- ~34s
explain analyze select t from a order by t collate "en_US";
I believe the confusion in this thread comes from:
* The syntax of CREATE DATABASE (the same as v15 but still confusing)
* The fact that you need provider=libc to get memcmp() behavior (same
as v15 but still confusing)
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 20:33 Robert Haas <[email protected]>
parent: Jeff Davis <[email protected]>
1 sibling, 2 replies; 45+ messages in thread
From: Robert Haas @ 2023-04-21 20:33 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Fri, Apr 21, 2023 at 3:25 PM Jeff Davis <[email protected]> wrote:
> I am also having second thoughts about accepting "C" or "POSIX" as an
> ICU locale and transforming it to "en-US-u-va-posix" in v16. It's not
> terribly useful (why not just use memcmp()?), it's not fast in my
> measurements (en-US is faster), so maybe it's better to just throw an
> error and tell the user to use C (or provider=none as I suggest
> above)?
I mean, to renew a complaint I've made previously, how the heck is
anyone supposed to understand what's going on here?
We have no meaningful documentation of how to select an ICU locale
that works for you. We have a couple of examples and a suggestion that
you should use BCP 47. But when I asked before for documentation
references, the ones you provided were not clear, basically
incomprehensible. In follow-up discussion, you admitted you'd had to
consult the source code to figure certain things out.
And the fact that "C" or "POSIX" gets transformed into
"en-US-u-va-posix" is also completely documented. That string appears
twice in the code, but zero times in the documentation. There's code
to do it, but users shouldn't have to read code, and it wouldn't help
much if they did, because the code comments don't really explain the
rationale behind this choice either.
I find the fact that people are having trouble here completely
predictable. Of course if people ask for "C" and the system tells them
that it's using "en-US-u-va-posix" instead they're going to be
confused and ask questions, exactly as is happening here. glibc
collations aren't particularly well-documented either, but people have
some experience with, and they can get a list of values that have a
chance of working from /usr/share/locale, and they know what "C"
means. Nobody knows what "en-US-u-va-posix" is. It's not even
Googleable, really, whereas "C locale" is.
My opinion is that the switch to using ICU by default is ill-advised
and should be reverted. The compatibility break isn't worth whatever
advantages ICU may have, the documentation to allow people to
transition to ICU with reasonable effort doesn't exist, and the fact
that within weeks of feature freeze people who know a lot about
PostgreSQL are struggling to get the behavior they want is a really
bad sign.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 20:45 Jeff Davis <[email protected]>
parent: Andrew Gierth <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 20:45 UTC (permalink / raw)
To: Andrew Gierth <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]
On Fri, 2023-04-21 at 19:00 +0100, Andrew Gierth wrote:
> > > > >
> Also, somewhere along the line someone broke initdb --no-locale,
> which
> should result in C locale being the default everywhere, but when I
> just
> tested it it picked 'en' for an ICU locale, which is not the right
> thing.
Fixed, thank you.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 20:50 Jeff Davis <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 20:50 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Regina Obe <[email protected]>; 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]
On Fri, 2023-04-21 at 16:00 -0400, Tom Lane wrote:
> Maybe this means we are not ready to do ICU-by-default in v16.
> It certainly feels like there might be more here than we want to
> start designing post-feature-freeze.
I don't see how punting to the next release helps. If the CREATE
DATABASE syntax (and similar issues for createdb and initdb) in v15 is
just too confusing, and we can't find a remedy for v16, then we
probably won't find a remedy for v17 either.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 21:56 Jeff Davis <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 2 replies; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 21:56 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Fri, 2023-04-21 at 16:33 -0400, Robert Haas wrote:
> My opinion is that the switch to using ICU by default is ill-advised
> and should be reverted.
Most of the complaints seem to be complaints about v15 as well, and
while those complaints may be a reason to not make ICU the default,
they are also an argument that we should continue to learn and try to
fix those issues because they exist in an already-released version.
Leaving it the default for now will help us fix those issues rather
than hide them.
It's still early, so we have plenty of time to revert the initdb
default if we need to.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* RE: Order changes in PG16 since ICU introduction
@ 2023-04-21 22:39 Regina Obe <[email protected]>
parent: Jeff Davis <[email protected]>
1 sibling, 0 replies; 45+ messages in thread
From: Regina Obe @ 2023-04-21 22:39 UTC (permalink / raw)
To: 'Jeff Davis' <[email protected]>; 'Robert Haas' <[email protected]>; +Cc: 'Tom Lane' <[email protected]>; 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]
> > My opinion is that the switch to using ICU by default is ill-advised
> > and should be reverted.
>
> Most of the complaints seem to be complaints about v15 as well, and while
> those complaints may be a reason to not make ICU the default, they are also
> an argument that we should continue to learn and try to fix those issues
> because they exist in an already-released version.
> Leaving it the default for now will help us fix those issues rather than hide
> them.
>
> It's still early, so we have plenty of time to revert the initdb default if we need
> to.
>
> Regards,
> Jeff Davis
I'm fine with that. Sounds like it wouldn't be too hard to just pull it out at the end.
Before this, I didn't even know ICU existed in PG15. My first realization that ICU was even a thing was when my PG16 refused to compile without adding my ICU path to my pkg-config or putting in --without-icu.
So yah I suspect leaving it in a little bit longer will uncover some more issues and won't harm too much.
Thanks,
Regina
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-21 23:00 Jeff Davis <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Jeff Davis @ 2023-04-21 23:00 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Fri, 2023-04-21 at 16:33 -0400, Robert Haas wrote:
> And the fact that "C" or "POSIX" gets transformed into
> "en-US-u-va-posix"
I already expressed, on reflection, that we should probably just not do
that. So I think we're in agreement on this point; patch attached.
Regards,
Jeff Davis
Attachments:
[text/x-patch] 0001-ICU-do-not-convert-locale-C-to-en-US-u-va-posix.patch (6.9K, ../../[email protected]/2-0001-ICU-do-not-convert-locale-C-to-en-US-u-va-posix.patch)
download | inline diff:
From 3d2791af0a236cbc7ce7f29d988e8ac7fd3fd389 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Fri, 21 Apr 2023 14:03:57 -0700
Subject: [PATCH] ICU: do not convert locale 'C' to 'en-US-u-va-posix'.
The conversion was intended to be for convenience, but it's more
likely to be confusing than useful.
The user can still directly specify 'en-US-u-va-posix' if desired.
Discussion: https://postgr.es/m/[email protected]
---
src/backend/utils/adt/pg_locale.c | 19 +------------------
src/bin/initdb/initdb.c | 17 +----------------
.../regress/expected/collate.icu.utf8.out | 8 ++++++++
src/test/regress/sql/collate.icu.utf8.sql | 4 ++++
4 files changed, 14 insertions(+), 34 deletions(-)
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c
index 51df570ce9..58c4c426bc 100644
--- a/src/backend/utils/adt/pg_locale.c
+++ b/src/backend/utils/adt/pg_locale.c
@@ -2782,26 +2782,10 @@ icu_language_tag(const char *loc_str, int elevel)
{
#ifdef USE_ICU
UErrorCode status;
- char lang[ULOC_LANG_CAPACITY];
char *langtag;
size_t buflen = 32; /* arbitrary starting buffer size */
const bool strict = true;
- status = U_ZERO_ERROR;
- uloc_getLanguage(loc_str, lang, ULOC_LANG_CAPACITY, &status);
- if (U_FAILURE(status))
- {
- if (elevel > 0)
- ereport(elevel,
- (errmsg("could not get language from locale \"%s\": %s",
- loc_str, u_errorName(status))));
- return NULL;
- }
-
- /* C/POSIX locales aren't handled by uloc_getLanguageTag() */
- if (strcmp(lang, "c") == 0 || strcmp(lang, "posix") == 0)
- return pstrdup("en-US-u-va-posix");
-
/*
* A BCP47 language tag doesn't have a clearly-defined upper limit
* (cf. RFC5646 section 4.4). Additionally, in older ICU versions,
@@ -2889,8 +2873,7 @@ icu_validate_locale(const char *loc_str)
/* check for special language name */
if (strcmp(lang, "") == 0 ||
- strcmp(lang, "root") == 0 || strcmp(lang, "und") == 0 ||
- strcmp(lang, "c") == 0 || strcmp(lang, "posix") == 0)
+ strcmp(lang, "root") == 0 || strcmp(lang, "und") == 0)
found = true;
/* search for matching language within ICU */
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 2c208ead01..4086834458 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -2238,24 +2238,10 @@ icu_language_tag(const char *loc_str)
{
#ifdef USE_ICU
UErrorCode status;
- char lang[ULOC_LANG_CAPACITY];
char *langtag;
size_t buflen = 32; /* arbitrary starting buffer size */
const bool strict = true;
- status = U_ZERO_ERROR;
- uloc_getLanguage(loc_str, lang, ULOC_LANG_CAPACITY, &status);
- if (U_FAILURE(status))
- {
- pg_fatal("could not get language from locale \"%s\": %s",
- loc_str, u_errorName(status));
- return NULL;
- }
-
- /* C/POSIX locales aren't handled by uloc_getLanguageTag() */
- if (strcmp(lang, "c") == 0 || strcmp(lang, "posix") == 0)
- return pstrdup("en-US-u-va-posix");
-
/*
* A BCP47 language tag doesn't have a clearly-defined upper limit
* (cf. RFC5646 section 4.4). Additionally, in older ICU versions,
@@ -2327,8 +2313,7 @@ icu_validate_locale(const char *loc_str)
/* check for special language name */
if (strcmp(lang, "") == 0 ||
- strcmp(lang, "root") == 0 || strcmp(lang, "und") == 0 ||
- strcmp(lang, "c") == 0 || strcmp(lang, "posix") == 0)
+ strcmp(lang, "root") == 0 || strcmp(lang, "und") == 0)
found = true;
/* search for matching language within ICU */
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index b5a221b030..99f12d2e73 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1020,6 +1020,7 @@ CREATE ROLE regress_test_role;
CREATE SCHEMA test_schema;
-- We need to do this this way to cope with varying names for encodings:
SET client_min_messages TO WARNING;
+SET icu_validation_level = disabled;
do $$
BEGIN
EXECUTE 'CREATE COLLATION test0 (provider = icu, locale = ' ||
@@ -1034,17 +1035,24 @@ BEGIN
quote_literal(current_setting('lc_collate')) || ');';
END
$$;
+RESET icu_validation_level;
RESET client_min_messages;
CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, needs "locale"
ERROR: parameter "locale" must be specified
CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); -- fails
ERROR: ICU locale "nonsense-nowhere" has unknown language "nonsense"
HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED.
+CREATE COLLATION testx (provider = icu, locale = 'c'); -- fails
+ERROR: could not convert locale name "c" to language tag: U_ILLEGAL_ARGUMENT_ERROR
CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); -- fails
ERROR: could not convert locale name "@colStrength=primary;nonsense=yes" to language tag: U_ILLEGAL_ARGUMENT_ERROR
SET icu_validation_level = WARNING;
CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DROP COLLATION testx;
WARNING: could not convert locale name "@colStrength=primary;nonsense=yes" to language tag: U_ILLEGAL_ARGUMENT_ERROR
+CREATE COLLATION testx (provider = icu, locale = 'c'); DROP COLLATION testx;
+WARNING: could not convert locale name "c" to language tag: U_ILLEGAL_ARGUMENT_ERROR
+WARNING: ICU locale "c" has unknown language "c"
+HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED.
CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx;
WARNING: ICU locale "nonsense-nowhere" has unknown language "nonsense"
HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED.
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 85e26951b6..d9778faacc 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -358,6 +358,7 @@ CREATE SCHEMA test_schema;
-- We need to do this this way to cope with varying names for encodings:
SET client_min_messages TO WARNING;
+SET icu_validation_level = disabled;
do $$
BEGIN
@@ -373,13 +374,16 @@ BEGIN
END
$$;
+RESET icu_validation_level;
RESET client_min_messages;
CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, needs "locale"
CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); -- fails
+CREATE COLLATION testx (provider = icu, locale = 'c'); -- fails
CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); -- fails
SET icu_validation_level = WARNING;
CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DROP COLLATION testx;
+CREATE COLLATION testx (provider = icu, locale = 'c'); DROP COLLATION testx;
CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx;
RESET icu_validation_level;
--
2.34.1
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-22 00:12 Robert Haas <[email protected]>
parent: Jeff Davis <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Robert Haas @ 2023-04-22 00:12 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Fri, Apr 21, 2023 at 5:56 PM Jeff Davis <[email protected]> wrote:
> Most of the complaints seem to be complaints about v15 as well, and
> while those complaints may be a reason to not make ICU the default,
> they are also an argument that we should continue to learn and try to
> fix those issues because they exist in an already-released version.
> Leaving it the default for now will help us fix those issues rather
> than hide them.
>
> It's still early, so we have plenty of time to revert the initdb
> default if we need to.
That's fair enough, but I really think it's important that some energy
get invested in providing adequate documentation for this stuff. Just
patching the code is not enough.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-24 15:10 Peter Eisentraut <[email protected]>
parent: Peter Eisentraut <[email protected]>
3 siblings, 1 reply; 45+ messages in thread
From: Peter Eisentraut @ 2023-04-24 15:10 UTC (permalink / raw)
To: Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; +Cc: Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
On 21.04.23 19:14, Peter Eisentraut wrote:
> On 21.04.23 19:09, Sandro Santilli wrote:
>> On Fri, Apr 21, 2023 at 11:48:51AM -0400, Tom Lane wrote:
>>> "Regina Obe" <[email protected]> writes:
>>>
>>>> https://trac.osgeo.org/postgis/ticket/5375
>>>
>>> If they actually are using locale C, I would say this is a bug.
>>> That should designate memcmp sorting and nothing else.
>>
>> Sounds like a bug to me. This is happening with a PostgreSQL cluster
>> created and served by a build of commit c04c6c5d6f :
>>
>> =# select version();
>> PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
>> 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
>> =# show lc_collate;
>> C
>> =# select '+' < '-';
>> f
>
> If the database is created with locale provider ICU, then lc_collate
> does not apply here, so the result might be correct (depending on what
> locale you have set).
The GUC settings lc_collate and lc_ctype are from a time when those
locale settings were cluster-global. When we made those locale settings
per-database (PG 8.4), we kept them as read-only. As of PG 15, you can
use ICU as the per-database locale provider, so what is being attempted
in the above example is already meaningless before PG 16, since you need
to look into pg_database to find out what is really happening.
I think we should just remove the GUC parameters lc_collate and lc_ctype.
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-24 15:26 Peter Eisentraut <[email protected]>
parent: Jeff Davis <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Peter Eisentraut @ 2023-04-24 15:26 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On 22.04.23 01:00, Jeff Davis wrote:
> On Fri, 2023-04-21 at 16:33 -0400, Robert Haas wrote:
>> And the fact that "C" or "POSIX" gets transformed into
>> "en-US-u-va-posix"
>
> I already expressed, on reflection, that we should probably just not do
> that. So I think we're in agreement on this point; patch attached.
This makes sense to me. This way, if someone specifies 'C' locale
together with ICU provider they get an error. They can then choose to
use the libc provider, to get the performance path, or stick with ICU by
using the native spelling of the locale.
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-04-25 04:31 Jeff Davis <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-04-25 04:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Regina Obe <[email protected]>; 'Peter Eisentraut' <[email protected]>; 'Sandro Santilli' <[email protected]>; [email protected]
On Fri, 2023-04-21 at 16:00 -0400, Tom Lane wrote:
> I think I might like this idea, except for one thing: you're
> imagining
> that the locale doesn't control anything except string comparisons.
> What about to_upper/to_lower, character classifications in regexes,
> etc?
If provider='libc' and LC_CTYPE='C', str_toupper/str_tolower are
handled with asc_tolower/asc_toupper. The regex character
classification is done with pg_char_properties. In these cases neither
ICU nor libc is used; it's just code in postgres.
libc is special in that you can set LC_COLLATE and LC_CTYPE separately,
so that different locales are used for sorting and character
classification. That's potentially useful to set LC_COLLATE to C for
performance reasons, while setting LC_CTYPE to a useful locale. We
don't allow ICU to set collation and ctype separately (it would be
possible to allow it, but I don't think there's a huge demand and it's
arguably inconsistent to set them differently).
> (I'm not sure whether those operations can get redirected to ICU
> today
> or whether they still always go to libc, but we'll surely want to fix
> it eventually if the latter is still true.)
Those operations do get redirected to ICU today. There are extensions
that call locale-sensitive libc functions directly, and obviously those
won't use ICU.
> Aside from the user-surprise issues discussed up to now, pg_dump
> scripts
> emitted by pre-v15 pg_dump are not going to contain LOCALE_PROVIDER
> clauses in CREATE DATABASE, and people are going to be very unhappy
> if that means they suddenly get totally different locale semantics
> after restoring into a new DB.
Agreed.
> I think we need some plan for mapping
> libc-style locale specs into ICU locales so that we can make that
> more nearly transparent.
ICU does a reasonable job mapping libc-like locale names to ICU
locales, e.g. en_US to en-US, etc. The ordering semantics aren't
guaranteed to be the same, of course (because the libc-locales are
platform-dependent), but it's at least conceptually the same locale.
>
> Maybe this means we are not ready to do ICU-by-default in v16.
> It certainly feels like there might be more here than we want to
> start designing post-feature-freeze.
This thread is already on the Open Items list. As long as it's not too
disruptive to others I'll leave it as-is for now to see how this sorts
out. Right now it's not clear to me how much of this is a v15 issue vs
a v16 issue.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-06 00:25 Jeff Davis <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Jeff Davis @ 2023-05-06 00:25 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Fri, 2023-04-21 at 20:12 -0400, Robert Haas wrote:
> On Fri, Apr 21, 2023 at 5:56 PM Jeff Davis <[email protected]> wrote:
> > Most of the complaints seem to be complaints about v15 as well, and
> > while those complaints may be a reason to not make ICU the default,
> > they are also an argument that we should continue to learn and try
> > to
> > fix those issues because they exist in an already-released version.
> > Leaving it the default for now will help us fix those issues rather
> > than hide them.
> >
> > It's still early, so we have plenty of time to revert the initdb
> > default if we need to.
>
> That's fair enough, but I really think it's important that some
> energy
> get invested in providing adequate documentation for this stuff. Just
> patching the code is not enough.
Attached a significant documentation patch.
I tried to make it comprehensive without trying to be exhaustive, and I
separated the explanation of language tags from what collation settings
you can include in a language tag, so hopefully that's more clear.
I added quite a few examples spread throughout the various sections,
and I preserved the existing examples at the end. I also left all of
the external links at the bottom for those interested enough to go
beyond what's there.
I didn't add additional documentation for ICU rules. There are so many
options for collations that it's hard for me to think of realistic
examples to specify the rules directly, unless someone wants to invent
a new language. Perhaps useful if working with an interesting text file
format with special treatment for delimiters?
I asked the question about rules here:
https://www.postgresql.org/message-id/e861ac4fdae9f9f5ce2a938a37bcb5e083f0f489.camel%40cybertec.at
and got some limited response about addressing sort complaints. That
sounds reasonable, but a lot of that can also be handled just by
specifying the right collation settings. Someone who understands the
use case better could add some more documentation.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
Attachments:
[text/x-patch] v1-0001-Doc-improvements-for-language-tags-and-custom-ICU.patch (29.1K, ../../[email protected]/2-v1-0001-Doc-improvements-for-language-tags-and-custom-ICU.patch)
download | inline diff:
From b09515bfaf5e9de330138ec4a627d02a7947de1a Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 27 Apr 2023 14:43:46 -0700
Subject: [PATCH v1] Doc improvements for language tags and custom ICU
collations.
Separate the documentation for language tags from the documentaiton
for the available collation settings which can be included in a
language tag.
Include tables of the available options, more details about the
effects of each option, and additional examples.
Also include an explanation of the "levels" of textual features and
how they relate to collation.
---
doc/src/sgml/charset.sgml | 656 +++++++++++++++++++++++++++++++-------
1 file changed, 535 insertions(+), 121 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 6dd95b8966..be74064168 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -377,7 +377,125 @@ initdb --locale-provider=icu --icu-locale=en
variants and customization options.
</para>
</sect2>
+ <sect2 id="icu-locales">
+ <title>ICU Locales</title>
+ <sect3 id="icu-locale-names">
+ <title>ICU Locale Names</title>
+ <para>
+ The ICU format for the locale name is a <link
+ linkend="icu-language-tag">Language Tag</link>.
+
+<programlisting>
+CREATE COLLATION mycollation1 (PROVIDER = icu, LOCALE = 'ja-JP);
+CREATE COLLATION mycollation2 (PROVIDER = icu, LOCALE = 'fr');
+</programlisting>
+ </para>
+ </sect3>
+ <sect3 id="icu-canonicalization">
+ <title>Locale Canonicalization and Validation</title>
+ <para>
+ When defining a new ICU collation object or database with ICU as the
+ provider, the given locale name is transformed ("canonicalized") into a
+ language tag if not already in that form. For instance,
+
+<screen>
+CREATE COLLATION mycollation3 (PROVIDER = icu, LOCALE = 'en-US-u-kn-true');
+NOTICE: using standard form "en-US-u-kn" for locale "en-US-u-kn-true"
+CREATE COLLATION mycollation4 (PROVIDER = icu, LOCALE = 'de_DE.utf8');
+NOTICE: using standard form "de-DE" for locale "de_DE.utf8"
+</screen>
+
+ If you see such a message, ensure that the <symbol>PROVIDER</symbol> and
+ <symbol>LOCALE</symbol> are as you expect, and consider specifying
+ directly as the canonical language tag instead of relying on the
+ transformation.
+ </para>
+ <note>
+ <para>
+ ICU can transform most libc locale names, as well as some other formats,
+ into language tags for easier transition to ICU. If a libc locale name
+ is used in ICU, it may not have precisely the same behavior as in libc.
+ </para>
+ </note>
+ <para>
+ If there is some problem interpreting the locale name, or if it represents
+ a language or region that ICU does not recognize, a message will be reported:
+<screen>
+SET icu_validation_level = ERROR;
+CREATE COLLATION nonsense (PROVIDER = icu, LOCALE = 'nonsense');
+ERROR: ICU locale "nonsense" has unknown language "nonsense"
+HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED.
+</screen>
+
+ <xref
+ linkend="guc-icu-validation-level"/> controls how the message is
+ reported. If set below <literal>ERROR</literal>, the collation will still
+ be created, but the behavior may not be what the user intended.
+ </para>
+ </sect3>
+ <sect3 id="icu-language-tag">
+ <title>Language Tag</title>
+ <para>
+ Basic language tags are simply
+ <replaceable>language</replaceable><literal>-</literal><replaceable>region</replaceable>;
+ or even just <replaceable>language</replaceable>. The
+ <replaceable>language</replaceable> is a language code
+ (e.g. <literal>fr</literal> for French or <literal>und</literal> for
+ "undefined"), and <replaceable>region</replaceable> is a region code
+ (e.g. <literal>CA</literal> for Canada). Examples:
+ <literal>ja-JP</literal>, <literal>de</literal>, or
+ <literal>fr-CA</literal>.
+ </para>
+ <para>
+ Collation settings may be included in the language tag to customize
+ collation behavior. ICU allows extensive customization, such as
+ sensitivity (or insensitivity) to accents, case, and punctuation;
+ treatment of digits within text; and many other options to satisfy a
+ variety of uses.
+ </para>
+ <para>
+ To include this additional collation information in a language tag,
+ append <literal>-u</literal>, followed by one or more
+ <literal>-</literal><replaceable>key</replaceable><literal>-</literal><replaceable>value</replaceable>
+ pairs, where <replaceable>key</replaceable> is the key for a collation
+ setting and <replaceable>value</replaceable> is a valid value for that
+ setting. For boolean settings, the
+ <literal>-</literal><replaceable>key</replaceable> may be specified
+ without a corresponding
+ <literal>-</literal><replaceable>value</replaceable>, which implies a
+ value of <literal>true</literal>.
+ </para>
+ <para>
+ For example, the language tag <literal>en-US-u-kn-ks-level2</literal>
+ means the locale with the English language in the US region, with
+ collation settings <literal>kn</literal> set to <literal>true</literal>
+ and <literal>ks</literal> set to <literal>level2</literal>. Those
+ settings mean the collation will be case-insensitive and treat a sequence
+ of digits as a single number:
+
+<screen>
+CREATE COLLATION mycollation5 (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'en-US-u-kn-ks-level2');
+SELECT 'aB' = 'Ab' COLLATE mycollation5 as result;
+ result
+--------
+ t
+(1 row)
+
+SELECT 'N-45' < 'N-123' COLLATE mycollation5 as result;
+ result
+--------
+ t
+(1 row)
+</screen>
+ </para>
+ <para>
+ See <xref linkend="icu-custom-collations"/> for details and additional
+ examples of using language tags with custom collation information for the
+ locale.
+ </para>
+ </sect3>
+ </sect2>
<sect2 id="locale-problems">
<title>Problems</title>
@@ -658,6 +776,13 @@ SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";
code byte values.
</para>
+ <note>
+ <para>
+ The <literal>C</literal> and <literal>POSIX</literal> locales may behave
+ differently depending on the database encoding.
+ </para>
+ </note>
+
<para>
Additionally, two SQL standard collation names are available:
@@ -869,132 +994,23 @@ CREATE COLLATION german (provider = libc, locale = 'de_DE');
<sect4 id="collation-managing-create-icu">
<title>ICU Collations</title>
- <para>
- ICU allows collations to be customized beyond the basic language+country
- set that is preloaded by <command>initdb</command>. Users are encouraged
- to define their own collation objects that make use of these facilities to
- suit the sorting behavior to their requirements.
- See <ulink url="https://unicode-org.github.io/icu/userguide/locale/"></ulink>
- and <ulink url="https://unicode-org.github.io/icu/userguide/collation/api.html"></ulink> for
- information on ICU locale naming. The set of acceptable names and
- attributes depends on the particular ICU version.
- </para>
-
- <para>
- Here are some examples:
-
- <variablelist>
- <varlistentry id="collation-managing-create-icu-de-u-co-phonebk-x-icu">
- <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</literal></term>
- <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');</literal></term>
- <listitem>
- <para>German collation with phone book collation type</para>
- <para>
- The first example selects the ICU locale using a <quote>language
- tag</quote> per BCP 47. The second example uses the traditional
- ICU-specific locale syntax. The first style is preferred going
- forward, and is used internally to store locales.
- </para>
- <para>
- Note that you can name the collation objects in the SQL environment
- anything you want. In this example, we follow the naming style that
- the predefined collations use, which in turn also follow BCP 47, but
- that is not required for user-defined collations.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-und-u-co-emoji-x-icu">
- <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</literal></term>
- <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');</literal></term>
- <listitem>
- <para>
- Root collation with Emoji collation type, per Unicode Technical Standard #51
- </para>
- <para>
- Observe how in the traditional ICU locale naming system, the root
- locale is selected by an empty string.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kr-grek-latn">
- <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</literal></term>
- <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en@colReorder=grek-latn');</literal></term>
- <listitem>
- <para>
- Sort Greek letters before Latin ones. (The default is Latin before Greek.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kf-upper">
- <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</literal></term>
- <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');</literal></term>
- <listitem>
- <para>
- Sort upper-case letters before lower-case letters. (The default is
- lower-case letters first.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kf-upper-kr-grek-latn">
- <term><literal>CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</literal></term>
- <term><literal>CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');</literal></term>
- <listitem>
- <para>
- Combines both of the above options.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kn-true">
- <term><literal>CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');</literal></term>
- <term><literal>CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');</literal></term>
- <listitem>
- <para>
- Numeric ordering, sorts sequences of digits by their numeric value,
- for example: <literal>A-21</literal> < <literal>A-123</literal>
- (also known as natural sort).
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
-
- See <ulink url="https://www.unicode.org/reports/tr35/tr35-collation.html">Unicode
- Technical Standard #35</ulink>
- and <ulink url="https://tools.ietf.org/html/bcp47">BCP 47</ulink> for
- details. The list of possible collation types (<literal>co</literal>
- subtag) can be found in
- the <ulink url="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml">CLDR
- repository</ulink>.
- </para>
+ <para>
+ ICU collations can be created like:
- <para>
- Note that while this system allows creating collations that <quote>ignore
- case</quote> or <quote>ignore accents</quote> or similar (using the
- <literal>ks</literal> key), in order for such collations to act in a
- truly case- or accent-insensitive manner, they also need to be declared as not
- <firstterm>deterministic</firstterm> in <command>CREATE COLLATION</command>;
- see <xref linkend="collation-nondeterministic"/>.
- Otherwise, any strings that compare equal according to the collation but
- are not byte-wise equal will be sorted according to their byte values.
- </para>
+<programlisting>
+CREATE COLLATION german (provider = icu, locale = 'de-DE');
+</programlisting>
- <note>
+ ICU locales are specified as a <link linkend="icu-language-tag">Language
+ Tag</link>, but can also accept most libc-style locale names (which will
+ be transformed into language tags if possible).
+ </para>
<para>
- By design, ICU will accept almost any string as a locale name and match
- it to the closest locale it can provide, using the fallback procedure
- described in its documentation. Thus, there will be no direct feedback
- if a collation specification is composed using features that the given
- ICU installation does not actually support. It is therefore recommended
- to create application-level test cases to check that the collation
- definitions satisfy one's requirements.
+ New ICU collations can customize collation behavior extensively by
+ including collation attributes in the langugage tag. See <xref
+ linkend="icu-custom-collations"/> for details and examples.
</para>
- </note>
</sect4>
-
<sect4 id="collation-copy">
<title>Copying Collations</title>
@@ -1072,6 +1088,404 @@ CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-tr
</tip>
</sect3>
</sect2>
+ <sect2 id="icu-custom-collations">
+ <title>ICU Custom Collations</title>
+
+ <para>
+ ICU allows extensive control over collation behavior by defining new
+ collations with collation settings as a part of the language tag. These
+ settings can modify the collation order to suit a variety of needs. For
+ instance:
+
+<programlisting>
+-- ignore differences in accents and case
+CREATE COLLATION ignore_accent_case (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'und-u-ks-level1');
+SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
+SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true
+
+-- upper case letters sort before lower case.
+CREATE COLLATION upper_first (PROVIDER=icu, LOCALE = 'und-u-kf-upper');
+SELECT 'B' < 'b' COLLATE upper_first; -- true
+
+-- treat digits numerically and ignore punctuation
+CREATE COLLATION num_ignore_punct (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'und-u-ka-shifted-kn');
+SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
+SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true
+</programlisting>
+
+ Many of the available options are described in <xref
+ linkend="icu-collation-settings"/>, or see <xref
+ linkend="icu-external-references"/> for more details.
+ </para>
+ <sect3 id="icu-collation-comparison-levels">
+ <title>ICU Comparison Levels</title>
+ <para>
+ Comparison of two strings (collation) in ICU is determined by a
+ multi-level process, where textual features are grouped into
+ "levels". Treatment of each level is controlled by the <link
+ linkend="icu-collation-settings-table">collation settings</link>. Higher
+ levels correspond to finer textual features.
+ </para>
+ <para>
+ <table id="icu-collation-levels">
+ <title>ICU Collation Levels</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Level</entry>
+ <entry>Description</entry>
+ <entry><literal>'f' = 'f'</literal></entry>
+ <entry><literal>'ab' = U&'a\2063b'</literal></entry>
+ <entry><literal>'x-y' = 'x_y'</literal></entry>
+ <entry><literal>'g' = 'G'</literal></entry>
+ <entry><literal>'n' = 'ñ'</literal></entry>
+ <entry><literal>'y' = 'z'</literal></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>level1</entry>
+ <entry>Base Character</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level2</entry>
+ <entry>Accents</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level3</entry>
+ <entry>Case/Variants</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level4</entry>
+ <entry>Punctuation</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>identic</entry>
+ <entry>All</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ The above table shows which textual feature differences are
+ considered significant when determining equality at the given level. The
+ unicode character <literal>U+2063</literal> is an invisible separator,
+ and as seen in the table, is ignored for at all levels of comparison less
+ than <literal>identic</literal>.
+ </para>
+ <para>
+ Examples:
+
+<programlisting>
+CREATE COLLATION level3 (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-level3');
+CREATE COLLATION level4 (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-level4');
+CREATE COLLATION identic (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-identic');
+
+-- invisible separator ignored at all levels except identic
+SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
+SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false
+
+-- punctuation ignored at level3 but not at level 4
+SELECT 'x-y' = 'x_y' COLLATE level3; -- true
+SELECT 'x-y' = 'x_y' COLLATE level4; -- false
+</programlisting>
+
+ </para>
+ <note>
+ <para>
+ For many collation settings, you must create the collation with
+ <option>DETERMINISTIC</option> set to <literal>false</literal> for the
+ setting to have the desired effect. Additionally, some settings only
+ take effect when the key <literal>ka</literal> is set to
+ <literal>shifted</literal> (see <xref
+ linkend="icu-collation-settings-table"/>).
+ </para>
+ </note>
+ </sect3>
+ <sect3 id="icu-collation-settings">
+ <title>Collation Settings for an ICU Locale</title>
+ <para>
+ <table id="icu-collation-settings-table">
+ <title>ICU Collation Settings</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Key</entry>
+ <entry>Values</entry>
+ <entry>Default</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>co</literal></entry>
+ <entry><literal>emoji</literal>, <literal>phonebk</literal>, <literal>standard</literal>, <replaceable>...</replaceable></entry>
+ <entry><literal>standard</literal></entry>
+ <entry>
+ Collation type. See <xref linkend="icu-external-references"/> for additional options and details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ks</literal></entry>
+ <entry><literal>level1</literal>, <literal>level2</literal>, <literal>level3</literal>, <literal>level4</literal>, <literal>identic</literal></entry>
+ <entry><literal>level3</literal></entry>
+ <entry>
+ Sensitivity when determining equality, with
+ <literal>level1</literal> the least sensitive and
+ <literal>identic</literal> the most sensitive. See <xref
+ linkend="icu-collation-levels"/> for details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ka</literal></entry>
+ <entry><literal>noignore</literal>, <literal>shifted</literal></entry>
+ <entry><literal>noignore</literal></entry>
+ <entry>
+ If set to <literal>shifted</literal>, causes some characters
+ (e.g. punctuation or space) to be ignored in comparison. Key
+ <literal>ks</literal> must be set to <literal>level3</literal> or
+ lower to take effect. Set key <literal>kv</literal> to control which
+ character classes are ignored.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kb</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ Backwards comparison for the level 2 differences. For example,
+ locale <literal>und-u-kb</literal> sorts <literal>'àe'</literal>
+ before <literal>'aé'</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kk</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ <para>
+ Enable full normalization; may affect performance. Basic
+ normalization is performed even when set to
+ <literal>false</literal>.
+ </para>
+ <para>
+ Full normalization is important in some cases, such as when
+ multiple accents are applied to a single character (e.g. in
+ Vietnamese or Arabic). Locales for languages that require full
+ normalization typically enable it by default.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kc</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ <para>
+ Separates case into a "level 2.5" that falls between accents and
+ other level 3 features.
+ </para>
+ <para>
+ If set to <literal>true</literal> and <literal>ks</literal> is set
+ to <literal>level1</literal>, will ignore accents but take case
+ into account.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kf</literal></entry>
+ <entry>
+ <literal>upper</literal>, <literal>lower</literal>,
+ <literal>false</literal>
+ </entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ If set to <literal>upper</literal>, upper case sorts before lower
+ case. If set to <literal>lower</literal>, lower case sorts before
+ upper case. If set to <literal>false</literal>, it depends on the
+ locale.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kn</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ If set to <literal>true</literal>, numbers within a string are
+ treated as a single numeric value rather than a sequence of
+ digits. For example, <literal>'id-45'</literal> sorts before
+ <literal>'id-123'</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kr</literal></entry>
+ <entry>
+ <literal>space</literal>, <literal>punct</literal>,
+ <literal>symbol</literal>, <literal>currency</literal>,
+ <literal>digit</literal>, <replaceable>script-id</replaceable>
+ </entry>
+ <entry></entry>
+ <entry>
+ <para>
+ Set to one or more of the valid values, or any BCP 47
+ <replaceable>script-id</replaceable>, e.g. <literal>latn</literal>
+ ("Latin") or <literal>grek</literal> ("Greek"). Multiple values are
+ separated by "<literal>-</literal>".
+ </para>
+ <para>
+ Redefines the ordering of classes of characters; those characters
+ belonging to a class earlier in the list sort before characters
+ belonging to a class later in the list. For instance, the value
+ <literal>digit-currency-space</literal> (as part of a language tag
+ like <literal>und-u-kr-digit-currency-space</literal>) sorts
+ punctuation before digits and spaces.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kv</literal></entry>
+ <entry>
+ <literal>space</literal>, <literal>punct</literal>,
+ <literal>symbol</literal>, <literal>currency</literal>
+ </entry>
+ <entry><literal>punct</literal></entry>
+ <entry>
+ Classes of characters ignored during comparison at level 3. Setting
+ to a later value includes earlier values;
+ e.g. <literal>symbol</literal> also includes
+ <literal>punct</literal> and <literal>space</literal> in the
+ characters to be ignored. Key <literal>ka</literal> must be set to
+ <literal>shifted</literal> and key <literal>ks</literal> must be set
+ to <literal>level3</literal> or lower to take effect.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ Defaults may depend on locale. The above table is not meant to be
+ complete. See <xref linkend="icu-external-references"/> for additinal
+ options and details.
+ </para>
+ </sect3>
+ <sect3 id="icu-locale-examples">
+ <title>Examples</title>
+ <para>
+ <variablelist>
+ <varlistentry id="collation-managing-create-icu-de-u-co-phonebk-x-icu">
+ <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</literal></term>
+ <listitem>
+ <para>German collation with phone book collation type</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-und-u-co-emoji-x-icu">
+ <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</literal></term>
+ <listitem>
+ <para>
+ Root collation with Emoji collation type, per Unicode Technical Standard #51
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kr-grek-latn">
+ <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</literal></term>
+ <listitem>
+ <para>
+ Sort Greek letters before Latin ones. (The default is Latin before Greek.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kf-upper">
+ <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</literal></term>
+ <listitem>
+ <para>
+ Sort upper-case letters before lower-case letters. (The default is
+ lower-case letters first.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kf-upper-kr-grek-latn">
+ <term><literal>CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</literal></term>
+ <listitem>
+ <para>
+ Combines both of the above options.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </sect3>
+ <sect3 id="icu-external-references">
+ <title>External References for ICU</title>
+ <para>
+ This section (<xref linkend="icu-custom-collations"/>) is only a brief
+ overview of ICU behavior and language tags. Refer to the following
+ documents for technical details, additional options, and new behavior:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <ulink
+ url="https://www.unicode.org/reports/tr35/tr35-collation.html">Unicode
+ Technical Standard #35</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://tools.ietf.org/html/bcp47">BCP 47</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml">CLDR
+ repository</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://unicode-org.github.io/icu/userguide/locale/"></ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://unicode-org.github.io/icu/userguide/collation/api.html"></ulink>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect3>
+ </sect2>
</sect1>
<sect1 id="multibyte">
--
2.34.1
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-09 08:25 Alvaro Herrera <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Alvaro Herrera @ 2023-05-09 08:25 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]; Jeff Davis <[email protected]>
On 2023-Apr-24, Peter Eisentraut wrote:
> The GUC settings lc_collate and lc_ctype are from a time when those locale
> settings were cluster-global. When we made those locale settings
> per-database (PG 8.4), we kept them as read-only. As of PG 15, you can use
> ICU as the per-database locale provider, so what is being attempted in the
> above example is already meaningless before PG 16, since you need to look
> into pg_database to find out what is really happening.
>
> I think we should just remove the GUC parameters lc_collate and lc_ctype.
I agree with removing these in v16, since they are going to become more
meaningless and confusing.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-16 19:35 Jonathan S. Katz <[email protected]>
parent: Jeff Davis <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Jonathan S. Katz @ 2023-05-16 19:35 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On 5/5/23 8:25 PM, Jeff Davis wrote:
> On Fri, 2023-04-21 at 20:12 -0400, Robert Haas wrote:
>> On Fri, Apr 21, 2023 at 5:56 PM Jeff Davis <[email protected]> wrote:
>>> Most of the complaints seem to be complaints about v15 as well, and
>>> while those complaints may be a reason to not make ICU the default,
>>> they are also an argument that we should continue to learn and try
>>> to
>>> fix those issues because they exist in an already-released version.
>>> Leaving it the default for now will help us fix those issues rather
>>> than hide them.
>>>
>>> It's still early, so we have plenty of time to revert the initdb
>>> default if we need to.
>>
>> That's fair enough, but I really think it's important that some
>> energy
>> get invested in providing adequate documentation for this stuff. Just
>> patching the code is not enough.
>
> Attached a significant documentation patch.
> I tried to make it comprehensive without trying to be exhaustive, and I
> separated the explanation of language tags from what collation settings
> you can include in a language tag, so hopefully that's more clear.
>
> I added quite a few examples spread throughout the various sections,
> and I preserved the existing examples at the end. I also left all of
> the external links at the bottom for those interested enough to go
> beyond what's there.
[Personal hat, not RMT]
Thanks -- this is super helpful. A bunch of these examples I had
previously had to figure out by randomly searching blog posts /
trial-and-error, so I think this will help developers get started more
quickly.
Comments (and a lot are just little nits to tighten the language)
Commit message -- typo: "documentaiton"
+ If you see such a message, ensure that the
<symbol>PROVIDER</symbol> and
+ <symbol>LOCALE</symbol> are as you expect, and consider specifying
+ directly as the canonical language tag instead of relying on the
+ transformation.
+ </para>
I'd recommend make this more prescriptive:
"If you see this notice, ensure that the <symbol>PROVIDER</symbol> and
<symbol>LOCALE</symbol> are the expected result. For consistent results
when using the ICU provider, specify the canonical <link
linkend="icu-language-tag">language tag</link> instead of relying on the
transformation."
+ If there is some problem interpreting the locale name, or if it
represents
+ a language or region that ICU does not recognize, a message will
be reported:
This is passive voice, consider:
"If there is a problem interpreting the locale name, or if the locale
name represents a language or region that ICU does not recognize, you'll
see the following error:"
+ <sect3 id="icu-language-tag">
+ <title>Language Tag</title>
+ <para>
Before jumping in, I'd recommend a quick definition of what a language
tag is, e.g.:
"A language tag, defined in BCP 47, is a standardized identifier used to
identify languages in computer systems" or something similar.
(I did find a database that made it simpler to search for these, which
is one issue I've previously add, but I don't think we'd want to link to i)
+ To include this additional collation information in a language tag,
+ append <literal>-u</literal>, followed by one or more
My first question was "what's special about '-u'", so maybe we say:
"To include this additional collation information in a language tag,
append <literal>-u</literal>, which indicates there are additional
collation settings, followed by one or more..."
+ ICU locales are specified as a <link
linkend="icu-language-tag">Language
+ Tag</link>, but can also accept most libc-style locale names
(which will
+ be transformed into language tags if possible).
+ </para>
I'd recommend removing the parantheticals:
ICU locales are specified as a BCP 47 <link
linkend="icu-language-tag">Language
Tag</link>, but can also accept most libc-style locale names. If
possible, libc-style locale names are transformed into language tags.
+ <title>ICU Collation Levels</title>
Nothing to add here other than to say I'm extremely appreciative of this
section. Once upon a time I sunk a lot of time trying to figure out how
all of these levels worked.
+ Sensitivity when determining equality, with
+ <literal>level1</literal> the least sensitive and
+ <literal>identic</literal> the most sensitive. See <xref
+ linkend="icu-collation-levels"/> for details.
This discusses equality sensitivity, but I'm not sure if I understand
that term here. The ICU docs seem to call these "strengths"[1], maybe we
use that term to be consistent with upstream?
+ If set to <literal>upper</literal>, upper case sorts before lower
+ case. If set to <literal>lower</literal>, lower case sorts before
+ upper case. If set to <literal>false</literal>, it depends on the
+ locale.
Suggestion to tighten this up:
"If set to <literal>false</literal>, the sort depends on the rules of
the locale."
+ Defaults may depend on locale. The above table is not meant to be
+ complete. See <xref linkend="icu-external-references"/> for additinal
+ options and details.
Typo: additinal => "additional"
> I didn't add additional documentation for ICU rules. There are so many
> options for collations that it's hard for me to think of realistic
> examples to specify the rules directly, unless someone wants to invent
> a new language. Perhaps useful if working with an interesting text file
> format with special treatment for delimiters?
>
> I asked the question about rules here:
>
> https://www.postgresql.org/message-id/e861ac4fdae9f9f5ce2a938a37bcb5e083f0f489.camel%40cybertec.at
>
> and got some limited response about addressing sort complaints. That
> sounds reasonable, but a lot of that can also be handled just by
> specifying the right collation settings. Someone who understands the
> use case better could add some more documentation.
I'm not too sure about this one -- from my experience, users want
predictability in sorts, but there are a variety of ways to get that
experience.
Thanks,
Jonathan
[1] https://unicode-org.github.io/icu/userguide/collation/concepts.html
Attachments:
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-17 03:23 Jeff Davis <[email protected]>
parent: Jonathan S. Katz <[email protected]>
0 siblings, 1 reply; 45+ messages in thread
From: Jeff Davis @ 2023-05-17 03:23 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Tue, 2023-05-16 at 15:35 -0400, Jonathan S. Katz wrote:
> + Sensitivity when determining equality, with
> + <literal>level1</literal> the least sensitive and
> + <literal>identic</literal> the most sensitive. See <xref
> + linkend="icu-collation-levels"/> for details.
>
> This discusses equality sensitivity, but I'm not sure if I understand
> that term here. The ICU docs seem to call these "strengths"[1], maybe
> we
> use that term to be consistent with upstream?
"Sensitivity" comes from "case sensitivity" which is more clear to me
than "strength". I added the term "strength" to correspond to the
unicode terminology, but I kept sensitivity and I tried to make it
slightly more clear.
Other than that, and I took your suggestions almost verbatim. Patch
attached. Thank you!
I also made a few other changes:
* added paragraph transformation of '' or 'root' to the 'und'
language (root collation)
* added paragraph that the "identic" level still performs some basic
normalization
* added example for when full normalization matters
I should also say that I don't really understand the case when "kc" is
set to true and "ks" is level 2 or higher. If someone has an example of
where that matters, let me know.
Regards,
Jeff Davis
Attachments:
[text/x-patch] v2-0001-Doc-improvements-for-language-tags-and-custom-ICU.patch (30.6K, ../../[email protected]/2-v2-0001-Doc-improvements-for-language-tags-and-custom-ICU.patch)
download | inline diff:
From 8633ec205b0b0297910cef8f931092d0c05eb3ce Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 27 Apr 2023 14:43:46 -0700
Subject: [PATCH v2] Doc improvements for language tags and custom ICU
collations.
Separate the documentation for language tags themselves from the
available collation settings which can be included in a language tag.
Include tables of the available options, more details about the
effects of each option, and additional examples.
Also include an explanation of the "levels" of textual features and
how they relate to collation.
Discussion: https://postgr.es/m/[email protected]
Reviewed-by: Jonathan S. Katz
---
doc/src/sgml/charset.sgml | 680 +++++++++++++++++++++++++++++++-------
1 file changed, 559 insertions(+), 121 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 6dd95b8966..ea43732ec9 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -377,7 +377,134 @@ initdb --locale-provider=icu --icu-locale=en
variants and customization options.
</para>
</sect2>
+ <sect2 id="icu-locales">
+ <title>ICU Locales</title>
+ <sect3 id="icu-locale-names">
+ <title>ICU Locale Names</title>
+ <para>
+ The ICU format for the locale name is a <link
+ linkend="icu-language-tag">Language Tag</link>.
+
+<programlisting>
+CREATE COLLATION mycollation1 (PROVIDER = icu, LOCALE = 'ja-JP);
+CREATE COLLATION mycollation2 (PROVIDER = icu, LOCALE = 'fr');
+</programlisting>
+ </para>
+ </sect3>
+ <sect3 id="icu-canonicalization">
+ <title>Locale Canonicalization and Validation</title>
+ <para>
+ When defining a new ICU collation object or database with ICU as the
+ provider, the given locale name is transformed ("canonicalized") into a
+ language tag if not already in that form. For instance,
+
+<screen>
+CREATE COLLATION mycollation3 (PROVIDER = icu, LOCALE = 'en-US-u-kn-true');
+NOTICE: using standard form "en-US-u-kn" for locale "en-US-u-kn-true"
+CREATE COLLATION mycollation4 (PROVIDER = icu, LOCALE = 'de_DE.utf8');
+NOTICE: using standard form "de-DE" for locale "de_DE.utf8"
+</screen>
+
+ If you see this notice, ensure that the <symbol>PROVIDER</symbol> and
+ <symbol>LOCALE</symbol> are the expected result. For consistent results
+ when using the ICU provider, specify the canonical <link
+ linkend="icu-language-tag">language tag</link> instead of relying on the
+ transformation.
+ </para>
+ <para>
+ A locale with no language name, or the special language name
+ <literal>root</literal>, is transformed to have the language
+ <literal>und</literal> ("undefined").
+ </para>
+ <para>
+ ICU can transform most libc locale names, as well as some other formats,
+ into language tags for easier transition to ICU. If a libc locale name is
+ used in ICU, it may not have precisely the same behavior as in libc.
+ </para>
+ <para>
+ If there is a problem interpreting the locale name, or if the locale name
+ represents a language or region that ICU does not recognize, you will see
+ the following error:
+
+<screen>
+CREATE COLLATION nonsense (PROVIDER = icu, LOCALE = 'nonsense');
+ERROR: ICU locale "nonsense" has unknown language "nonsense"
+HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED.
+</screen>
+
+ <xref
+ linkend="guc-icu-validation-level"/> controls how the message is
+ reported. If set below <literal>ERROR</literal>, the collation will still
+ be created, but the behavior may not be what the user intended.
+ </para>
+ </sect3>
+ <sect3 id="icu-language-tag">
+ <title>Language Tag</title>
+ <para>
+ A language tag, defined in BCP 47, is a standardized identifier used to
+ identify languages, regions, and other information about a locale.
+ </para>
+ <para>
+ Basic language tags are simply
+ <replaceable>language</replaceable><literal>-</literal><replaceable>region</replaceable>;
+ or even just <replaceable>language</replaceable>. The
+ <replaceable>language</replaceable> is a language code
+ (e.g. <literal>fr</literal> for French), and
+ <replaceable>region</replaceable> is a region code
+ (e.g. <literal>CA</literal> for Canada). Examples:
+ <literal>ja-JP</literal>, <literal>de</literal>, or
+ <literal>fr-CA</literal>.
+ </para>
+ <para>
+ Collation settings may be included in the language tag to customize
+ collation behavior. ICU allows extensive customization, such as
+ sensitivity (or insensitivity) to accents, case, and punctuation;
+ treatment of digits within text; and many other options to satisfy a
+ variety of uses.
+ </para>
+ <para>
+ To include this additional collation information in a language tag,
+ append <literal>-u</literal>, which indicates there are additional
+ collation settings, followed by one or more
+ <literal>-</literal><replaceable>key</replaceable><literal>-</literal><replaceable>value</replaceable>
+ pairs. The <replaceable>key</replaceable> is the key for a <link
+ linkend="icu-collation-settings">collation setting</link> and
+ <replaceable>value</replaceable> is a valid value for that setting. For
+ boolean settings, the <literal>-</literal><replaceable>key</replaceable>
+ may be specified without a corresponding
+ <literal>-</literal><replaceable>value</replaceable>, which implies a
+ value of <literal>true</literal>.
+ </para>
+ <para>
+ For example, the language tag <literal>en-US-u-kn-ks-level2</literal>
+ means the locale with the English language in the US region, with
+ collation settings <literal>kn</literal> set to <literal>true</literal>
+ and <literal>ks</literal> set to <literal>level2</literal>. Those
+ settings mean the collation will be case-insensitive and treat a sequence
+ of digits as a single number:
+<screen>
+CREATE COLLATION mycollation5 (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'en-US-u-kn-ks-level2');
+SELECT 'aB' = 'Ab' COLLATE mycollation5 as result;
+ result
+--------
+ t
+(1 row)
+
+SELECT 'N-45' < 'N-123' COLLATE mycollation5 as result;
+ result
+--------
+ t
+(1 row)
+</screen>
+ </para>
+ <para>
+ See <xref linkend="icu-custom-collations"/> for details and additional
+ examples of using language tags with custom collation information for the
+ locale.
+ </para>
+ </sect3>
+ </sect2>
<sect2 id="locale-problems">
<title>Problems</title>
@@ -658,6 +785,13 @@ SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";
code byte values.
</para>
+ <note>
+ <para>
+ The <literal>C</literal> and <literal>POSIX</literal> locales may behave
+ differently depending on the database encoding.
+ </para>
+ </note>
+
<para>
Additionally, two SQL standard collation names are available:
@@ -869,132 +1003,24 @@ CREATE COLLATION german (provider = libc, locale = 'de_DE');
<sect4 id="collation-managing-create-icu">
<title>ICU Collations</title>
- <para>
- ICU allows collations to be customized beyond the basic language+country
- set that is preloaded by <command>initdb</command>. Users are encouraged
- to define their own collation objects that make use of these facilities to
- suit the sorting behavior to their requirements.
- See <ulink url="https://unicode-org.github.io/icu/userguide/locale/"></ulink>
- and <ulink url="https://unicode-org.github.io/icu/userguide/collation/api.html"></ulink> for
- information on ICU locale naming. The set of acceptable names and
- attributes depends on the particular ICU version.
- </para>
-
- <para>
- Here are some examples:
-
- <variablelist>
- <varlistentry id="collation-managing-create-icu-de-u-co-phonebk-x-icu">
- <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</literal></term>
- <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');</literal></term>
- <listitem>
- <para>German collation with phone book collation type</para>
- <para>
- The first example selects the ICU locale using a <quote>language
- tag</quote> per BCP 47. The second example uses the traditional
- ICU-specific locale syntax. The first style is preferred going
- forward, and is used internally to store locales.
- </para>
- <para>
- Note that you can name the collation objects in the SQL environment
- anything you want. In this example, we follow the naming style that
- the predefined collations use, which in turn also follow BCP 47, but
- that is not required for user-defined collations.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-und-u-co-emoji-x-icu">
- <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</literal></term>
- <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');</literal></term>
- <listitem>
- <para>
- Root collation with Emoji collation type, per Unicode Technical Standard #51
- </para>
- <para>
- Observe how in the traditional ICU locale naming system, the root
- locale is selected by an empty string.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kr-grek-latn">
- <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</literal></term>
- <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en@colReorder=grek-latn');</literal></term>
- <listitem>
- <para>
- Sort Greek letters before Latin ones. (The default is Latin before Greek.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kf-upper">
- <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</literal></term>
- <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');</literal></term>
- <listitem>
- <para>
- Sort upper-case letters before lower-case letters. (The default is
- lower-case letters first.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kf-upper-kr-grek-latn">
- <term><literal>CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</literal></term>
- <term><literal>CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');</literal></term>
- <listitem>
- <para>
- Combines both of the above options.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kn-true">
- <term><literal>CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');</literal></term>
- <term><literal>CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');</literal></term>
- <listitem>
- <para>
- Numeric ordering, sorts sequences of digits by their numeric value,
- for example: <literal>A-21</literal> < <literal>A-123</literal>
- (also known as natural sort).
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
-
- See <ulink url="https://www.unicode.org/reports/tr35/tr35-collation.html">Unicode
- Technical Standard #35</ulink>
- and <ulink url="https://tools.ietf.org/html/bcp47">BCP 47</ulink> for
- details. The list of possible collation types (<literal>co</literal>
- subtag) can be found in
- the <ulink url="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml">CLDR
- repository</ulink>.
- </para>
+ <para>
+ ICU collations can be created like:
- <para>
- Note that while this system allows creating collations that <quote>ignore
- case</quote> or <quote>ignore accents</quote> or similar (using the
- <literal>ks</literal> key), in order for such collations to act in a
- truly case- or accent-insensitive manner, they also need to be declared as not
- <firstterm>deterministic</firstterm> in <command>CREATE COLLATION</command>;
- see <xref linkend="collation-nondeterministic"/>.
- Otherwise, any strings that compare equal according to the collation but
- are not byte-wise equal will be sorted according to their byte values.
- </para>
+<programlisting>
+CREATE COLLATION german (provider = icu, locale = 'de-DE');
+</programlisting>
- <note>
+ ICU locales are specified as a BCP 47 <link
+ linkend="icu-language-tag">Language Tag</link>, but can also accept most
+ libc-style locale names. If possible, libc-style locale names are
+ transformed into language tags.
+ </para>
<para>
- By design, ICU will accept almost any string as a locale name and match
- it to the closest locale it can provide, using the fallback procedure
- described in its documentation. Thus, there will be no direct feedback
- if a collation specification is composed using features that the given
- ICU installation does not actually support. It is therefore recommended
- to create application-level test cases to check that the collation
- definitions satisfy one's requirements.
+ New ICU collations can customize collation behavior extensively by
+ including collation attributes in the langugage tag. See <xref
+ linkend="icu-custom-collations"/> for details and examples.
</para>
- </note>
</sect4>
-
<sect4 id="collation-copy">
<title>Copying Collations</title>
@@ -1072,6 +1098,418 @@ CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-tr
</tip>
</sect3>
</sect2>
+ <sect2 id="icu-custom-collations">
+ <title>ICU Custom Collations</title>
+
+ <para>
+ ICU allows extensive control over collation behavior by defining new
+ collations with collation settings as a part of the language tag. These
+ settings can modify the collation order to suit a variety of needs. For
+ instance:
+
+<programlisting>
+-- ignore differences in accents and case
+CREATE COLLATION ignore_accent_case (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'und-u-ks-level1');
+SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
+SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true
+
+-- upper case letters sort before lower case.
+CREATE COLLATION upper_first (PROVIDER=icu, LOCALE = 'und-u-kf-upper');
+SELECT 'B' < 'b' COLLATE upper_first; -- true
+
+-- treat digits numerically and ignore punctuation
+CREATE COLLATION num_ignore_punct (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'und-u-ka-shifted-kn');
+SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
+SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true
+</programlisting>
+
+ Many of the available options are described in <xref
+ linkend="icu-collation-settings"/>, or see <xref
+ linkend="icu-external-references"/> for more details.
+ </para>
+ <sect3 id="icu-collation-comparison-levels">
+ <title>ICU Comparison Levels</title>
+ <para>
+ Comparison of two strings (collation) in ICU is determined by a
+ multi-level process, where textual features are grouped into
+ "levels". Treatment of each level is controlled by the <link
+ linkend="icu-collation-settings-table">collation settings</link>. Higher
+ levels correspond to finer textual features.
+ </para>
+ <para>
+ <table id="icu-collation-levels">
+ <title>ICU Collation Levels</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Level</entry>
+ <entry>Description</entry>
+ <entry><literal>'f' = 'f'</literal></entry>
+ <entry><literal>'ab' = U&'a\2063b'</literal></entry>
+ <entry><literal>'x-y' = 'x_y'</literal></entry>
+ <entry><literal>'g' = 'G'</literal></entry>
+ <entry><literal>'n' = 'ñ'</literal></entry>
+ <entry><literal>'y' = 'z'</literal></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>level1</entry>
+ <entry>Base Character</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level2</entry>
+ <entry>Accents</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level3</entry>
+ <entry>Case/Variants</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level4</entry>
+ <entry>Punctuation</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>identic</entry>
+ <entry>All</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ The above table shows which textual feature differences are
+ considered significant when determining equality at the given level. The
+ unicode character <literal>U+2063</literal> is an invisible separator,
+ and as seen in the table, is ignored for at all levels of comparison less
+ than <literal>identic</literal>.
+ </para>
+ <para>
+ At every level, even with full normalization off, basic normalization is
+ performed. For example, <literal>'á'</literal> may be composed of the
+ code points <literal>U&'\0061\0301'</literal> or the single code
+ point <literal>U&'\00E1'</literal>, and those sequences will be
+ considered equal even at the <literal>identic</literal> level. To treat
+ any difference in code point representation as distinct, use a collation
+ created with <symbol>DETERMINISTIC</symbol> set to
+ <literal>false</literal>.
+ </para>
+ <para>
+ Examples:
+
+<programlisting>
+CREATE COLLATION level3 (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-level3');
+CREATE COLLATION level4 (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-level4');
+CREATE COLLATION identic (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-identic');
+
+-- invisible separator ignored at all levels except identic
+SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
+SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false
+
+-- punctuation ignored at level3 but not at level 4
+SELECT 'x-y' = 'x_y' COLLATE level3; -- true
+SELECT 'x-y' = 'x_y' COLLATE level4; -- false
+</programlisting>
+
+ </para>
+ <note>
+ <para>
+ For many collation settings, you must create the collation with
+ <option>DETERMINISTIC</option> set to <literal>false</literal> for the
+ setting to have the desired effect. Additionally, some settings only
+ take effect when the key <literal>ka</literal> is set to
+ <literal>shifted</literal> (see <xref
+ linkend="icu-collation-settings-table"/>).
+ </para>
+ </note>
+ </sect3>
+ <sect3 id="icu-collation-settings">
+ <title>Collation Settings for an ICU Locale</title>
+ <para>
+ <table id="icu-collation-settings-table">
+ <title>ICU Collation Settings</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Key</entry>
+ <entry>Values</entry>
+ <entry>Default</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>co</literal></entry>
+ <entry><literal>emoji</literal>, <literal>phonebk</literal>, <literal>standard</literal>, <replaceable>...</replaceable></entry>
+ <entry><literal>standard</literal></entry>
+ <entry>
+ Collation type. See <xref linkend="icu-external-references"/> for additional options and details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ks</literal></entry>
+ <entry><literal>level1</literal>, <literal>level2</literal>, <literal>level3</literal>, <literal>level4</literal>, <literal>identic</literal></entry>
+ <entry><literal>level3</literal></entry>
+ <entry>
+ Sensitivity (or "strength") when determining equality, with
+ <literal>level1</literal> the least sensitive to differences and
+ <literal>identic</literal> the most sensitive to differences. See
+ <xref linkend="icu-collation-levels"/> for details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ka</literal></entry>
+ <entry><literal>noignore</literal>, <literal>shifted</literal></entry>
+ <entry><literal>noignore</literal></entry>
+ <entry>
+ If set to <literal>shifted</literal>, causes some characters
+ (e.g. punctuation or space) to be ignored in comparison. Key
+ <literal>ks</literal> must be set to <literal>level3</literal> or
+ lower to take effect. Set key <literal>kv</literal> to control which
+ character classes are ignored.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kb</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ Backwards comparison for the level 2 differences. For example,
+ locale <literal>und-u-kb</literal> sorts <literal>'àe'</literal>
+ before <literal>'aé'</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kk</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ <para>
+ Enable full normalization; may affect performance. Basic
+ normalization is performed even when set to
+ <literal>false</literal>. Locales for languages that require full
+ normalization typically enable it by default.
+ </para>
+ <para>
+ Full normalization is important in some cases, such as when
+ multiple accents are applied to a single character. For instance,
+ <literal>'ệ'</literal> can be composed of code points
+ <literal>U&'\0065\0323\0302'</literal> or
+ <literal>U&'\0065\0302\0323'</literal>. With full normalization
+ on, these code point sequences are treated as equal; otherwise they
+ are unequal.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kc</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ <para>
+ Separates case into a "level 2.5" that falls between accents and
+ other level 3 features.
+ </para>
+ <para>
+ If set to <literal>true</literal> and <literal>ks</literal> is set
+ to <literal>level1</literal>, will ignore accents but take case
+ into account.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kf</literal></entry>
+ <entry>
+ <literal>upper</literal>, <literal>lower</literal>,
+ <literal>false</literal>
+ </entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ If set to <literal>upper</literal>, upper case sorts before lower
+ case. If set to <literal>lower</literal>, lower case sorts before
+ upper case. If set to <literal>false</literal>, the sort depends on
+ the rules of the locale.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kn</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ If set to <literal>true</literal>, numbers within a string are
+ treated as a single numeric value rather than a sequence of
+ digits. For example, <literal>'id-45'</literal> sorts before
+ <literal>'id-123'</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kr</literal></entry>
+ <entry>
+ <literal>space</literal>, <literal>punct</literal>,
+ <literal>symbol</literal>, <literal>currency</literal>,
+ <literal>digit</literal>, <replaceable>script-id</replaceable>
+ </entry>
+ <entry></entry>
+ <entry>
+ <para>
+ Set to one or more of the valid values, or any BCP 47
+ <replaceable>script-id</replaceable>, e.g. <literal>latn</literal>
+ ("Latin") or <literal>grek</literal> ("Greek"). Multiple values are
+ separated by "<literal>-</literal>".
+ </para>
+ <para>
+ Redefines the ordering of classes of characters; those characters
+ belonging to a class earlier in the list sort before characters
+ belonging to a class later in the list. For instance, the value
+ <literal>digit-currency-space</literal> (as part of a language tag
+ like <literal>und-u-kr-digit-currency-space</literal>) sorts
+ punctuation before digits and spaces.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kv</literal></entry>
+ <entry>
+ <literal>space</literal>, <literal>punct</literal>,
+ <literal>symbol</literal>, <literal>currency</literal>
+ </entry>
+ <entry><literal>punct</literal></entry>
+ <entry>
+ Classes of characters ignored during comparison at level 3. Setting
+ to a later value includes earlier values;
+ e.g. <literal>symbol</literal> also includes
+ <literal>punct</literal> and <literal>space</literal> in the
+ characters to be ignored. Key <literal>ka</literal> must be set to
+ <literal>shifted</literal> and key <literal>ks</literal> must be set
+ to <literal>level3</literal> or lower to take effect.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ Defaults may depend on locale. The above table is not meant to be
+ complete. See <xref linkend="icu-external-references"/> for additional
+ options and details.
+ </para>
+ </sect3>
+ <sect3 id="icu-locale-examples">
+ <title>Examples</title>
+ <para>
+ <variablelist>
+ <varlistentry id="collation-managing-create-icu-de-u-co-phonebk-x-icu">
+ <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</literal></term>
+ <listitem>
+ <para>German collation with phone book collation type</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-und-u-co-emoji-x-icu">
+ <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</literal></term>
+ <listitem>
+ <para>
+ Root collation with Emoji collation type, per Unicode Technical Standard #51
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kr-grek-latn">
+ <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</literal></term>
+ <listitem>
+ <para>
+ Sort Greek letters before Latin ones. (The default is Latin before Greek.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kf-upper">
+ <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</literal></term>
+ <listitem>
+ <para>
+ Sort upper-case letters before lower-case letters. (The default is
+ lower-case letters first.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kf-upper-kr-grek-latn">
+ <term><literal>CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</literal></term>
+ <listitem>
+ <para>
+ Combines both of the above options.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </sect3>
+ <sect3 id="icu-external-references">
+ <title>External References for ICU</title>
+ <para>
+ This section (<xref linkend="icu-custom-collations"/>) is only a brief
+ overview of ICU behavior and language tags. Refer to the following
+ documents for technical details, additional options, and new behavior:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <ulink
+ url="https://www.unicode.org/reports/tr35/tr35-collation.html">Unicode
+ Technical Standard #35</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://tools.ietf.org/html/bcp47">BCP 47</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml">CLDR
+ repository</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://unicode-org.github.io/icu/userguide/locale/"></ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://unicode-org.github.io/icu/userguide/collation/api.html"></ulink>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect3>
+ </sect2>
</sect1>
<sect1 id="multibyte">
--
2.34.1
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-17 22:59 Jeff Davis <[email protected]>
parent: Jeff Davis <[email protected]>
0 siblings, 2 replies; 45+ messages in thread
From: Jeff Davis @ 2023-05-17 22:59 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Tue, 2023-05-16 at 20:23 -0700, Jeff Davis wrote:
> Other than that, and I took your suggestions almost verbatim. Patch
> attached. Thank you!
Attached new patch with a typo fix and a few other edits. I plan to
commit soon.
Regards,
Jeff Davis
Attachments:
[text/x-patch] 0001-Doc-improvements-for-language-tags-and-custom-ICU-co.patch (30.7K, ../../[email protected]/2-0001-Doc-improvements-for-language-tags-and-custom-ICU-co.patch)
download | inline diff:
From d0d2375fa55618b60f361f6bb64b2c49490125b9 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 27 Apr 2023 14:43:46 -0700
Subject: [PATCH] Doc improvements for language tags and custom ICU collations.
Separate the documentation for language tags themselves from the
available collation settings which can be included in a language tag.
Include tables of the available options, more details about the
effects of each option, and additional examples.
Also include an explanation of the "levels" of textual features and
how they relate to collation.
Discussion: https://postgr.es/m/[email protected]
Reviewed-by: Jonathan S. Katz
---
doc/src/sgml/charset.sgml | 683 +++++++++++++++++++++++++++++++-------
1 file changed, 562 insertions(+), 121 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 6dd95b8966..6b9c323edd 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -377,7 +377,134 @@ initdb --locale-provider=icu --icu-locale=en
variants and customization options.
</para>
</sect2>
+ <sect2 id="icu-locales">
+ <title>ICU Locales</title>
+ <sect3 id="icu-locale-names">
+ <title>ICU Locale Names</title>
+ <para>
+ The ICU format for the locale name is a <link
+ linkend="icu-language-tag">Language Tag</link>.
+
+<programlisting>
+CREATE COLLATION mycollation1 (PROVIDER = icu, LOCALE = 'ja-JP);
+CREATE COLLATION mycollation2 (PROVIDER = icu, LOCALE = 'fr');
+</programlisting>
+ </para>
+ </sect3>
+ <sect3 id="icu-canonicalization">
+ <title>Locale Canonicalization and Validation</title>
+ <para>
+ When defining a new ICU collation object or database with ICU as the
+ provider, the given locale name is transformed ("canonicalized") into a
+ language tag if not already in that form. For instance,
+
+<screen>
+CREATE COLLATION mycollation3 (PROVIDER = icu, LOCALE = 'en-US-u-kn-true');
+NOTICE: using standard form "en-US-u-kn" for locale "en-US-u-kn-true"
+CREATE COLLATION mycollation4 (PROVIDER = icu, LOCALE = 'de_DE.utf8');
+NOTICE: using standard form "de-DE" for locale "de_DE.utf8"
+</screen>
+
+ If you see this notice, ensure that the <symbol>PROVIDER</symbol> and
+ <symbol>LOCALE</symbol> are the expected result. For consistent results
+ when using the ICU provider, specify the canonical <link
+ linkend="icu-language-tag">language tag</link> instead of relying on the
+ transformation.
+ </para>
+ <para>
+ A locale with no language name, or the special language name
+ <literal>root</literal>, is transformed to have the language
+ <literal>und</literal> ("undefined").
+ </para>
+ <para>
+ ICU can transform most libc locale names, as well as some other formats,
+ into language tags for easier transition to ICU. If a libc locale name is
+ used in ICU, it may not have precisely the same behavior as in libc.
+ </para>
+ <para>
+ If there is a problem interpreting the locale name, or if the locale name
+ represents a language or region that ICU does not recognize, you will see
+ the following warning:
+
+<screen>
+CREATE COLLATION nonsense (PROVIDER = icu, LOCALE = 'nonsense');
+WARNING: ICU locale "nonsense" has unknown language "nonsense"
+HINT: To disable ICU locale validation, set parameter icu_validation_level to DISABLED.
+CREATE COLLATION
+</screen>
+
+ <xref linkend="guc-icu-validation-level"/> controls how the message is
+ reported. Unless set to <literal>ERROR</literal>, the collation will
+ still be created, but the behavior may not be what the user intended.
+ </para>
+ </sect3>
+ <sect3 id="icu-language-tag">
+ <title>Language Tag</title>
+ <para>
+ A language tag, defined in BCP 47, is a standardized identifier used to
+ identify languages, regions, and other information about a locale.
+ </para>
+ <para>
+ Basic language tags are simply
+ <replaceable>language</replaceable><literal>-</literal><replaceable>region</replaceable>;
+ or even just <replaceable>language</replaceable>. The
+ <replaceable>language</replaceable> is a language code
+ (e.g. <literal>fr</literal> for French), and
+ <replaceable>region</replaceable> is a region code
+ (e.g. <literal>CA</literal> for Canada). Examples:
+ <literal>ja-JP</literal>, <literal>de</literal>, or
+ <literal>fr-CA</literal>.
+ </para>
+ <para>
+ Collation settings may be included in the language tag to customize
+ collation behavior. ICU allows extensive customization, such as
+ sensitivity (or insensitivity) to accents, case, and punctuation;
+ treatment of digits within text; and many other options to satisfy a
+ variety of uses.
+ </para>
+ <para>
+ To include this additional collation information in a language tag,
+ append <literal>-u</literal>, which indicates there are additional
+ collation settings, followed by one or more
+ <literal>-</literal><replaceable>key</replaceable><literal>-</literal><replaceable>value</replaceable>
+ pairs. The <replaceable>key</replaceable> is the key for a <link
+ linkend="icu-collation-settings">collation setting</link> and
+ <replaceable>value</replaceable> is a valid value for that setting. For
+ boolean settings, the <literal>-</literal><replaceable>key</replaceable>
+ may be specified without a corresponding
+ <literal>-</literal><replaceable>value</replaceable>, which implies a
+ value of <literal>true</literal>.
+ </para>
+ <para>
+ For example, the language tag <literal>en-US-u-kn-ks-level2</literal>
+ means the locale with the English language in the US region, with
+ collation settings <literal>kn</literal> set to <literal>true</literal>
+ and <literal>ks</literal> set to <literal>level2</literal>. Those
+ settings mean the collation will be case-insensitive and treat a sequence
+ of digits as a single number:
+<screen>
+CREATE COLLATION mycollation5 (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'en-US-u-kn-ks-level2');
+SELECT 'aB' = 'Ab' COLLATE mycollation5 as result;
+ result
+--------
+ t
+(1 row)
+
+SELECT 'N-45' < 'N-123' COLLATE mycollation5 as result;
+ result
+--------
+ t
+(1 row)
+</screen>
+ </para>
+ <para>
+ See <xref linkend="icu-custom-collations"/> for details and additional
+ examples of using language tags with custom collation information for the
+ locale.
+ </para>
+ </sect3>
+ </sect2>
<sect2 id="locale-problems">
<title>Problems</title>
@@ -658,6 +785,13 @@ SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";
code byte values.
</para>
+ <note>
+ <para>
+ The <literal>C</literal> and <literal>POSIX</literal> locales may behave
+ differently depending on the database encoding.
+ </para>
+ </note>
+
<para>
Additionally, two SQL standard collation names are available:
@@ -869,132 +1003,24 @@ CREATE COLLATION german (provider = libc, locale = 'de_DE');
<sect4 id="collation-managing-create-icu">
<title>ICU Collations</title>
- <para>
- ICU allows collations to be customized beyond the basic language+country
- set that is preloaded by <command>initdb</command>. Users are encouraged
- to define their own collation objects that make use of these facilities to
- suit the sorting behavior to their requirements.
- See <ulink url="https://unicode-org.github.io/icu/userguide/locale/"></ulink>
- and <ulink url="https://unicode-org.github.io/icu/userguide/collation/api.html"></ulink> for
- information on ICU locale naming. The set of acceptable names and
- attributes depends on the particular ICU version.
- </para>
-
- <para>
- Here are some examples:
-
- <variablelist>
- <varlistentry id="collation-managing-create-icu-de-u-co-phonebk-x-icu">
- <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</literal></term>
- <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');</literal></term>
- <listitem>
- <para>German collation with phone book collation type</para>
- <para>
- The first example selects the ICU locale using a <quote>language
- tag</quote> per BCP 47. The second example uses the traditional
- ICU-specific locale syntax. The first style is preferred going
- forward, and is used internally to store locales.
- </para>
- <para>
- Note that you can name the collation objects in the SQL environment
- anything you want. In this example, we follow the naming style that
- the predefined collations use, which in turn also follow BCP 47, but
- that is not required for user-defined collations.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-und-u-co-emoji-x-icu">
- <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</literal></term>
- <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');</literal></term>
- <listitem>
- <para>
- Root collation with Emoji collation type, per Unicode Technical Standard #51
- </para>
- <para>
- Observe how in the traditional ICU locale naming system, the root
- locale is selected by an empty string.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kr-grek-latn">
- <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</literal></term>
- <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en@colReorder=grek-latn');</literal></term>
- <listitem>
- <para>
- Sort Greek letters before Latin ones. (The default is Latin before Greek.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kf-upper">
- <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</literal></term>
- <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');</literal></term>
- <listitem>
- <para>
- Sort upper-case letters before lower-case letters. (The default is
- lower-case letters first.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kf-upper-kr-grek-latn">
- <term><literal>CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</literal></term>
- <term><literal>CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');</literal></term>
- <listitem>
- <para>
- Combines both of the above options.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry id="collation-managing-create-icu-en-u-kn-true">
- <term><literal>CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');</literal></term>
- <term><literal>CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');</literal></term>
- <listitem>
- <para>
- Numeric ordering, sorts sequences of digits by their numeric value,
- for example: <literal>A-21</literal> < <literal>A-123</literal>
- (also known as natural sort).
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
-
- See <ulink url="https://www.unicode.org/reports/tr35/tr35-collation.html">Unicode
- Technical Standard #35</ulink>
- and <ulink url="https://tools.ietf.org/html/bcp47">BCP 47</ulink> for
- details. The list of possible collation types (<literal>co</literal>
- subtag) can be found in
- the <ulink url="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml">CLDR
- repository</ulink>.
- </para>
+ <para>
+ ICU collations can be created like:
- <para>
- Note that while this system allows creating collations that <quote>ignore
- case</quote> or <quote>ignore accents</quote> or similar (using the
- <literal>ks</literal> key), in order for such collations to act in a
- truly case- or accent-insensitive manner, they also need to be declared as not
- <firstterm>deterministic</firstterm> in <command>CREATE COLLATION</command>;
- see <xref linkend="collation-nondeterministic"/>.
- Otherwise, any strings that compare equal according to the collation but
- are not byte-wise equal will be sorted according to their byte values.
- </para>
+<programlisting>
+CREATE COLLATION german (provider = icu, locale = 'de-DE');
+</programlisting>
- <note>
+ ICU locales are specified as a BCP 47 <link
+ linkend="icu-language-tag">Language Tag</link>, but can also accept most
+ libc-style locale names. If possible, libc-style locale names are
+ transformed into language tags.
+ </para>
<para>
- By design, ICU will accept almost any string as a locale name and match
- it to the closest locale it can provide, using the fallback procedure
- described in its documentation. Thus, there will be no direct feedback
- if a collation specification is composed using features that the given
- ICU installation does not actually support. It is therefore recommended
- to create application-level test cases to check that the collation
- definitions satisfy one's requirements.
+ New ICU collations can customize collation behavior extensively by
+ including collation attributes in the langugage tag. See <xref
+ linkend="icu-custom-collations"/> for details and examples.
</para>
- </note>
</sect4>
-
<sect4 id="collation-copy">
<title>Copying Collations</title>
@@ -1072,6 +1098,421 @@ CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-tr
</tip>
</sect3>
</sect2>
+ <sect2 id="icu-custom-collations">
+ <title>ICU Custom Collations</title>
+
+ <para>
+ ICU allows extensive control over collation behavior by defining new
+ collations with collation settings as a part of the language tag. These
+ settings can modify the collation order to suit a variety of needs. For
+ instance:
+
+<programlisting>
+-- ignore differences in accents and case
+CREATE COLLATION ignore_accent_case (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'und-u-ks-level1');
+SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
+SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true
+
+-- upper case letters sort before lower case.
+CREATE COLLATION upper_first (PROVIDER=icu, LOCALE = 'und-u-kf-upper');
+SELECT 'B' < 'b' COLLATE upper_first; -- true
+
+-- treat digits numerically and ignore punctuation
+CREATE COLLATION num_ignore_punct (PROVIDER = icu, DETERMINISTIC = false, LOCALE = 'und-u-ka-shifted-kn');
+SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
+SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true
+</programlisting>
+
+ Many of the available options are described in <xref
+ linkend="icu-collation-settings"/>, or see <xref
+ linkend="icu-external-references"/> for more details.
+ </para>
+ <sect3 id="icu-collation-comparison-levels">
+ <title>ICU Comparison Levels</title>
+ <para>
+ Comparison of two strings (collation) in ICU is determined by a
+ multi-level process, where textual features are grouped into
+ "levels". Treatment of each level is controlled by the <link
+ linkend="icu-collation-settings-table">collation settings</link>. Higher
+ levels correspond to finer textual features.
+ </para>
+ <para>
+ <table id="icu-collation-levels">
+ <title>ICU Collation Levels</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Level</entry>
+ <entry>Description</entry>
+ <entry><literal>'f' = 'f'</literal></entry>
+ <entry><literal>'ab' = U&'a\2063b'</literal></entry>
+ <entry><literal>'x-y' = 'x_y'</literal></entry>
+ <entry><literal>'g' = 'G'</literal></entry>
+ <entry><literal>'n' = 'ñ'</literal></entry>
+ <entry><literal>'y' = 'z'</literal></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>level1</entry>
+ <entry>Base Character</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level2</entry>
+ <entry>Accents</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level3</entry>
+ <entry>Case/Variants</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>level4</entry>
+ <entry>Punctuation</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ <row>
+ <entry>identic</entry>
+ <entry>All</entry>
+ <entry><literal>true</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ The above table shows which textual feature differences are
+ considered significant when determining equality at the given level. The
+ unicode character <literal>U+2063</literal> is an invisible separator,
+ and as seen in the table, is ignored for at all levels of comparison less
+ than <literal>identic</literal>.
+ </para>
+ <para>
+ At every level, even with full normalization off, basic normalization is
+ performed. For example, <literal>'á'</literal> may be composed of the
+ code points <literal>U&'\0061\0301'</literal> or the single code
+ point <literal>U&'\00E1'</literal>, and those sequences will be
+ considered equal even at the <literal>identic</literal> level. To treat
+ any difference in code point representation as distinct, use a collation
+ created with <symbol>DETERMINISTIC</symbol> set to
+ <literal>true</literal>.
+ </para>
+ <sect4 id="icu-collation-level-examples">
+ <title>Collation Level Examples</title>
+ <para>
+
+<programlisting>
+CREATE COLLATION level3 (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-level3');
+CREATE COLLATION level4 (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-level4');
+CREATE COLLATION identic (PROVIDER=icu, DETERMINISTIC=false, LOCALE='und-u-ka-shifted-ks-identic');
+
+-- invisible separator ignored at all levels except identic
+SELECT 'ab' = U&'a\2063b' COLLATE level4; -- true
+SELECT 'ab' = U&'a\2063b' COLLATE identic; -- false
+
+-- punctuation ignored at level3 but not at level 4
+SELECT 'x-y' = 'x_y' COLLATE level3; -- true
+SELECT 'x-y' = 'x_y' COLLATE level4; -- false
+</programlisting>
+
+ </para>
+ </sect4>
+ </sect3>
+ <sect3 id="icu-collation-settings">
+ <title>Collation Settings for an ICU Locale</title>
+ <para>
+ <table id="icu-collation-settings-table">
+ <title>ICU Collation Settings</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Key</entry>
+ <entry>Values</entry>
+ <entry>Default</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>co</literal></entry>
+ <entry><literal>emoji</literal>, <literal>phonebk</literal>, <literal>standard</literal>, <replaceable>...</replaceable></entry>
+ <entry><literal>standard</literal></entry>
+ <entry>
+ Collation type. See <xref linkend="icu-external-references"/> for additional options and details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ks</literal></entry>
+ <entry><literal>level1</literal>, <literal>level2</literal>, <literal>level3</literal>, <literal>level4</literal>, <literal>identic</literal></entry>
+ <entry><literal>level3</literal></entry>
+ <entry>
+ Sensitivity (or "strength") when determining equality, with
+ <literal>level1</literal> the least sensitive to differences and
+ <literal>identic</literal> the most sensitive to differences. See
+ <xref linkend="icu-collation-levels"/> for details.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>ka</literal></entry>
+ <entry><literal>noignore</literal>, <literal>shifted</literal></entry>
+ <entry><literal>noignore</literal></entry>
+ <entry>
+ If set to <literal>shifted</literal>, causes some characters
+ (e.g. punctuation or space) to be ignored in comparison. Key
+ <literal>ks</literal> must be set to <literal>level3</literal> or
+ lower to take effect. Set key <literal>kv</literal> to control which
+ character classes are ignored.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kb</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ Backwards comparison for the level 2 differences. For example,
+ locale <literal>und-u-kb</literal> sorts <literal>'àe'</literal>
+ before <literal>'aé'</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kk</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ <para>
+ Enable full normalization; may affect performance. Basic
+ normalization is performed even when set to
+ <literal>false</literal>. Locales for languages that require full
+ normalization typically enable it by default.
+ </para>
+ <para>
+ Full normalization is important in some cases, such as when
+ multiple accents are applied to a single character. For instance,
+ <literal>'ệ'</literal> can be composed of code points
+ <literal>U&'\0065\0323\0302'</literal> or
+ <literal>U&'\0065\0302\0323'</literal>. With full normalization
+ on, these code point sequences are treated as equal; otherwise they
+ are unequal.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kc</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ <para>
+ Separates case into a "level 2.5" that falls between accents and
+ other level 3 features.
+ </para>
+ <para>
+ If set to <literal>true</literal> and <literal>ks</literal> is set
+ to <literal>level1</literal>, will ignore accents but take case
+ into account.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kf</literal></entry>
+ <entry>
+ <literal>upper</literal>, <literal>lower</literal>,
+ <literal>false</literal>
+ </entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ If set to <literal>upper</literal>, upper case sorts before lower
+ case. If set to <literal>lower</literal>, lower case sorts before
+ upper case. If set to <literal>false</literal>, the sort depends on
+ the rules of the locale.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kn</literal></entry>
+ <entry><literal>true</literal>, <literal>false</literal></entry>
+ <entry><literal>false</literal></entry>
+ <entry>
+ If set to <literal>true</literal>, numbers within a string are
+ treated as a single numeric value rather than a sequence of
+ digits. For example, <literal>'id-45'</literal> sorts before
+ <literal>'id-123'</literal>.
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kr</literal></entry>
+ <entry>
+ <literal>space</literal>, <literal>punct</literal>,
+ <literal>symbol</literal>, <literal>currency</literal>,
+ <literal>digit</literal>, <replaceable>script-id</replaceable>
+ </entry>
+ <entry></entry>
+ <entry>
+ <para>
+ Set to one or more of the valid values, or any BCP 47
+ <replaceable>script-id</replaceable>, e.g. <literal>latn</literal>
+ ("Latin") or <literal>grek</literal> ("Greek"). Multiple values are
+ separated by "<literal>-</literal>".
+ </para>
+ <para>
+ Redefines the ordering of classes of characters; those characters
+ belonging to a class earlier in the list sort before characters
+ belonging to a class later in the list. For instance, the value
+ <literal>digit-currency-space</literal> (as part of a language tag
+ like <literal>und-u-kr-digit-currency-space</literal>) sorts
+ punctuation before digits and spaces.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry><literal>kv</literal></entry>
+ <entry>
+ <literal>space</literal>, <literal>punct</literal>,
+ <literal>symbol</literal>, <literal>currency</literal>
+ </entry>
+ <entry><literal>punct</literal></entry>
+ <entry>
+ Classes of characters ignored during comparison at level 3. Setting
+ to a later value includes earlier values;
+ e.g. <literal>symbol</literal> also includes
+ <literal>punct</literal> and <literal>space</literal> in the
+ characters to be ignored. Key <literal>ka</literal> must be set to
+ <literal>shifted</literal> and key <literal>ks</literal> must be set
+ to <literal>level3</literal> or lower to take effect.
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ Defaults may depend on locale. The above table is not meant to be
+ complete. See <xref linkend="icu-external-references"/> for additional
+ options and details.
+ </para>
+ <note>
+ <para>
+ For many collation settings, you must create the collation with
+ <option>DETERMINISTIC</option> set to <literal>false</literal> for the
+ setting to have the desired effect (see <xref
+ linkend="collation-nondeterministic"/>). Additionally, some settings
+ only take effect when the key <literal>ka</literal> is set to
+ <literal>shifted</literal> (see <xref
+ linkend="icu-collation-settings-table"/>).
+ </para>
+ </note>
+ </sect3>
+ <sect3 id="icu-locale-examples">
+ <title>Examples</title>
+ <para>
+ <variablelist>
+ <varlistentry id="collation-managing-create-icu-de-u-co-phonebk-x-icu">
+ <term><literal>CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');</literal></term>
+ <listitem>
+ <para>German collation with phone book collation type</para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-und-u-co-emoji-x-icu">
+ <term><literal>CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');</literal></term>
+ <listitem>
+ <para>
+ Root collation with Emoji collation type, per Unicode Technical Standard #51
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kr-grek-latn">
+ <term><literal>CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn');</literal></term>
+ <listitem>
+ <para>
+ Sort Greek letters before Latin ones. (The default is Latin before Greek.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kf-upper">
+ <term><literal>CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');</literal></term>
+ <listitem>
+ <para>
+ Sort upper-case letters before lower-case letters. (The default is
+ lower-case letters first.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="collation-managing-create-icu-en-u-kf-upper-kr-grek-latn">
+ <term><literal>CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn');</literal></term>
+ <listitem>
+ <para>
+ Combines both of the above options.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </sect3>
+ <sect3 id="icu-external-references">
+ <title>External References for ICU</title>
+ <para>
+ This section (<xref linkend="icu-custom-collations"/>) is only a brief
+ overview of ICU behavior and language tags. Refer to the following
+ documents for technical details, additional options, and new behavior:
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ <ulink
+ url="https://www.unicode.org/reports/tr35/tr35-collation.html">Unicode
+ Technical Standard #35</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://tools.ietf.org/html/bcp47">BCP 47</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml">CLDR
+ repository</ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://unicode-org.github.io/icu/userguide/locale/"></ulink>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <ulink url="https://unicode-org.github.io/icu/userguide/collation/api.html"></ulink>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect3>
+ </sect2>
</sect1>
<sect1 id="multibyte">
--
2.34.1
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-17 23:59 Jonathan S. Katz <[email protected]>
parent: Jeff Davis <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Jonathan S. Katz @ 2023-05-17 23:59 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On 5/17/23 6:59 PM, Jeff Davis wrote:
> On Tue, 2023-05-16 at 20:23 -0700, Jeff Davis wrote:
>> Other than that, and I took your suggestions almost verbatim. Patch
>> attached. Thank you!
>
> Attached new patch with a typo fix and a few other edits. I plan to
> commit soon.
I did a quicker read through this time. LGTM overall. I like what you
did with the explanations around sensitivity (now it makes sense).
Thanks,
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-18 17:55 Jeff Davis <[email protected]>
parent: Jonathan S. Katz <[email protected]>
0 siblings, 3 replies; 45+ messages in thread
From: Jeff Davis @ 2023-05-18 17:55 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Wed, 2023-05-17 at 19:59 -0400, Jonathan S. Katz wrote:
> I did a quicker read through this time. LGTM overall. I like what you
> did with the explanations around sensitivity (now it makes sense).
Committed, thank you.
There are a few things I don't understand that would be good to
document better:
* Rules. I still don't quite understand the use case: are these for
people inventing new languages? What is a plausible use case that isn't
covered by the existing locales and collation settings? Do rules make
sense for a database default collation? Are they for language experts
only or might an ordinary developer benefit from using them?
* The collation types "phonebk", "emoji", etc.: are these variants of
particular locales, or do they make sense in multiple locales? I don't
know where they fit in or how to document them.
* I don't understand what "kc" means if "ks" is not set to "level1".
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-18 17:58 Jonathan S. Katz <[email protected]>
parent: Jeff Davis <[email protected]>
2 siblings, 1 reply; 45+ messages in thread
From: Jonathan S. Katz @ 2023-05-18 17:58 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On 5/18/23 1:55 PM, Jeff Davis wrote:
> On Wed, 2023-05-17 at 19:59 -0400, Jonathan S. Katz wrote:
>> I did a quicker read through this time. LGTM overall. I like what you
>> did with the explanations around sensitivity (now it makes sense).
>
> Committed, thank you.
\o/
> There are a few things I don't understand that would be good to
> document better:
>
> * Rules. I still don't quite understand the use case: are these for
> people inventing new languages? What is a plausible use case that isn't
> covered by the existing locales and collation settings? Do rules make
> sense for a database default collation? Are they for language experts
> only or might an ordinary developer benefit from using them?
From my read of them, as an app developer I'd be very unlikely to use
this. Maybe there is something with building out some collation rules
vis-a-vis an extension, but I have trouble imagining the use-case. I may
also not be the target audience for this feature.
> * The collation types "phonebk", "emoji", etc.: are these variants of
> particular locales, or do they make sense in multiple locales? I don't
> know where they fit in or how to document them.
I remember I had a exploratory use case for "phonebk" but I couldn't
figure out how to get it to work. AIUI from random searching, the idea
is that it provides the "phonebook" rules for ordering "names" in a
particular locale, but I couldn't get it to work.
> * I don't understand what "kc" means if "ks" is not set to "level1".
Me neither, but I haven't stared at this as hard as others.
Thanks,
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-18 18:11 Matthias van de Meent <[email protected]>
parent: Jeff Davis <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Matthias van de Meent @ 2023-05-18 18:11 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Andrew Gierth <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; Tom Lane <[email protected]>; Regina Obe <[email protected]>; [email protected]
On Fri, 21 Apr 2023 at 22:46, Jeff Davis <[email protected]> wrote:
>
> On Fri, 2023-04-21 at 19:00 +0100, Andrew Gierth wrote:
> > > > > >
> > Also, somewhere along the line someone broke initdb --no-locale,
> > which
> > should result in C locale being the default everywhere, but when I
> > just
> > tested it it picked 'en' for an ICU locale, which is not the right
> > thing.
>
> Fixed, thank you.
As I complain about in [0], since 5cd1a5af --no-locale has been broken
/ bahiving outside it's description: Instead of being equivalent to
`--locale=C` it now also overrides `--locale-provider=libc`, resulting
in undocumented behaviour.
Kind regards,
Matthias van de Meent
Neon, Inc.
[0] https://www.postgresql.org/message-id/CAEze2WiZFQyyb-DcKwayUmE4rY42Bo6kuK9nBjvqRHYxUYJ-DA%40mail.gma...
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-18 18:26 Jeff Davis <[email protected]>
parent: Jonathan S. Katz <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-05-18 18:26 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Thu, 2023-05-18 at 13:58 -0400, Jonathan S. Katz wrote:
> From my read of them, as an app developer I'd be very unlikely to
> use
> this. Maybe there is something with building out some collation rules
> vis-a-vis an extension, but I have trouble imagining the use-case. I
> may
> also not be the target audience for this feature.
That's a problem for the ICU rules feature. I understand some features
may be for domain experts only, but we at least need to call that out
so that ordinary developers don't get confused. And we should hear from
some of those domain experts that they actually want it and it solves a
real problem.
For the features that can be described with collation
settings/attributes right in the locale name, the use cases are more
plausible and we've supported them since v10, so it's good to document
them as best we can. It's hard to expose only the particular ICU
collation settings we understand best (e.g. the "ks" setting that
allows case insensitive collation), so it's inevitable that there will
be some settings that are more obscure and harder to document.
But in the case of ICU rules, they are newly-supported in 16, so there
should be a clear reason we're adding them. Otherwise we're just
setting up users for confusion or problems, and creating backwards-
compatibility headaches for ourselves (and the last thing we want is to
fret over backwards compatibility for a feature with no users).
Beyond that, there seems to be some danger: if the syntax for rules is
not perfectly compatible between ICU versions, the user might run into
big problems.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-19 22:57 Tom Lane <[email protected]>
parent: Jeff Davis <[email protected]>
2 siblings, 0 replies; 45+ messages in thread
From: Tom Lane @ 2023-05-19 22:57 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; Regina Obe <[email protected]>; Peter Eisentraut <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
Jeff Davis <[email protected]> writes:
> Committed, thank you.
This commit has given the PDF docs build some indigestion:
Making portrait pages on A4 paper (210mmx297mm)
/home/postgres/bin/fop -fo postgres-A4.fo -pdf postgres-A4.pdf
[WARN] FOUserAgent - Font "Symbol,normal,700" not found. Substituting with "Symbol,normal,400".
[WARN] FOUserAgent - Font "ZapfDingbats,normal,700" not found. Substituting with "ZapfDingbats,normal,400".
[WARN] FOUserAgent - Hyphenation pattern not found. URI: en.
[WARN] FOUserAgent - The contents of fo:block line 1 exceed the available area in the inline-progression direction by 3531 millipoints. (See position 55117:2388)
[WARN] FOUserAgent - The contents of fo:block line 1 exceed the available area in the inline-progression direction by 1871 millipoints. (See position 55117:12998)
[WARN] FOUserAgent - Glyph "?" (0x323, dotbelowcmb) not available in font "Courier".
[WARN] FOUserAgent - Glyph "?" (0x302, circumflexcmb) not available in font "Courier".
[WARN] FOUserAgent - The contents of fo:block line 12 exceed the available area in the inline-progression direction by 20182 millipoints. (See position 55172:188)
[WARN] FOUserAgent - The contents of fo:block line 10 exceed the available area in the inline-progression direction by 17682 millipoints. (See position 55172:188)
[WARN] FOUserAgent - Glyph "?" (0x142, lslash) not available in font "Times-Roman".
[WARN] PropertyMaker - span="inherit" on fo:block, but no explicit value found on the parent FO.
(The first three and last one warnings are things we've been living
with, but the ones between are new.)
The first two "exceed the available area" complaints are in the "ICU
Collation Levels" table. We can silence them by providing some column
width hints to make the "Description" column a tad wider than the rest,
as in the proposed patch attached. The other two, as well as the first
two glyph-not-available complaints, are caused by this bit:
Full normalization is important in some cases, such as when
multiple accents are applied to a single character. For instance,
<literal>'ệ'</literal> can be composed of code points
<literal>U&'\0065\0323\0302'</literal> or
<literal>U&'\0065\0302\0323'</literal>. With full normalization
on, these code point sequences are treated as equal; otherwise they
are unequal.
which renders just abysmally (see attached screen shot), and even in HTML
where it's rendering about as intended, it really is an unintelligible
example. Few people are going to understand that the circumflex and the
dot-below are separately applied accents. How about we drop the explicit
example and write something like
Full normalization allows code point sequences such as
characters with multiple accent marks applied in different
orders to be seen as equal.
?
(The last missing-glyph complaint is evidently from the release notes;
I'll bug Bruce about that separately.)
regards, tom lane
Attachments:
[text/x-diff] add-column-width-hints.patch (807B, ../../[email protected]/2-add-column-width-hints.patch)
download | inline diff:
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 9db14649aa..96a23bf530 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -1140,6 +1140,14 @@ SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true
<table id="icu-collation-levels">
<title>ICU Collation Levels</title>
<tgroup cols="8">
+ <colspec colname="col1" colwidth="1*"/>
+ <colspec colname="col2" colwidth="1.25*"/>
+ <colspec colname="col3" colwidth="1*"/>
+ <colspec colname="col4" colwidth="1*"/>
+ <colspec colname="col5" colwidth="1*"/>
+ <colspec colname="col6" colwidth="1*"/>
+ <colspec colname="col7" colwidth="1*"/>
+ <colspec colname="col8" colwidth="1*"/>
<thead>
<row>
<entry>Level</entry>
[image/png] ICU-documentation-excerpt.png (58.9K, ../../[email protected]/3-ICU-documentation-excerpt.png)
download | view image
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-22 12:27 Peter Eisentraut <[email protected]>
parent: Jeff Davis <[email protected]>
2 siblings, 1 reply; 45+ messages in thread
From: Peter Eisentraut @ 2023-05-22 12:27 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On 18.05.23 19:55, Jeff Davis wrote:
> On Wed, 2023-05-17 at 19:59 -0400, Jonathan S. Katz wrote:
>> I did a quicker read through this time. LGTM overall. I like what you
>> did with the explanations around sensitivity (now it makes sense).
>
> Committed, thank you.
>
> There are a few things I don't understand that would be good to
> document better:
>
> * Rules. I still don't quite understand the use case: are these for
> people inventing new languages? What is a plausible use case that isn't
> covered by the existing locales and collation settings? Do rules make
> sense for a database default collation? Are they for language experts
> only or might an ordinary developer benefit from using them?
The rules are for setting whatever sort order you like. Maybe you want
to sort + before - or whatever. It's like, if you don't like it, build
your own.
> * The collation types "phonebk", "emoji", etc.: are these variants of
> particular locales, or do they make sense in multiple locales? I don't
> know where they fit in or how to document them.
The k* settings are parametric settings, in that they transform the sort
key in some algorithmic way. The co settings are just everything else.
They are not parametric, they are just some other sort order that
someone spelled out explicitly.
> * I don't understand what "kc" means if "ks" is not set to "level1".
There is an example here:
https://peter.eisentraut.org/blog/2023/05/16/overview-of-icu-collation-settings#colcaselevel
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-22 12:34 Peter Eisentraut <[email protected]>
parent: Jeff Davis <[email protected]>
1 sibling, 1 reply; 45+ messages in thread
From: Peter Eisentraut @ 2023-05-22 12:34 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On 18.05.23 00:59, Jeff Davis wrote:
> On Tue, 2023-05-16 at 20:23 -0700, Jeff Davis wrote:
>> Other than that, and I took your suggestions almost verbatim. Patch
>> attached. Thank you!
>
> Attached new patch with a typo fix and a few other edits. I plan to
> commit soon.
Some small follow-up on this patch:
Please put blank lines between
</sect3>
<sect3 ...>
etc., matching existing style.
We usually don't capitalize the collation parameters like
CREATE COLLATION mycollation1 (PROVIDER = icu, LOCALE = 'ja-JP);
elsewhere in the documentation.
Table 24.2. ICU Collation Settings should probably be sorted by key, or
at least by something.
All tables should referenced in the text, like "Table x.y shows this and
that." (Note that a table could float to a different page in some
output formats, so just putting it into a section without some
introductory text isn't sound.)
Table 24.1. ICU Collation Levels shows punctuation as level 4, which is
only true in shifted mode, which isn't the default. The whole business
of treating variable collation elements is getting a bit lost in this
description. The kv option is described as "Classes of characters
ignored during comparison at level 3.", which is effectively true but
not the whole picture.
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-22 19:03 Jeff Davis <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-05-22 19:03 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Mon, 2023-05-22 at 14:27 +0200, Peter Eisentraut wrote:
> The rules are for setting whatever sort order you like. Maybe you
> want
> to sort + before - or whatever. It's like, if you don't like it,
> build
> your own.
A build-your-own feature is fine, but it's not completely zero cost.
There some risk that rules specified for ICU version X fail to load for
ICU version Y. If that happens to your database default collation, you
are in big trouble. The risk of failing to load a language tag in a
later version, especially one returned by uloc_toLanguageTag() in
strict mode, is much lower. We can reduce the risk by allowing rules
only for CREATE COLLATION (not CREATE DATABASE), and see what users do
with it first, and consider adding it to CREATE DATABASE later.
We can also try to explain in the docs that it's a build-it-yourself
kind of feature (use it if you see a purpose, otherwise ignore it),
though I'm not sure quite how we should word it.
And I'm skeptical that we don't have a single plausible end-to-end user
story. I just can't think of any reason someone would need something
like this, given how flexible the collation settings in the language
tags are. The best case I can think of is if someone is trying to make
an ICU collation that matches some non-ICU collation in another system,
which sounds hard; but perhaps it's reasonable to do in cases where it
just needs to work well-enough in some limited case.
Also, do we have an answer as to why specifying the rules as '' is not
the same as not specifying any rules[1]?
[1]
https://www.postgresql.org/message-id/[email protected]
> The co settings are just everything else.
> They are not parametric, they are just some other sort order that
> someone spelled out explicitly.
This sounds like another case where we can't really tell the user why
they would want to use a specific "co" setting; they should only use it
if they already know they want it. Is there some way we can word that
in the documentation so that people don't misuse them?
For instance, one of them is called "emoji". I'm sure a lot of
applications use emoji (or at least might encounter them), should they
always use co-emoji, or would some people who are using emoji not want
it? Can it be combined with "ks" or other "k*" settings?
What I'm trying to avoid is users seeing something in the documentation
and using it without it really being a good fit for their problem. Then
they see something unexpected, and need to rebuild all of their indexes
or something.
> > * I don't understand what "kc" means if "ks" is not set to
> > "level1".
>
> There is an example here:
> https://peter.eisentraut.org/blog/2023/05/16/overview-of-icu-collation-settings#colcaselevel
Interesting, thank you.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
* Re: Order changes in PG16 since ICU introduction
@ 2023-05-26 00:23 Jeff Davis <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 45+ messages in thread
From: Jeff Davis @ 2023-05-26 00:23 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; Jonathan S. Katz <[email protected]>; Robert Haas <[email protected]>; +Cc: Tom Lane <[email protected]>; Regina Obe <[email protected]>; Sandro Santilli <[email protected]>; [email protected]
On Mon, 2023-05-22 at 14:34 +0200, Peter Eisentraut wrote:
> Please put blank lines between
>
> </sect3>
> <sect3 ...>
>
> etc., matching existing style.
>
> We usually don't capitalize the collation parameters like
>
> CREATE COLLATION mycollation1 (PROVIDER = icu, LOCALE = 'ja-JP);
>
> elsewhere in the documentation.
>
> Table 24.2. ICU Collation Settings should probably be sorted by key,
> or
> at least by something.
>
> All tables should referenced in the text, like "Table x.y shows this
> and
> that." (Note that a table could float to a different page in some
> output formats, so just putting it into a section without some
> introductory text isn't sound.)
Thank you, done.
> Table 24.1. ICU Collation Levels shows punctuation as level 4, which
> is
> only true in shifted mode, which isn't the default. The whole
> business
> of treating variable collation elements is getting a bit lost in this
> description. The kv option is described as "Classes of characters
> ignored during comparison at level 3.", which is effectively true but
> not the whole picture.
I organized the documentation around practical examples and available
options, and less around the conceptual model. I think that's a good
start, but you're right that it over-simplifies in a few areas.
Discussing the model would work better along with an explanation of ICU
rules, where you can make better use of those concepts. I feel like
there are some interesting things that can be done with rules, but I
haven't had a chance to really dig in yet.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 45+ messages in thread
end of thread, other threads:[~2023-05-26 00:23 UTC | newest]
Thread overview: 45+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-15 23:43 [PATCH v2 3/8] wip: meson: add install-{docs,doc-html,doc-man} targets Andres Freund <[email protected]>
2023-04-21 17:09 Re: Order changes in PG16 since ICU introduction Sandro Santilli <[email protected]>
2023-04-21 17:14 ` Re: Order changes in PG16 since ICU introduction Peter Eisentraut <[email protected]>
2023-04-21 17:28 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-04-21 17:37 ` RE: Order changes in PG16 since ICU introduction Regina Obe <[email protected]>
2023-04-21 17:46 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-04-21 17:56 ` RE: Order changes in PG16 since ICU introduction Regina Obe <[email protected]>
2023-04-21 17:59 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-04-21 18:13 ` RE: Order changes in PG16 since ICU introduction Regina Obe <[email protected]>
2023-04-21 18:23 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-04-21 19:25 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-21 20:00 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-04-21 20:50 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-25 04:31 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-21 20:33 ` Re: Order changes in PG16 since ICU introduction Robert Haas <[email protected]>
2023-04-21 21:56 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-21 22:39 ` RE: Order changes in PG16 since ICU introduction Regina Obe <[email protected]>
2023-04-22 00:12 ` Re: Order changes in PG16 since ICU introduction Robert Haas <[email protected]>
2023-05-06 00:25 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-16 19:35 ` Re: Order changes in PG16 since ICU introduction Jonathan S. Katz <[email protected]>
2023-05-17 03:23 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-17 22:59 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-17 23:59 ` Re: Order changes in PG16 since ICU introduction Jonathan S. Katz <[email protected]>
2023-05-18 17:55 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-18 17:58 ` Re: Order changes in PG16 since ICU introduction Jonathan S. Katz <[email protected]>
2023-05-18 18:26 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-19 22:57 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-05-22 12:27 ` Re: Order changes in PG16 since ICU introduction Peter Eisentraut <[email protected]>
2023-05-22 19:03 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-22 12:34 ` Re: Order changes in PG16 since ICU introduction Peter Eisentraut <[email protected]>
2023-05-26 00:23 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-21 23:00 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-24 15:26 ` Re: Order changes in PG16 since ICU introduction Peter Eisentraut <[email protected]>
2023-04-21 20:13 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-21 18:00 ` Re: Order changes in PG16 since ICU introduction Andrew Gierth <[email protected]>
2023-04-21 18:06 ` Re: Order changes in PG16 since ICU introduction Tom Lane <[email protected]>
2023-04-21 19:14 ` Re: Order changes in PG16 since ICU introduction Andrew Gierth <[email protected]>
2023-04-21 20:45 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-05-18 18:11 ` Re: Order changes in PG16 since ICU introduction Matthias van de Meent <[email protected]>
2023-04-21 19:14 ` Re: Order changes in PG16 since ICU introduction Sandro Santilli <[email protected]>
2023-04-21 20:03 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-24 15:10 ` Re: Order changes in PG16 since ICU introduction Peter Eisentraut <[email protected]>
2023-05-09 08:25 ` Re: Order changes in PG16 since ICU introduction Alvaro Herrera <[email protected]>
2023-04-21 17:27 ` Re: Order changes in PG16 since ICU introduction Jeff Davis <[email protected]>
2023-04-21 19:17 ` Re: Order changes in PG16 since ICU introduction Sandro Santilli <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox