collation-provider

# collation-provider — builtin, ICU, libc — the 3-way split

Every text comparison in PG (equality, LIKE, ORDER BY, index key comparison) goes through a **collation**. Collations are catalog objects (`pg_collation`) that tell the backend WHICH library to use for comparison, WHICH locale within that library, and WHICH encoding the strings are in.

Since PG 17, collations come from **three providers**:

- `builtin` — PG-owned; deterministic Unicode + a few special cases. Added PG 17 to avoid OS/library upgrade breakage.
- `icu` — ICU library (International Components for Unicode). Rich, version-tracked, portable across OSes.
- `libc` — OS-provided (glibc / macOS / Windows). Fast + backwards-compatible + fragile (OS upgrades change collation order silently).

## The file map

| File | KB | Role |
|---|---:|---|
| `utils/adt/pg_locale.c` | 50 | The dispatcher. `pg_locale_deterministic`, `pg_strcoll`, `pg_strxfrm`, provider dispatch table. Handles the fast paths (deterministic C-locale). |
| `utils/adt/pg_locale_builtin.c` | 7 | PG-owned builtin provider — `C.UTF-8`, `unicode`, `PG_C_COLLATION_OID`. Small because the rules ARE the code. |
| `utils/adt/pg_locale_icu.c` | 36 | ICU integration — UCollator setup, version reading, `u_strcmp` etc. Conditional on `--with-icu`. |
| `utils/adt/pg_locale_libc.c` | 34 | libc integration — `newlocale` / `strcoll_l` / `strxfrm_l`. Historical + widely deployed. |
| `include/utils/pg_locale.h` | 8 | Public API surface + `pg_locale_t` opaque handle. |
| `utils/mb/mbutils.c` | 53 | Encoding conversion — client-encoding ↔ server-encoding, wchar helpers. NOT collation but tightly related. |
| `utils/mb/conv.c` | 13 | Conversion function dispatch. |
| `utils/mb/Unicode/` | — | Auto-generated codepoint mapping tables. |

## The provider dispatch

Every text-op call site does:

1. Look up the collation OID from the input columns / cast / GUC.
2. Fetch a `pg_locale_t` via `pg_newlocale_from_collation(colloid)` — this is CACHED per backend.
3. Call the provider-specific op:
   - `pg_strcoll_l(a, b, locale)` for compare.
   - `pg_strxfrm(dst, src, srclen, locale)` for sort-key generation.
   - Etc.

The `pg_locale_t` struct carries a `provider` tag; every dispatch routine switches on it.

## The `builtin` provider (PG 17+)

Motivation: OS/ICU upgrades change collation ordering silently, invalidating indexes. The builtin provider is PG-owned, versioned by PG major release, and (mostly) deterministic Unicode.

Two builtin locales:

- `C.UTF-8` (`PG_C_COLLATION_OID` — literally OID hardcoded) — codepoint comparison + UTF-8 aware ctype (upper/lower/digit).
- `unicode` — Unicode ROOT collation (DUCET) — the canonical Unicode ordering, no locale-specific tailoring.

**Deterministic by design** — two byte-equal strings compare equal, two byte-unequal strings never compare equal at the `builtin` provider. No expansion ligatures. This is why `builtin` is safe for hash-based dedup (indexes, DISTINCT).

Adding a new builtin collation would touch `pg_locale_builtin.c` + `pg_collation.dat` for the catalog entry + bump `CATALOG_VERSION_NO`.

## The ICU provider

Uses libicuuc + libicui18n. Each ICU collation has:

