postgresql pg_import_system_collations does not import OS-added locales

user_14702057 0
Viewed 268

First post. Playing around with postgresql 11.9 on debian buster light, I wanted to add a new locale to postgreqls' list. Doing select * from pg_collation; in psql shows that the locale that I want is not in the list.

To add a new locale I first added a new locale to my debian by un-commenting the line sv_SE.UTF-8 UTF-8 in /etc/locale.gen. Then I run /usr/sbin/locale-gen which was successful. After this both locale -a and localectl list-locales shows my newly added locale.

Now I would like to update the list of locales in postgresql that came from c-lib. This is done using pg_import_system_collations documented here https://www.postgresql.org/docs/11/functions-admin.html

Running psql as user postgres and executing select pg_import_system_collations('pg_catalog'); yields

 pg_import_system_collations
-----------------------------
                           0
(1 row)

So the new locale is not imported. I don't know why. Any ideas?

Edit: converted "update" with answer to separate answer

postgresql
1 Answers

All of the above was done without restarting any daemon, service, the host or the like.

I have now made systemctl restart postgresql. Now the pg_import_system_collations works as expected. My conclusion here is that the documentation for pg_import_system_collations is a bit off. It obviously caches system locales on startup and they can be imported using pg_import_system_collations if that is desired.

It would be interesting to see if other postgresql-users see the same behaviour and if it varies with OS.

user_147020570
Related