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

61–70 of 82 posts

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

#61

Post by BeeOnRope »

jrochkind1 wrote: post #40
BeeOnRope wrote: post #28

Earlier quoted context omitted.

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

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

Well 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

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

#62

Post by BeeOnRope »

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.

Um, no?

The most important job of a DB is store records which largely consist of numeric (including boolean) values and strings. Strings often need to be outside the ASCII range, and even in existing cases where they aren't supported, it's a limitation of the application and the users would really prefer unicode strings.

Even if you just target English speaking people from the US or whatever, there are a variety of non-ASCII characters that come up all the time, including accented characters, various currency signs, etc.

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

#63

Post by BeeOnRope »

nine_k wrote: post #57
cbsmith wrote: post #49

Earlier quoted context omitted.

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 kee…

Of course it's hairy - that's kind of the point of this whole discussion!

People who use databases naturally end up wanting to store Unicode strings, and people naturally want to query them in locale-sensitive ways. Your Mueller example is a fine example of this. Databases have deep support for this, and you can also build your own compromise, so to speak, on top of the database with ICU or another library if it doesn't meet your needs.

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

#64

Post by BeeOnRope »

nine_k wrote: post #58
ben509 wrote: post #52

Earlier quoted context omitted.

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 wit…

So in summary it's a hard problem and requires some smarts to get right? Then we agree.

Retreating to ASCII is not the answer, and "full text search engines" don't even solve the same class of problem, such as say paginating a list of local towns by name.

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

#65

Post by rurban »

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.

That's done via

    unicode/uchar.h:
    #define U_UNICODE_VERSION "9.0"
in icu. pcre2 has PCRE2_CONFIG_UNICODE_VERSION 10 fribidi has FRIBIDI_UNICODE_VERSION "6.2.0"

libc's need something similar. In safelibc I do have nothing so far, but I'm at 11.0

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

#66

Post by jcranmer »

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.

This brings up something that is perhaps easy to forget: the interpretation of diacritics is far from universal. The diacritic in the character 'ä' can be one of two semantically different diacritics. It can be a diaeresis, a diacritic whose function is to mark that vowel starts the next syllable rather than existing as part of a diphthong; or it can be an umlaut, whose purpose is to indicate that it is a different vowel sound altogether. As far as any charset is concerned, though, despite those very different semantics, the two things are the same diacritical mark [1].

Even beyond the issue with two concepts using the same glyph, the interpretation of the same diactrics among different languages is inconsistent. English tends to drop diacritics to the point that many people think that English doesn't use them; German uses expansion (so ä becomes ae). As you mention, some languages are incomprehensible either way, so they need to be preserved. And sorting and collation is even more fun!

[1] This does mean that Unicode's insistence that characters represent semantic differences rather than graphical differences can come across as rather arbitrary. The original purpose of Unicode was to unify different character sets together, so it preserves character differentiation that existed in antecedent charsets but tends to otherwise unify characters in practice.

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

#68

Post by mjevans »

Someone wrote: post #26
gumby wrote: post #12

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.

ICU 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…

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

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

#69

Post by voltagex_ »

leeter wrote: post #23
theamk wrote: post #13

This, 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.

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

Are you talking about WSL or some other way that applications can use POSIX on Windows?

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

#70

Post by senderista »

antirez wrote: post #51
cbsmith wrote: post #48

Earlier quoted context omitted.

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…

You’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).

Post reply on HN