AFAIK, this is fixed in PostgreSQL 10 (ICU), so no reason to panic if you are on v10+. However, it nicely demonstrates problems with strcoll :-)
Introduction to ICU in PostgreSQL: https://blog.2ndquadrant.com/icu-support-postgresql-10/
The dangers of streaming across versions of glibc: A cautionary tale (2014)
11–20 of 82 posts
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#12This isn't a glibc issue; really it's a story that "character collations are not stable over time" (mentioned downthread in the article in a message quoting Unicode Technical Standard #10).
Which is a cautionary tale the solution for which is to use icu, the solution adopted by Postgres.
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#13This, BTW, is a great example why many filesystems are case sensitive -- ignoring case requires collation support, and this can change all the time. Treating filenames as opaque byte strings, on the other hand, makes the filesystems, databases and so on Always Work.
Of course, PostgreSQL is one the few programs which actually cares about collation and case-insensitivity, so it has to work the hard way.
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#14AFAIK, this is fixed in PostgreSQL 10 (ICU), so no reason to panic if you are on v10+. However, it nicely demonstrates problems with strcoll :-)
This was not fixed in PostgreSQL 10.
ICU collations are not used by default, you have to explicitly enable them, and you can't set them as the database default. And even if you use ICU collations, I'm not sure if indices actually detect if the ICU versions don't match.
Maybe they improved some of the issues in PostgreSQL 11 (haven't been following the ICU issue in detail), but I don't think this issue is fixed.
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#15AFAIK, this is fixed in PostgreSQL 10 (ICU), so no reason to panic if you are on v10+. However, it nicely demonstrates problems with strcoll :-)
Correct. Using ICU collations avoids this problem. Also, with libc collations you have to rebuild indexes after a glibc upgrade: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade....
Even if you use ICU, you need to make sure you link with the same version of ICU!
Edit: Why the downvote? New versions of Unicode come out every year, and ICU collations are explicitely versioned. If you use ICU 51.1 and ICU 52.1 you are going to have the same kind of issues
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#16This isn't a glibc issue; really it's a story that "character collations are not stable over time" (mentioned downthread in the article in a message quoting Unicode Technical Standard #10). Which is a cautionary tale the solution for which is to use icu, the solution adopted by Postgres.
> the solution for which is to use icu
We ran headfirst into this issue at my company and we've actually been recommending the opposite (use the "C" locale on the database, treat collation as a render level concern).
I have a whole write up explaining the technical motivations behind that recommendation: https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af...
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#17About 20 years ago, I implemented a hash table that used binary trees for buckets. It was supposed to support Unicode strings as keys, and I used the Windows comparison functions to figure out how to compare the keys, in order to handle case insensitive lookup IIRC.
I tested the table with randomly generated strings, and was puzzled to discover expected lookups would fail with some table constructions. I narrowed it down to inconsistencies in collation.
It turns out that the collation order implemented by Windows was not transitive. You could have three code points, a, b and c, where a > b and b > c and c > a. Sort order is well defined within blocks, but there isn't necessarily a meaningful sort order across blocks; how should your Cyrillic letter compare with your Greek letter vs your Armenian letter?
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#18What does Postgres Aurora use?
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#19About 20 years ago, I implemented a hash table that used binary trees for buckets. It was supposed to support Unicode strings as keys, and I used the Windows comparison functions to figure out how to compare the keys, in order to handle case insensitive lookup IIRC. I tested the table with randomly generated strings, and was puzzled to discover expected lookups would fail with some table constructions. I narrowed it…
> I used the Windows comparison functions to figure out how to compare the keys, in order to handle case insensitive lookup IIRC.
Wouldn't you need to do case normalization before hashing, to make that work?
Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)
#20About 20 years ago, I implemented a hash table that used binary trees for buckets. It was supposed to support Unicode strings as keys, and I used the Windows comparison functions to figure out how to compare the keys, in order to handle case insensitive lookup IIRC. I tested the table with randomly generated strings, and was puzzled to discover expected lookups would fail with some table constructions. I narrowed it…
> I used the Windows comparison functions to figure out how to compare the keys, in order to handle case insensitive lookup IIRC. Wouldn't you need to do case normalization before hashing, to make that work?
That's one way to implement case-insensitive lookup. OTOH, for a programming language symbol table, maybe you want to look up with case sensitivity first, then a second time with case insensitivity so you can emit a warning about the discrepancy (for a case-insensitive language) or an error with suggested spelling correction (for a case-sensitive language) and you don't want to maintain two separate hash tables for every scope.