LOCALE environment variable doesn't work

[vojtechmasa]

Thank you for offering your container! I tried to set locale to cs_CZ.UTF-8 - in docker-compose.yml - by setting LANG=cs_CZ.UTF-8, but it seems it doesn't have any effect - records are still sorted as with EN locale. Also, I don't see using LANG variable in the whole repo.

[onjin]

Hi. Thank you for feedback.

LANG variable is set in Dockerfile:

• https://github.com/onjin/docker-alpine-postgres/blob/master/9.6/Dockerfile#L10

and is used during cluster initialization. So if you have created container with mounted volume and default LANG=en_US.utf-8, changing LANG will have no effect to already created cluster after restart.

In that case you have to create new container with LANG=cs_CS.utf-8 and migrate data from previous container by dump/restore.

I checked LANG with this docker-compose.yml:

postgres-cs:
    image: onjin/alpine-postgres:9.6
    environment:
        LANG: 'cs_CS.UTF-8'

and after starting this container I've got databases with cs_CS.utf-8 as collate and ctype:

$ docker-compose up -d

$ docker-compose exec --user postgres postgres-cs psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 |
 template0 | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

 $ docker-compose exec --user postgres postgres-cs psql
psql (9.6.0)
Type "help" for help.

postgres=# create database sorting;
CREATE DATABASE
postgres=# \q

 $ docker-compose exec --user postgres postgres-cs psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 |
 sorting   | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 |
 template0 | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | cs_CS.UTF-8 | cs_CS.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

if this is not resolving your problem, please attach here you docker-compose.yml file and example data which is not sorting properly.

[vojtechmasa]

Actually, I did quite the same as you wrote. This is the relevant part from my docker-compose.yml file:

sql:
  container_name: backend-1-sql
  image: onjin/alpine-postgres:9.6
ports:
- "45432:5432"
environment:
  POSTGRES_PASSWORD: secret
  LANG: cs_CZ.UTF-8

Information about the db:

/ $ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 |
 template0 | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | cs_CZ.UTF-8 | cs_CZ.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Then I created table profile with column lastname and inserted sample data. When I select I get

postgres=# select lastname from profile order by lastname;
    lastname
-----------------
 Ambrož
 Břečková
 Charvátová
 Chytilová
 Daňková
 Dembická
 Dobrovolná
 Dobrovolná
 Doležalová
 Vaněk
 Zatloukalová
 Zavřelová
 Řeháková
 Ševčíková
 Šimková
(15 rows)

which is not correct order. If I use cs_CS.UTF-8 as you did, I get similar output - Ch is in correct position, but words starting with letters with accent are not in correct order:

 Ambrož
 Břečková
 Daňková
 Dembická
 Dobrovolná
 Dobrovolná
 Doležalová
 Charvátová
 Chytilová
 Vaněk
 Zatloukalová
 Zavřelová
 Řeháková
 Ševčíková
 Šimková

If I use official postgres image (no alpine - in official postgres alpine image, locale doesn't work), i.e. postgres:9.6, with the following dockerfile (according to documentation at https://hub.docker.com/r/library/postgres/):

FROM postgres:9.6
RUN localedef -i cs_CZ -c -f UTF-8 -A /usr/share/locale/locale.alias cs_CZ.UTF-8
ENV LANG cs_CZ.utf8

the sorting is correct - like this:

 Ambrož
 Břečková
 Daňková
 Dembická
 Dobrovolná
 Dobrovolná
 Doležalová
 Charvátová
 Chytilová
 Řeháková
 Ševčíková
 Šimková
 Vaněk
 Zatloukalová
 Zavřelová

[onjin]

Thanks for details.

I thing i've found what is the problem, but unfortunately we can't do anything about it.

Alpine linux is based on musl library instead of glibc. The newest musl version is 1.1.16 and LC_COLLATE support is planned for next 1.1.7 and 1.2.0 versions:

• http://wiki.musl-libc.org/wiki/Roadmap
• https://www.musl-libc.org/download.html
• http://www.openwall.com/lists/musl/2014/08/01/1

So we need to wait for new musl and then for alpine version based on newer musl library.

[onjin]

I've added notice about this issue to readme file:

• https://github.com/onjin/docker-alpine-postgres#caveats

I'll let this issue opened to remember following the musl library development.