Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The dangers of streaming across versions of glibc: A cautionary tale (2014)

postgresql.org

Post reply on HN
Print view View original

51–60 of 82 posts

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#51

Post by antirez »

cbsmith wrote: post #48
antirez wrote: post #45

To 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 byt…

It'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.

Yes 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)

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#52

Post by ben509 »

nine_k wrote: post #42
BeeOnRope wrote: post #38

Earlier quoted context omitted.

It'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…

If 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.

It's wrong to claim indexes are "for" numbers and ASCII. They're for names of things, and names fill in the propositions the relational model is built on.

If you're using surrogate keys for everything, it means your tools are broken or you don't trust them. It may be the least worst available option to plaster over them, but you shouldn't accept that as a best practice.

> For strings that have encodings and locales, you likely need a full-text index, provided by your DB or by something like Solr / ElasticSearch.

Really, a reverse index, which, as its name implies, does the opposite of what you want if you're trying to index something.

Those products "work" by doing text preprocessing (stemming and such), which anyone can do, and then they toss a bunch of garbage back and let the user sort through it.

That doesn't solve the problem a database index is trying to address.

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#53

Post by chousuke »

nine_k wrote: post #47
krylon wrote: post #43

Earlier quoted context omitted.

There is an uppercase ß, actually: https://en.wikipedia.org/wiki/Capital_%E1%BA%9E Not that it has any impact on your point. But as a German who only learned about it fairly recently, I have rather ambivalent feelings about it. ;-)

Nice! German has funnier problems, though, because a vowel with an umlaut can be represented as that vowel + e, that is, "fuer" is a legit representation of "für". How do you normalize that? Turning to one canonical representation works most of the time, but sometimes you also need letter-to-letter correspondence.

Is that really legit in German? I know Finnish umlauts (ä, ö) get sometimes mangled to ae or oe, but they are definitely not valid alternative spellings nor are they pronounced even close to similar.

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#54

Post by ben509 »

alexchamberlain wrote: post #36

May 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)?

To 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.

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#55

Post by anyfoo »

chousuke wrote: post #53
nine_k wrote: post #47

Earlier quoted context omitted.

Nice! German has funnier problems, though, because a vowel with an umlaut can be represented as that vowel + e, that is, "fuer" is a legit representation of "für". How do you normalize that? Turning to one canonical representation works most of the time, but sometimes you also need letter-to-letter correspondence.

Is that really legit in German? I know Finnish umlauts (ä, ö) get sometimes mangled to ae or oe, but they are definitely not valid alternative spellings nor are they pronounced even close to similar.

Yeah, completely legit and not uncommon at all (though advances in locale support have probably made it less necessary in recent decades). As an official transliteration, documented and taught in school, it is definitely pronounces the same.

It seems like German could actually be at fault for your bogus transliteration issues in Finnish, then. Sorry about that! 8)

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#56

Post by anyfoo »

nine_k wrote: post #47
krylon wrote: post #43

Earlier quoted context omitted.

There is an uppercase ß, actually: https://en.wikipedia.org/wiki/Capital_%E1%BA%9E Not that it has any impact on your point. But as a German who only learned about it fairly recently, I have rather ambivalent feelings about it. ;-)

Nice! German has funnier problems, though, because a vowel with an umlaut can be represented as that vowel + e, that is, "fuer" is a legit representation of "für". How do you normalize that? Turning to one canonical representation works most of the time, but sometimes you also need letter-to-letter correspondence.

It also gets interesting with proper names. It's perfectly legitimate to transliterate "Herr Schröder" to "Herr Schroeder" (and if you don't have ö at your disposal you have to), but a proper name that starts out with the transliteration, like "Dr. Oetker" can usually not be transliterated to "ö".

In some cases that might be because the "oe" was never an "ö" in this case, even if people might have shifted to pronouncing it that way. But in other cases I imagine that it was intended and did start out as an "ö" sound, but people just decided to write the name with "oe".

(Same with the other Umlauts.)

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#57

Post by nine_k »

cbsmith wrote: post #49
nine_k wrote: post #42

Earlier quoted context omitted.

If 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.

I'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.

I'd posit that this is not universally true. It may be true for English names.

It may be implementable for e.g. French names: a name like "François" may be stored as UTF-8 with "ç" always represented as a composite pair (or always a single character), and the application layer knows and uses this.

It must be a dubious idea for German names when you may need to see "Müller" and "Mueller" as the same name, but also keep e.g. only "Mueller" as the form referenced in legal papers.

Once you start considering anything non-ASCII, things become hairy. (Of course, if you only work for the US market and don't care about any international travelers as customers, you can continue using these introductory courses as a guidance.)

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#58

Post by nine_k »

ben509 wrote: post #52
nine_k wrote: post #42

Earlier quoted context omitted.

If 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.

It's wrong to claim indexes are "for" numbers and ASCII. They're for names of things, and names fill in the propositions the relational model is built on. If you're using surrogate keys for everything, it means your tools are broken or you don't trust them. It may be the least worst available option to plaster over them, but you shouldn't accept that as a best practice. > For strings that have encodings and locales,…

Indexes are not "for", they will happily index anything. But they are "best used for" certain things.

Tools are not broken; reality is. So you have to work around assumptions that are pertinent to blind usage of technology.

You need to think how to use a tool (such as a Unicode-capable DB text type) to get a correct result. And first you need to understand what constitutes the correct result. Else you may end up with strings that are equally correct and nominally equal, but bytewise different, so search by index fails.

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#59

Post by macdice »

PostgreSQL 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...

Proposal to add versions to libc collations in FreeBSD: https://reviews.freebsd.org/D17166

Re: The dangers of streaming across versions of glibc: A cautionary tale (2014)

#60

Post by rurban »

Not 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"

Post reply on HN