Re: Allow tailoring of ICU locales with custom rules - Mailing list pgsql-hackers

On Mon, 2023-01-16 at 12:18 +0100, Peter Eisentraut wrote:
> Updated patch attached.

I like that patch.  It applies and passes regression tests.

I played with it:

  CREATE COLLATION german_phone (LOCALE = 'de-AT', PROVIDER = icu, RULES = '&oe < ö');

  SELECT * FROM (VALUES ('od'), ('oe'), ('of'), ('p'), ('ö')) AS q(c)
  ORDER BY c COLLATE german_phone;

   c
  ════
   od
   oe
   ö
   of
   p
  (5 rows)

Cool so far.  Now I created a database with that locale:

  CREATE DATABASE teutsch LOCALE_PROVIDER icu ICU_LOCALE german_phone
     LOCALE "de_AT.utf8" TEMPLATE template0;

Now the rules are not in "pg_database":

  SELECT datcollate, daticulocale, daticurules FROM pg_database WHERE datname = 'teutsch';

   datcollate │ daticulocale │ daticurules
  ════════════╪══════════════╪═════════════
   de_AT.utf8 │ german_phone │ ∅
  (1 row)

I connect to the database and try:

  SELECT * FROM (VALUES ('od'), ('oe'), ('of'), ('p'), ('ö')) AS q(c)
  ORDER BY c COLLATE german_phone;

  ERROR:  collation "german_phone" for encoding "UTF8" does not exist
  LINE 1: ... ('oe'), ('of'), ('p'), ('ö')) AS q(c) ORDER BY c COLLATE ge...
                                                               ^

Indeed, the collation isn't there...

I guess that it is not the fault of this patch that the collation isn't there,
but I think it is surprising.  What good is a database collation that does not
exist in the database?

What might be the fault of this patch, however, is that "daticurules" is not
set in "pg_database".  Looking at the code, that column seems to be copied
from the template database, but cannot be overridden.

Perhaps this only needs more documentation, but I am confused.

Yours,
Laurenz Albe