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

21–30 of 82 posts

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

#21

Post by mnw21cam »

rraval wrote: post #16
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.

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

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

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

#22

Post by leeter »

barrkel wrote: post #17

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

IIRC the newer Unicode collation sensitive comparison functions hadn't been implemented until Vista

Looks like that's the case: https://docs.microsoft.com/en-us/windows/desktop/api/stringa...

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

#23

Post by leeter »

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.

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

#24

Post by greglindahl »

mnw21cam wrote: post #21
rraval wrote: post #16

Earlier quoted context omitted.

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

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

Normalization is a separate issue, you can normalize and then use the C collation order.

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

#25

Post by rraval »

mnw21cam wrote: post #21
rraval wrote: post #16

Earlier quoted context omitted.

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

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

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

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

#26

Post by Someone »

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 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/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.)”)

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

#27

Post by anarazel »

jakobegger wrote: post #15
emddudley wrote: post #7

Earlier quoted context omitted.

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

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

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

#28

Post by BeeOnRope »

greglindahl wrote: post #24
mnw21cam wrote: post #21

Earlier quoted context omitted.

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

Normalization is a separate issue, you can normalize and then use the C collation order.

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.

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

#29

Post by toast0 »

rraval wrote: post #16
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.

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

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

Post reply on HN