Subject: Add standard collation UNICODE

From: Peter Eisentraut <[email-masked]>
Date: Wed, 1 Mar 2023 11:09:52 +0100

The SQL standard defines several standard collations.  Most of them are 
only of legacy interest (IMO), but two are currently relevant: UNICODE 
and UCS_BASIC.  UNICODE sorts by the default Unicode collation algorithm 
specifications and UCS_BASIC sorts by codepoint.

When collation support was added to PostgreSQL, we added UCS_BASIC, 
since that could easily be mapped to the C locale.  But there was no 
straightforward way to provide the UNICODE collation.  (Recall that 
collation support came several releases before ICU support.)

With ICU support, we can provide the UNICODE collation, since it's just 
the root locale.  I suppose one hesitation was that ICU was not a 
standard feature, so this would create variations in the default catalog 
contents, or something like that.  But I think now that we are drifting 
to make ICU more prominent, we can just add that anyway.  I think being 
able to say

     COLLATE UNICODE

instead of

     COLLATE "und-x-icu"

or whatever it is, is pretty useful.

So, attached is a small patch to add this.

From 98fce30e4997253e5b010b6ac72d66255bc77bf6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Wed, 1 Mar 2023 10:38:19 +0100
Subject: [PATCH] Add standard collation UNICODE

---
 doc/src/sgml/charset.sgml | 30 +++++++++++++++++++++++++++---
 src/bin/initdb/initdb.c   | 10 +++++++---
 2 files changed, 34 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 3032392b80..13ec238a81 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -659,9 +659,33 @@ <title>Standard Collations</title>
    </para>
 
    <para>
-    Additionally, the SQL standard collation name <literal>ucs_basic</literal>
-    is available for encoding <literal>UTF8</literal>.  It is equivalent
-    to <literal>C</literal> and sorts by Unicode code point.
+    Additionally, two SQL standard collation names are available for encoding
+    <literal>UTF8</literal>:
+
+    <variablelist>
+     <varlistentry>
+      <term><literal>unicode</literal></term>
+      <listitem>
+       <para>
+        This collation sorts using the Unicode Collation Algorithm with the
+        Default Unicode Collation Element Table.  (This is the same behavior
+        as the ICU root locale; see <xref
+        linkend="collation-managing-predefined-icu-und-x-icu"/>.)  This
+        collation is only available when ICU support is configured.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><literal>ucs_basic</literal></term>
+      <listitem>
+       <para>
+        This collation sorts by Unicode code point.  (This is the same
+        behavior as the libc locale specification <literal>C</literal>.)
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
    </para>
   </sect3>
 
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 7a58c33ace..525bec4b44 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1482,10 +1482,14 @@ static void
 setup_collation(FILE *cmdfd)
 {
 	/*
-	 * Add an SQL-standard name.  We don't want to pin this, so it doesn't go
-	 * in pg_collation.h.  But add it before reading system collations, so
-	 * that it wins if libc defines a locale named ucs_basic.
+	 * Add SQL-standard names.  We don't want to pin these, so they don't go
+	 * in pg_collation.h.  But add them before reading system collations, so
+	 * that they win if libc defines a locale with the same name.
 	 */
+	PG_CMD_PRINTF("INSERT INTO pg_collation (oid, collname, collnamespace, collowner, collprovider, collisdeterministic, collencoding, colliculocale)"
+				  "VALUES (pg_nextoid('pg_catalog.pg_collation', 'oid', 'pg_catalog.pg_collation_oid_index'), 'unicode', 'pg_catalog'::regnamespace, %u, '%c', true, %d, 'und');\n\n",
+				  BOOTSTRAP_SUPERUSERID, COLLPROVIDER_ICU, PG_UTF8);
+
 	PG_CMD_PRINTF("INSERT INTO pg_collation (oid, collname, collnamespace, collowner, collprovider, collisdeterministic, collencoding, collcollate, collctype)"
 				  "VALUES (pg_nextoid('pg_catalog.pg_collation', 'oid', 'pg_catalog.pg_collation_oid_index'), 'ucs_basic', 'pg_catalog'::regnamespace, %u, '%c', true, %d, 'C', 'C');\n\n",
 				  BOOTSTRAP_SUPERUSERID, COLLPROVIDER_LIBC, PG_UTF8);
-- 
2.39.2

---

From: Vik Fearing <[email-masked]>
Date: Thu, 2 Mar 2023 01:05:34 +0100

> So, attached is a small patch to add this.

I don't feel competent to review the patch (simple as it is), but +1 on 
the principle.
-- 
Vik Fearing

---

From: "Daniel Verite" <[email-masked]>
Date: Thu, 27 Apr 2023 13:44:55 +0200

> So, attached is a small patch to add this.

This collation has an empty pg_collation.collversion column, instead
of being set to the same value as "und-x-icu" to track its version.

postgres=# select * from pg_collation  where collname='unicode' \gx
-[ RECORD 1 ]-------+--------
oid		    | 963
collname	    | unicode
collnamespace	    | 11
collowner	    | 10
collprovider	    | i
collisdeterministic | t
collencoding	    | -1
collcollate	    | 
collctype	    | 
colliculocale	    | und
collicurules	    | 
collversion	    | 

The original patch implements this as an INSERT in which it would be easy to
fix I guess, but in current HEAD it comes as an entry in
include/catalog/pg_collation.dat:

{ oid => '963',
  descr => 'sorts using the Unicode Collation Algorithm with default
settings',
  collname => 'unicode', collprovider => 'i', collencoding => '-1',
  colliculocale => 'und' },

Should it be converted back into an INSERT or better left
in this file and collversion being updated afterwards?

Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 8 May 2023 17:48:09 +0200

> in this file and collversion being updated afterwards?

How about we do it with an UPDATE command.  We already do this for 
pg_database in a similar way.  See attached patch.
From 91f2aff04f9bf137e4ac6e7df624dde770503bfd Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Mon, 8 May 2023 17:45:46 +0200
Subject: [PATCH] initdb: Set collversion for standard collation UNICODE

Discussion: https://www.postgresql.org/message-id/[email-masked]
---
 src/bin/initdb/initdb.c | 7 +++++++
 1 file changed, 7 insertions(+)

diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 2c208ead01..632f6c9c72 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1695,6 +1695,13 @@ setup_description(FILE *cmdfd)
 static void
 setup_collation(FILE *cmdfd)
 {
+	/*
+	 * Set the collation version for collations defined in pg_collation.dat,
+	 * except the ones where we know that the collation behavior will never
+	 * change.
+	 */
+	PG_CMD_PUTS("UPDATE pg_collation SET collversion = pg_collation_actual_version(oid) WHERE collname = 'unicode';\n\n");
+
 	/* Import all collations we can find in the operating system */
 	PG_CMD_PUTS("SELECT pg_import_system_collations('pg_catalog');\n\n");
 }
-- 
2.40.0

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 12 May 2023 10:04:50 +0200

> pg_database in a similar way.  See attached patch.

This has been committed.