9 ms·
The dangers of streaming across versions of glibc: A cautionary tale (2014)
- gumby 8y agoThis 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.
- hgq 8y ago2014
- nezirus 8y agoAFAIK, this is fixed in PostgreSQL 10 (ICU), so no reason to panic if you are on v10+. However, it nicely demonstrates problems with strcoll :-)
- emddudley 8y agoCorrect. 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.html https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade....
- jakobegger 8y agoEven 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
- anarazel 8y agoICU collations in PG are versioned, and ICU has support for accessing the different collation versions. So you might be stuck on an older collation version without explicit action, but it'll not yield wrong results.
- jakobegger 8y agoReally? PostgreSQL can use an index created with an older version of ICU? I didn't know that. Edit: The docs say indices need to be rebuilt when ICU version changes: https://www.postgresql.org/docs/11/sql-altercollation.html https://www.postgresql.org/docs/11/sql-altercollation.html ICU offers a way to detect the collation version has changed, but I thought there was no way to access older collation versions.
- aepiepaey 8y agoIntroduction to ICU in PostgreSQL: https://blog.2ndquadrant.com/icu-support-postgresql-10/ https://blog.2ndquadrant.com/icu-support-postgresql-10/
- bmurphy1976 8y agoThe post of from 2014 FYI. No idea if this is still an issue.
- daamien 8y agoThis is still an issue : https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade....
- JadeNB 8y agonezirus (https://news.ycombinator.com/item?id=18855711 https://news.ycombinator.com/item?id=18855711) says it isn't. (I don't know how this squares with daamien's (https://news.ycombinator.com/item?id=18855837 https://news.ycombinator.com/item?id=18855837) contrary reply.)
- nezirus 8y agoArch package is configured with ICU support, but I think you need to use ICU collations explicitly: https://postgresql.verite.pro/blog/2018/07/25/icu-extension.html https://postgresql.verite.pro/blog/2018/07/25/icu-extension.... SELECT * FROM pg_catalog.pg_collation; As usual with collations, you must be careful and know what you are doing (old collations are also still available, for backwards compatibility)
- tssva 8y agoPostgresql 10+ has added icu as a collation provider. If you select icu as your provider this is not an issue. If you select libc as your provider than this is still an issue you need to account for.
- deleted 8y ago[deleted]
- theamk 8y agoThis, 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.
- leeter 8y agoIt has always amused me that NTFS and NT Kernel are both case sensitive when it comes to filenames. But WIN32 emulates the case insensitive legacy behavior because people won't fix, or can't fix legacy code. That said applications can properly opt into POSIX semantics.
- voltagex_ 8y agoAre you talking about WSL or some other way that applications can use POSIX on Windows?
- blattimwind 8y agoFILE_FLAG_POSIX_SEMANTICS
- wrs 8y agoIIRC, the NTFS filesystem has its own collation tables on disk that are fixed when the volume is formatted, so its indexes are forever consistent but don’t necessarily match the ongoing changes to collations. Thus the shell has to re-sort everything before showing it, even though you’d think the filenames were already sorted by NTFS.
- barrkel 8y agoAbout 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?
- oconnor663 8y ago> 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?
- barrkel 8y agoThat'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.
- jandrese 8y agoCase normalization is it's own minefield in Unicode. This article discusses some of the challenges: https://stackoverflow.com/questions/6162484/why-does-modern-perl-avoid-utf-8-by-default https://stackoverflow.com/questions/6162484/why-does-modern-... In the first section points 16, 22, and 23 are relevant, in the second section look at points 8, 9, 10, 11, 12, 13, and 40. Sorting based on Unicode strings is tricky, especially if you want cross platform compatibility. It's better today but there are still a lot of edge cases to consider if you have any hope of getting the same sort twice from "interesting" data.
- phonon 8y agoWhat does Postgres Aurora use?
- cryptonector 8y agoPostgreSQL should use its own internationalization library, or ICU, but not the C library's I18N components. This would allow PG to ensure that any one version of PG also uses the same version of Unicode.
- cbsmith 8y agoOr, you know, you could just track the versioning of the library that you use for collation...
- alexchamberlain 8y agoMay I ask a stupid question? What's the difference between collation and encoding? Some people in this thread have suggested "use byte strings"; is it "enough" to "just" use UTF-8, like you should "just" use UTC (unless you know/Should know better)?
- BeeOnRope 8y agoCollation is basically a fancy way of saying "sorting" and should be independent of encoding (or course, certain encodings lend themselves more naturally to certain collation algorithms). In general collation needs to be locale-aware while encoding (in Unicode) largely doesn't care.
- ben509 8y agoTo get a bit into the theory: a string is a sequence of symbols drawn from some "alphabet", and we can define operations like concatenation, counting, matching and substring on it. In particular, a natural operation on strings is lexicographic comparison, whereby you compare the first characters of two strings, if they are different, the two strings compare that way, if they match, you proceed to look at the second character, etc. Encoding translates a string from one language to another, and it's often a reversible translation. The obvious case is to translate from Unicode to binary and back. In Unicode, the alphabet is the set of all Unicode codepoints. In binary, you have a stream of octets and the alphabet is the set of integers between 0 and 255. So UTF-8 is a standard that specifies how to perform such a translation. Since we're often going back and forth between Unicode and binary, and because real processors work with bytes, it's reasonable to call "encoding" translating a string to binary and "decoding" translating binary to a string. The math doesn't require that, though. Collation represents a string as an expression (usually also a string) that is trivially comparable. For instance, supposed I wanted an English-friendly way to compare band names. I might map all codepoints that look like A or E to "A", then K, S and C to C, etc., discarding any accents and maybe throwing out leading junk like "The" or "A". That's why a case-insensitive collation won't let you have Foo and FOO, because they would both collate as FOO. It also means, unlike encoding, collation is expected to be one-way.
- 8y ago
- butterisgood 8y agoStatic linking can be good.
- waynecochran 8y agoOf course if you have two applications communicating with each other that are statically linked with different version of the library you can still have inconsistency problems.
- CamperBob2 8y agoThat's more a protocol design problem than anything else.
- antirez 8y agoTo rely on anything non trivial provided by libc is always a risk, but here the problem is that collation is not the right tool for the job (implementing data structures were there is to compare elements). In Redis memcmp() is used, because byte-to-byte comparison is a much more stable story. This leaves the user with the problem of finding a suitable representation that is lexicographically sortable by the first bytes in a way that makes sense for the application, and that also contains, in the farest bytes, what the user really wants to return. Not viable for something higher level than Redis I guess.
- cbsmith 8y agoIt's not that simple. SQL in particular actually requires lexicographical sorting, so you need to do it somehow. The problem is relying on lexicographical sorting remaining fixed when it isn't. Per the unicode standard, lexicographical sorting rules are subject to change. So now you need a mechanism to detect/manage those changes.
- antirez 8y agoYes I understand that, that's why I said that it's not applicable to higher level systems. Btw to put my comment in context, also Redis has a similar feature to SQL DBs where it shows results in lexicographical sorting directly to the user, but it that case it just provides memcmp() byte-to-byte sorting. So what you do in order to index "Aèèé" is to apply a transformation to that string and store a transformed prefix and later the string itself, like "aeee:Aèéé" (ref: https://redis.io/commands/zrangebylex https://redis.io/commands/zrangebylex)
- senderista 8y agoYou’re talking about sort keys, no? That allows you to deal with collation in a higher layer (which generates the sort keys per desired collation), but delegate the actual sorting to a dumb lower layer (i.e., memcmp). (Just because I can’t resist, I’ll mention that sort keys are a perfect application for Hu-Tucker codes: https://cs.stackexchange.com/a/49586 https://cs.stackexchange.com/a/49586).
- macdice 8y agoPostgreSQL has the beginnings of a solution to this problem: version tracking for collations. I think it should be extended to track versions in a more fine grained way so that it can detect and reject this scenario (and related scenarios), and I think that libc implementations should provide a way to expose the version. I have proposed this for FreeBSD libc, and I hope someone proposes something similar for glibc (maybe me eventually). I think PostgreSQL should continue to support both ICU and libc collations (I don't think it's reasonable for every piece of software to use its own collation system, or for everyone to switch to ICU, I think libc should do a slightly better job). More recent discussion of various possible approaches on PostgreSQL -hackers list: https://www.postgresql.org/message-id/flat/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com https://www.postgresql.org/message-id/flat/CAEepm%3D0uEQCpfq... Proposal to add versions to libc collations in FreeBSD: https://reviews.freebsd.org/D17166 https://reviews.freebsd.org/D17166
- rurban 8y agoNot just different glibc's, also Unicode versions. I constantly have to update my towupper() function for my safelibc. musl is hopelessly behind, and glibc is not much better with Unicode support. ICU constantly has breaking changes, but at least keeps its tables up to date. Unicode itself changes its tables every year. This year we had 6 changes there. DB or Web clients really need to pass the unicode version in its API: like "sorted according to Unicode 11.0"
- rurban 8y agoThe updated variant of this postgresql/glibc warning and story is https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.... And glibc is still 2 years behind in its unicode version: they are at 9.0, current is 11.0. musl is at 6.0