Kollationen in PostgreSQL: libc, ICU und der eingebaute Provider, und warum ein Bibliotheks-Upgrade einen Index beschädigen kann

Worum es geht

Die Dokumentation definiert eine Kollation als SQL-Objekt, das einen Namen auf Locale-Daten eines Providers abbildet: libc (die Locales des Betriebssystems, an die Datenbankkodierung gebunden), icu (die ICU-Bibliothek, mit Namen als BCP-47-Tags wie de-x-icu, unabhängig von der Kodierung) oder builtin (nur C, C.UTF-8 und PG_UNICODE_FAST, die nach Code Point sortieren). LC_COLLATE und LC_CTYPE der Datenbank werden bei deren Erstellung festgelegt und lassen sich nur durch Erstellen einer neuen Datenbank ändern. Eine Spalte, ein Index oder ein Ausdruck kann eine eigene Kollation tragen (COLLATE "de-x-icu"), und CREATE COLLATION definiert eigene, einschliesslich nichtdeterministischer Kollationen (deterministic = false) für gross-/kleinschreibungs- oder akzentunabhängigen Vergleich.

Warum es wichtig ist

Erstens ist die sprachliche Reihenfolge nicht die Code-Point-Reihenfolge: ORDER BY name und Bereichsbedingungen auf Text hängen von der Kollation ab, und ein Index dient einer Sortierung nur, wenn seine Kollation übereinstimmt. Zweitens speichert ein B-Tree Schlüssel in Kollationsreihenfolge; ändert die C-Bibliothek des Betriebssystems oder ICU ihre Regeln, stimmen bestehende Indizes nicht mehr mit dem neuen Vergleich überein, und die Dokumentation zu ALTER COLLATION warnt, dass dies zu beschädigten Indizes führen kann. PostgreSQL vermerkt eine Kollationsversion und warnt bei Abweichung; die Abhilfe ist REINDEX der betroffenen Objekte, gefolgt von ALTER COLLATION ... REFRESH VERSION.

So wird es angewendet

• Die Standardkollation beim Erstellen von Cluster oder Datenbank wählen. C oder das eingebaute C.UTF-8 eignen sich für Bezeichner, Schlüssel und Maschinendaten: Die Dokumentation beschreibt C als über alle Versionen hinweg stabil für eine gegebene Kodierung und C.UTF-8 als stabil innerhalb einer Hauptversion. Eine Sprachkollation nur dort verwenden, wo Nutzende sortierten Text lesen.
• ICU gegenüber libc für Sprachkollationen bevorzugen, wo beides verfügbar ist: Das Kapitel zu Locales beschreibt das Verhalten von ICU als unabhängig vom Betriebssystem und von der Datenbankkodierung.
• Die für Nutzende sichtbare Reihenfolge auf die Spalte oder die Abfrage legen (ORDER BY title COLLATE "de-x-icu") und einen Index mit derselben Kollation erstellen, wenn diese Sortierung schnell sein muss.
• Eine nichtdeterministische Kollation für gross-/kleinschreibungsunabhängige Eindeutigkeit verwenden, wo ihre dokumentierten Grenzen (langsamerer Vergleich, keine B-Tree-Deduplizierung, manche Musterabgleiche nicht verfügbar) akzeptabel sind.
• Nach einem OS-Upgrade, einem ICU-Upgrade oder pg_upgrade auf neuere Bibliotheken Abweichungen auflisten mit SELECT collname FROM pg_collation WHERE collversion <> pg_collation_actual_version(oid), neu indexieren und dann die Versionen aktualisieren; die Standardkollation der Datenbank wird separat mit ALTER DATABASE ... REFRESH COLLATION VERSION aktualisiert.

Stolpersteine

Eine aus einem anderen OS-Image gebaute Replik kann eine andere C-Bibliothek und damit eine andere Reihenfolge für denselben Index tragen. Unter der Kollation C zählen nur die ASCII-Buchstaben A bis Z als Buchstaben, sodass upper() und lower() andere Buchstaben unverändert lassen. REFRESH VERSION vermerkt die neue Version, ohne zu prüfen, dass die Objekte neu aufgebaut wurden.