5 ms·
This 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 Uni
by gumby 8y ago
This 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.
- rraval 8y ago> 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/ef4e4bdc63e68fe3e83c9f98f56af7a4 https://gist.github.com/rraval/ef4e4bdc63e68fe3e83c9f98f56af...
- mnw21cam 8y agoThis has the advantage that your database operations run a heck of a lot faster, but has the potential disadvantage that primary key uniqueness may not be maintained, if you think that alternate ways of writing the same characters in unicode matters for that.
- greglindahl 8y agoNormalization is a separate issue, you can normalize and then use the C collation order.
- BeeOnRope 8y agoSure but either you are talking about a fixed normalization algorithm which is not locale aware, in which case it doesn't solve the locale-specific unique key issue, or it is locale-aware and hence suffers the same problem with time-varying behavior.
- jrochkind1 8y agoYou are using string/text values as a pk and trying to sort on em? I'd say this is another reason not to do that.
- BeeOnRope 8y agoWell I am not doing any of the things in the comment chain leading up to this, but probably the mention of primary key was a red herring. The GP's [1] point was that some index constraint (they mentioned PK uniqueness, but it could really be any constraint) might not be correctly maintained if the DB was not aware of the correct collation order. So from the point of view of the renderer, which is locale aware and uses locale-based collation, the DB is violating the constraints. --- [1] The GP relative to my reply
- rraval 8y agoAs you say, I'd question the technical motivation for enforcing uniqueness on unicode data in the first place (and as a primary key on top of that???) However, if someone really wanted to accomplish this, they could probably use PostgreSQL's functional indices and unicode normalization to do it.
- toast0 8y agoThe motivations are strong here. You want your database to be as sane as possible. But this will make some features really hard, if you need them. Pagination based on sorted values subject to collation wouldn't be queryable, you would need to either get all the data and sort it, or query for a key and the sorted column, sort and then query for details on the displayed items. Selecting a range would also be potentially difficult.
- cryptonector 8y agoUsing the C locale certainly helps, but do watch out: you still need to normalize. That means you still need something like ICU.
- BeeOnRope 8y agoIt's easy to say "treat collation as a render level concern", but this doesn't really work efficiently when the rendering component wants to query the database using this index, does it? That is, to do anything you want to do a locale sensitive way, such as querying the database for a given case insensitive string, or pagination, you'll need to have the DB index be locale/collation aware or else return every possible value and the renderer sort it out. As an example, how you would you repeatedly return a range of results based on a string in a locale-aware way, e.g., to display paged results, if you defer the work to the renderer? The only general solution I'm aware of which lets you "bypass" the DB collation is to use a locale-aware collation library like ICU to generate a binary sort key, which can be compared using plain binary comparison and storing those in the DB. This still means the overall index is locale aware, but the DB doesn't need to be aware of any collation rules: only the code that generates queries and handles the results needs to do the sort key transformation. It means that you have a single library that does all the conversion, which you can probably control more easily, rather than delegating this to the database, where you might need to support several vendors or at least various versions (and problems can arise even within a single DB version as this postgres issue shows).
- nine_k 8y agoIf you build a plain index over Unicode strings, chances are you're doing it wrong. Normal DB indexes are mostly for numbers and (short) ASCII strings. (Something like canonicalized UTF-8 is an edge case.) For strings that have encodings and locales, you likely need a full-text index, provided by your DB or by something like Solr / ElasticSearch. It addresses the oddities of human-oriented texts better.
- cbsmith 8y agoI'm sorry but no. It's a pretty common use case (so common that it is usually taught in an introduction to databases) that you might want an index that allows you to efficiently search by non-ASCII strings like say, a person's last name.
- 8y ago
- Someone 8y agoICU isn’t the solution. If a bug in its collation routines is found, it likely will be fixed. You don’t want that if retrieving your data requires perfect stability of string comparisons. The only real solution is to have your own collation routines, test the hell out of them for every release (you don’t want a compiler bug or the fixing of a compiler bug to introduce a subtle change in your collation code. The truly paranoid also worry about bug fixes in hardware), and to never stop supporting a collation, once you have ever released a product using it. That’s what many databases do and why, for example, SQL Server has two binary collations. https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017#Unicode_Defn https://docs.microsoft.com/en-us/sql/relational-databases/co...: ”There are two types of binary collations in SQL Server; the older BIN collations and the newer BIN2 collations. In a BIN2 collation all characters are sorted according to their code points. In a BIN collation only the first character is sorted according to the code point, and remaining characters are sorted according to their byte values. (Because the Intel platform is a little endian architecture, Unicode code characters are always stored byte-swapped.)”)
- jjnoakes 8y agoI imagine the database would use versioned collation, so a fixed bug would have to be opted-into by whomever owns the tables, no?
- j16sdiz 8y agoIBM use ICU for DB2 collation. It have versioned collation that do bug-to-bug backward compatibly.
- mjevans 8y agoOr they could just drag Windows in to the world of sanity and store everything as UTF-8 (at last) instead of the 16 variant... Then endien-ness and a bunch of other bugs no longer matter.
- jrochkind1 8y agoyou'd of course need to make sure everything in the system is using the same version of ICU/unicode.