- A locale identifier (`en-US`, `de-DE-u-co-phonebk`).
- A rule string (optional customization on top of the locale).
- A version identifier (from ICU's `ucol_getVersion`).

The version identifier is stored in `pg_collation.collversion` at CREATE time. On collation use, PG compares the stored version to the CURRENT ICU version — if they differ, `pg_collation_actual_version()` reports the mismatch and functions like `ALTER COLLATION ... REFRESH VERSION` are expected to run after ICU is upgraded, followed by REINDEX of affected indexes.

## The libc provider

Uses OS `newlocale` / `strcoll_l` etc. Fast but:

- **glibc-version dependent** — glibc 2.28 (2018) changed collation ordering. Indexes built on older glibc silently mis-sort on newer.
- **No version fingerprint** — libc doesn't expose a version identifier PG can store. Some catalog entries carry the version PG guesses from `uname` but it's unreliable.
- **Locale must exist on the system** — a collation for `de_DE.UTF-8` fails if the OS doesn't have that locale generated.

**Recommendation in the docs**: use `builtin` for deterministic C.UTF-8, `icu` for language-aware collation with version tracking. libc is legacy but ubiquitous.

## Encoding conversion (the mb/ side)

Separate from collation but interlocks:

- **Server encoding** — fixed at initdb (`ENCODING = 'UTF8'` etc.), same for the whole cluster.
- **Client encoding** — per-session, via `SET client_encoding`.
- Every text going over the wire gets converted: server → client on send, client → server on receive.

Conversion procs live in `src/backend/utils/mb/conversion_procs/` — one directory per encoding pair (e.g. `utf8_and_win1252`).

Multibyte string handling helpers (character length, character-boundary detection) are in `mbutils.c`. Every text function that operates on characters (not bytes) uses these — e.g. `substring`, `length`, `left`, `right`.

## Common patch shapes

### Add a new builtin collation

- Add row to `pg_collation.dat` with `collprovider => 'b'`.
- Add matching case in `pg_locale_builtin.c`'s dispatch.
- Update `pg_locale_deterministic` if the new collation is non-deterministic.
- Bump `CATALOG_VERSION_NO`.
- Test in `src/test/regress/sql/collate.linux.utf8.sql` + `.icu.utf8.sql`.

### Extend ICU integration

- Touch `pg_locale_icu.c`.
- Consider version tracking — every new field affects the `collversion` string.
- Test with multiple ICU versions locally (buildfarm has several).

### Debug "index scan skips rows after glibc upgrade"

- `pg_collation.collversion` should show the mismatch (if PG stored one).
- `SELECT pg_collation_actual_version('en_US'::regcollation);` — current OS version.
- Fix: `ALTER COLLATION ... REFRESH VERSION` + `REINDEX INDEX ...` for every affected index.
- Prevention: migrate to `icu` provider for cross-OS-upgrade stability.

### Add a new encoding conversion

- New directory in `utils/mb/conversion_procs/<encA>_and_<encB>/`.
- Meson-buildable module producing `<encA>_to_<encB>` and `<encB>_to_<encA>` functions.
- Grant SQL-callable via `pg_conversion` catalog row.
- Test with `SELECT convert('...'::bytea, 'encA', 'encB');`.

## Pitfalls

- **`collversion` mismatch is silent by default** — a `SELECT` may return wrong results if the OS collation library upgraded and the index is stale. PG emits WARNINGs on `pg_collation_actual_version` mismatch but doesn't refuse the query.
- **`ORDER BY` in tests is provider-dependent** — a regress test using a language-aware locale may pass on the author's box (glibc 2.31) and fail on the CI (glibc 2.35) with entirely different ordering. Prefer `C.UTF-8` (builtin) for regress tests unless testing collation semantics specifically.
- **`LIKE 'x%'` optimization requires locale-independent prefix comparison** — deterministic collations (C, builtin) enable a fast-path where the planner can use B-tree index range scans on `col LIKE 'prefix%'`. Non-deterministic ICU collations disable this.
- **Non-deterministic collations don't work with hash indexes** — hash equality requires byte-identity, which non-deterministic collations don't provide.
- **`char_length` / `octet_length`** — different values for multibyte. `char_length('café')` = 4, `octet_length('café')` = 5 in UTF-8. Common bug.
- **Client-encoding conversion happens per wire message** — an app that sends 1M small INSERT statements with client_encoding ≠ server_encoding pays per-conversion cost.
- **`pg_locale_deterministic` returns FALSE for ICU with custom tailorings** — some ICU rules add expansions/contractions that make equal-bytes non-equal-collation. Check with `SELECT collationdeterministic FROM pg_collation`.
- **Backend caches `pg_locale_t` per collation** — hot-reload of ICU (e.g. ldconfig then keeping backends alive) has undefined behavior. Restart the backends.

## Related corpus

- **File docs**: `knowledge/files/src/backend/utils/adt/pg_locale.c.md` + the 3 provider files + `mbutils.c.md`.
- **Subsystems**: `utils-cache` (relcache/typcache cache collation info), `parser-and-rewrite` (parse-time collation inference via `parse_collate.c`).
- **Related planning**: `planning/sp7-tablefunc-quoting/` — the tablefunc contrib bug touched identifier quoting which interacts with encoding.
- **Docs distilled**: `knowledge/docs-distilled/§24` (locale + collation + multibyte) + `§23.2` (specific collations chapter).

## Corpus-chain shortcut

```
python3 scripts/corpus-chain.py --file src/backend/utils/adt/pg_locale.c
python3 scripts/corpus-chain.py --file src/backend/utils/mb/mbutils.c
```

Neighborhood: 4-6 files under `utils/adt/` for collation + ~15 under `utils/mb/` for encoding.

## Boundary

**Use this skill** for `pg_locale*.c` + `mb/` encoding + collation catalog + provider dispatch.

**Don't use** for:
- **`tsearch`** — full-text search dictionaries (`utils/adt/tsvector*.c` + `tsearch/*.c`). Separate subsystem.
- **`CREATE TEXT SEARCH CONFIGURATION`** — text-search DDL, not collation.
- **`nls.mk` / gettext** — server-message localization. Different concern; touches `src/backend/utils/misc/*` and per-locale `.po` files.
- **Client-side encoding** — `psql \encoding` etc. is in `src/bin/psql/`.
- **Custom text ordering via callbacks** — that's implementing a function using existing collations; use `fmgr-and-spi` for the fn side.