Major changes in sorting method upgrade 18.04->18.04.5 due to base-files change

[vodopad27]

I had an Ubuntu 18.04.0 with Postgres 9.6.

Before Ubuntu upgrade following command result was:

[code]
vodka@ubuntu140:~$ ( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort
1-1
11
[/code]

But after upgrading Ubuntu 18.04 to latest release via apt upgrade (18.04.5) result of the command above totally changed:

[code]
vodka@ubuntu140:~$ ( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort
11
1-1
[/code]

Due to this our production Postgres database started work very slowly and we had a long downtime for REINDEX. Sorting method is very important for Postgres database: https://wiki.postgresql.org/wiki/Locale_data_changes

Please, read "Testing collation" part.

Is it normal behavior for Ubuntu? Why glibc totally changed within LTS release without 'major upgrade'? I did not expect this...

ProblemType: Bug
DistroRelease: Ubuntu 18.04
Package: libc-bin 2.27-3ubuntu1.4
ProcVersionSignature: Ubuntu 4.15.0-136.140-generic 4.15.18
Uname: Linux 4.15.0-136-generic x86_64
ApportVersion: 2.20.9-0ubuntu7.23
Architecture: amd64
Date: Thu Feb 25 13:13:03 2021
Dependencies:
 gcc-8-base 8.4.0-1ubuntu1~18.04
 libc6 2.27-3ubuntu1.4
 libgcc1 1:8.4.0-1ubuntu1~18.04
InstallationDate: Installed on 2021-02-25 (0 days ago)
InstallationMedia: Ubuntu-Server 18.04 LTS "Bionic Beaver" - Release amd64 (20180426)
SourcePackage: glibc
UpgradeStatus: No upgrade log present (probably fresh install)

[Balint Reczey (rbalint)]

Thank you for the bug report.
I' could not reproduce the old behaviour just by downgrading glibc, but will check it with an original 18.04.0 installation, then upgrading it.

I'm marking postgresql as affected, for better visibility.

[Balint Reczey (rbalint)]

Also you mentioned using Postgres 9.6, but I see only Postgres 10 in Bionic. You don't use a packaged version, right?

[vodopad27 (family-gan)]

Yes, we are using Postgres from official repo: https://wiki.postgresql.org/wiki/Apt

[Christian Ehrhardt  (paelzer)]

Hi,
to simplify the following text let us call order that lists 1-1 first "1-1" and the one that lists 11 first "11".

I checked the following:
bionic container (up to date) => 11
focal container (up to date) => 1-1
hirsute container (up to date) => 1-1
bionic vm (up to date) => 11
bionic vm (downgraded all) => 11
bionic vm (downgraded all + re-login) => 1-1

So it seems to be related to something that is applied while logging in (maybe some more ENV/Locale magic?).

bionic vm (glibc upgraded again) => 1-1
bionic vm (glibc upgraded again + re-login) => 1-1

So it was not glibc (that included libc-bin libc6 locales multiarch-support).
So it was none of these ... ?!?
Let me check if I can

bionic vm (all-upgraded) => 1-1
bionic vm (all-upgraded + re-login) => 11

Ok so I know the following for now:
1. some upgrade breaks/changes this
2. it is not glibc
3. it needs a re-login to have effect

I did the following experiment.
All downgraded, one session kept up, one session re-logged.
That gave me both behaviors with the same software.

Comparing the ENV and EXPORT of those two I've found:

$ diff 11.env 1-1.env | grep LC
< LC_MEASUREMENT=C.UTF-8
> LC_MEASUREMENT=de_DE.UTF-8
< LC_PAPER=C.UTF-8
< LC_MONETARY=C.UTF-8
> LC_PAPER=de_DE.UTF-8
> LC_MONETARY=de_DE.UTF-8
< LC_NAME=C.UTF-8
> LC_NAME=de_DE.UTF-8
< LC_ADDRESS=C.UTF-8
< LC_NUMERIC=C.UTF-8
> LC_ADDRESS=de_DE.UTF-8
> LC_NUMERIC=de_DE.UTF-8
< LC_TELEPHONE=C.UTF-8
> LC_TELEPHONE=de_DE.UTF-8
< LC_IDENTIFICATION=C.UTF-8
> LC_IDENTIFICATION=de_DE.UTF-8
< LC_TIME=C.UTF-8
> LC_TIME=de_DE.UTF-8

Seeing that one of them carried my local LC info also indicates that the problem might only affect "some" systems depending on their local language settings.

In my case it came down to
LC_NUMERIC=de_DE.UTF-8
vs
LC_NUMERIC=C.UTF-8

That made it behave constantly the same.
No a question might be which upgrade changed that ... ?

[Christian Ehrhardt  (paelzer)]

FYI - This happens only to a login shell

$ ssh ubuntu@192.168.122.177 "env | grep LC_NUM";
LC_NUMERIC=de_DE.UTF-8
ssh ubuntu@192.168.122.177 -t 'bash -l -c "env | grep LC_NUM"'
LC_NUMERIC=C.UTF-8

And while downgrading I saw the suspicious
/etc/profile.d/01-locale-fix.sh: line 2: /usr/bin/locale-check: No such file or directory

$ cat /etc/profile.d/01-locale-fix.sh
# Make sure the locale variables are set to valid values.
eval $(/usr/bin/locale-check C.UTF-8)

And indeed on "release" Bionic that binary used there isn't present.

I checked where it came from:
$ dpkg -S /usr/bin/locale-check
base-files: /usr/bin/locale-check

So it seems this check was always meant to work, but on bionic-release calls a file that isn't present and therefore is not in effect to "fixup" the locales on a login shell.

Now that I know that I was able to swicth between old/new behavior by switching "just" base files 10.1ubuntu2 <-> 10.1ubuntu2.10.

That change was introduced back in 2018 via bug 1134036 as far as I found.

I'm not sure what to do, this is "longer in the field with the new behavior" and not new at all.
So changing it either way might regress people.
But in any case we can re-target this bug against base-files for re-consideration now that we know all that.

[Christian Ehrhardt  (paelzer)]

FYI: Until resolved, hard setting the "other" LC_* values will get you either behavior as you need it.

[Balint Reczey (rbalint)]

@paelzer Thank you for the triaging and the detailed explanation. Since this change slipped in too long ago I don't think it should be reverted.
I think Postresql could check if there was a change in sorting compared to the which the indices were created with, but this installation does not uses Postgresql from the archive so that would just be an unrelated improvement. Maybe Postgres already does that.