4 ms·

AFAIK, 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 ago
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.html https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade....
- jakobegger 8y ago
  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
  - anarazel 8y ago
    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.
    - jakobegger 8y ago
      Really? 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 ago
Introduction to ICU in PostgreSQL: https://blog.2ndquadrant.com/icu-support-postgresql-10/ https://blog.2ndquadrant.com/icu-support-postgresql-10/
jakobegger 8y ago
This was not fixed in PostgreSQL 10. ICU collations are not used by default, you have to explicitly enable them, and you can't set them as the database default. And even if you use ICU collations, I'm not sure if indices actually detect if the ICU versions don't match. Maybe they improved some of the issues in PostgreSQL 11 (haven't been following the ICU issue in detail), but I don't think this issue is fixed.

emddudley 8y ago

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.html https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade....

jakobegger 8y ago
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
- anarazel 8y ago
  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.
  - jakobegger 8y ago
    Really? 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 ago

Introduction to ICU in PostgreSQL: https://blog.2ndquadrant.com/icu-support-postgresql-10/ https://blog.2ndquadrant.com/icu-support-postgresql-10/

jakobegger 8y ago

This was not fixed in PostgreSQL 10. ICU collations are not used by default, you have to explicitly enable them, and you can't set them as the database default. And even if you use ICU collations, I'm not sure if indices actually detect if the ICU versions don't match. Maybe they improved some of the issues in PostgreSQL 11 (haven't been following the ICU issue in detail), but I don't think this issue is fixed.