Collation is wrong in alpine version

[etienned]

If you run this test:

CREATE TEMPORARY TABLE table1 (column1 TEXT); 

INSERT INTO table1 VALUES
('Barn'),
('beef'),
('bémol'),
('Bœuf'),
('boulette'),
('Bubble');

SELECT * FROM table1 ORDER BY column1 COLLATE "en_US"; --Gives the expected order
SELECT * FROM table1 ORDER BY column1 COLLATE "C"; --Gives "wrong" order

First SELECT should gives this order:

• Barn
• beef
• bémol
• Bœuf
• boulette
• Bubble

But instead it gives (same result as COLLATE "C"):

• Barn
• Bubble
• Bœuf
• beef
• boulette
• bémol

Works fine in Jessie version.

It's probably because current musl library version (1.1.16) does not support LC_COLLATE, so despite settings LANG variable, sorting data will be bytewise (C). LC_COLLATE should be added to musl version 1.1.17.

[dannylui]

👍

[approxit]

Any updates on this?

This case popped out in timescale/timescaledb#448.

@tianon

[tianon]

This sounds similar to #273 / #276 (which both still have an unknown root cause). 😞

The next step for determining the root of the problem would be to install PostgreSQL from Alpine's repos and see if that also exhibits the problem (because if it does, that points more strongly at a musl issue rather than a PostgreSQL issue or even a Docker image specific issue).

[adamhooper]

Collation may be the root cause of #273 and #276, right? Different collation would make different index ordering.

That poses a disaster-scale problem: once musl supports LC_COLLATE, all Postgres VARCHAR indexes will break. One workaround: choose "C" collation.

[jperelli]

Maybe relevant: official musl vs glibc Character sets and locale https://wiki.musl-libc.org/functional-differences-from-glibc.html#Character-sets-and-locale

[ManiacDC]

We just ran into this also, anyone running the alpine image got the default "C" collation.

[raijinsetsu]

My entire development team spent the last 6 hours debugging why our unit tests were failing on some local installations and not others. It came down to: the unit tests were written against the Alpine image and expected results is a specific order and the non-Alpine image returned results in a different order.

I ask that the Docker hub page be updated to reflect this as I doubt that we are the only development team that has run into this. A note that "Alpine" will use "C" collation is almost all cases (at least from our testing) would be sufficient.

[tianon]

Great idea, I've filed docker-library/docs#1778 👍

[jirutka]

If you build PostgreSQL with ICU, then you can use ICU Collations instead. Since Alpine v3.16, install icu-data-full to get support for all locales.

[lukaromih]

Here's how you can get it working with the new postgres:15-alpine image.