4 ms·

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 sorti

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.

antirez 8y ago
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 https://redis.io/commands/zrangebylex)
- senderista 8y ago
  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 https://cs.stackexchange.com/a/49586).
rurban 8y ago
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
- cbsmith 8y ago
  Right... so properly detecting when U_UNICODE_VERSION in the replication target differs from U_UNICODE_VERSION in the source, and adjusting for it is what's needed. PostgreSQL wasn't doing this in 2014.
  - rurban 8y ago
    Exactly. I'm just not happy with the types of these defines. Only major updates need to be tracked, so the PCRE format seems to be the best. But all others are using strings, not an integer.

antirez 8y ago

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 https://redis.io/commands/zrangebylex)

senderista 8y ago
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 https://cs.stackexchange.com/a/49586).

rurban 8y ago

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

cbsmith 8y ago
Right... so properly detecting when U_UNICODE_VERSION in the replication target differs from U_UNICODE_VERSION in the source, and adjusting for it is what's needed. PostgreSQL wasn't doing this in 2014.
- rurban 8y ago
  Exactly. I'm just not happy with the types of these defines. Only major updates need to be tracked, so the PCRE format seems to be the best. But all others are using strings, not an integer.