Re: pgsql: Add function to import operating system collations

On 18-01-2017 12:43, Tom Lane wrote:
> I wrote:
>> running bootstrap script ... ok
>> performing post-bootstrap initialization ... 2017-01-18 09:49:45.019 EST [25919] FATAL:  collation "aa_ER@saaho" for encoding "UTF8" already exists
>> 2017-01-18 09:49:45.019 EST [25919] STATEMENT:  SELECT pg_import_system_collations(if_not_exists => false, schema => 'pg_catalog');
> 
> As a stopgap so I could get some work done, I did
> 
> -   PG_CMD_PUTS("SELECT pg_import_system_collations(if_not_exists => false, schema => 'pg_catalog');\n\n");
> +   PG_CMD_PUTS("SELECT pg_import_system_collations(if_not_exists => true, schema => 'pg_catalog');\n\n");
> 
> and what I now see in pg_collation is
> 
> regression=# select * from pg_collation where collname like 'aa_ER%';
>      collname     | collnamespace | collowner | collencoding |   collcollate    |    collctype     
> ------------------+---------------+-----------+--------------+------------------+------------------
>  aa_ER            |            11 |        10 |            6 | aa_ER            | aa_ER
>  aa_ER.utf8       |            11 |        10 |            6 | aa_ER.utf8       | aa_ER.utf8
>  aa_ER.utf8@saaho |            11 |        10 |            6 | aa_ER.utf8@saaho | aa_ER.utf8@saaho
>  aa_ER@saaho      |            11 |        10 |            6 | aa_ER.utf8@saaho | aa_ER.utf8@saaho
> (4 rows)
> 
> Maybe an appropriate fix would be to ignore collations whose names aren't
> equal to what we get for collcollate/collctype.  Presumably the latter
> are getting canonicalized somehow.
> 
collname 'en_US' seems to be more popular than 'en_US.utf8' (it is
shorter). We can't ignore locales without .utf8 or @something because it
would break queries with COLLATE clause.

Ignore collations at initdb time seems to fix the error. However, do
collations remain the same as 9.6?


-- 
   Euler Taveira                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento