Les collations dans PostgreSQL : libc, ICU et le fournisseur builtin, et pourquoi une mise à jour de bibliothèque peut corrompre un index

Ce que c'est

La documentation définit une collation comme un objet SQL qui associe un nom à des données de locale issues d'un fournisseur : libc (les locales du système d'exploitation, liées à l'encodage de la base de données), icu (la bibliothèque ICU, avec des noms sous forme d'étiquettes BCP 47 telles que de-x-icu, indépendantes de l'encodage) ou builtin (uniquement C, C.UTF-8 et PG_UNICODE_FAST, qui trient par point de code). Les paramètres LC_COLLATE et LC_CTYPE de la base de données sont fixés à la création et ne peuvent être modifiés qu'en créant une nouvelle base de données. Une colonne, un index ou une expression peut porter sa propre collation (COLLATE "de-x-icu"), et CREATE COLLATION permet d'en définir des personnalisées, y compris des collations non déterministes (deterministic = false) pour une comparaison insensible à la casse ou aux accents.

Pourquoi c'est important

Premièrement, l'ordre linguistique n'est pas l'ordre des points de code : ORDER BY name et les conditions d'intervalle sur du texte dépendent de la collation, et un index ne sert un tri que si sa collation correspond. Deuxièmement, un B-tree stocke les clés dans l'ordre de la collation ; lorsque la bibliothèque C du système d'exploitation ou ICU change ses règles, les index existants ne concordent plus avec la nouvelle comparaison, et la documentation d'ALTER COLLATION avertit que cela peut corrompre des index. PostgreSQL enregistre une version de collation et avertit en cas de désaccord ; le remède est un REINDEX des objets concernés, suivi d'un ALTER COLLATION ... REFRESH VERSION.

Comment l'appliquer

• Choisir la collation par défaut à la création du cluster ou de la base de données. C ou le C.UTF-8 intégré conviennent aux identifiants, aux clés et aux données machine : la documentation décrit C comme stable à travers toutes les versions pour un encodage donné, et C.UTF-8 comme stable au sein d'une version majeure. N'utiliser une collation linguistique que là où des utilisateurs lisent du texte trié.
• Préférer ICU à libc pour les collations linguistiques lorsque les deux sont disponibles : le chapitre sur les locales décrit le comportement d'ICU comme indépendant du système d'exploitation et de l'encodage de la base de données.
• Placer l'ordre destiné aux utilisateurs sur la colonne ou la requête (ORDER BY title COLLATE "de-x-icu") et créer un index avec la même collation si ce tri doit être rapide.
• Utiliser une collation non déterministe pour une unicité insensible à la casse là où ses limites documentées (comparaison plus lente, pas de déduplication B-tree, certaines correspondances de motifs indisponibles) sont acceptables.
• Après une mise à jour du système d'exploitation, une mise à jour d'ICU ou un pg_upgrade vers des bibliothèques plus récentes, lister les désaccords avec SELECT collname FROM pg_collation WHERE collversion <> pg_collation_actual_version(oid), réindexer, puis rafraîchir les versions ; la collation par défaut de la base de données se rafraîchit séparément avec ALTER DATABASE ... REFRESH COLLATION VERSION.

Pièges

Une réplique construite à partir d'une image système différente peut embarquer une bibliothèque C différente, et donc un ordre différent pour le même index. Sous la collation C, seules les lettres ASCII de A à Z comptent comme des lettres, si bien que upper() et lower() laissent les autres lettres inchangées. REFRESH VERSION enregistre la nouvelle version sans vérifier que les objets ont bien été reconstruits.