Decide the canonical order of string-backed value sets for 11.0.0: code point order, which PostgreSQL can sort, or ordinal #479

Problem

String-backed value sets (StringSet, StringSet<T>) sort by StringComparer.Ordinal, which compares UTF-16 code units. PostgreSQL can't reproduce that order with any built-in collation. COLLATE "C" on a UTF8 database compares bytes, which is Unicode code point order. Measured with the same four strings (PostgreSQL 16, .NET 10):

The two orders differ only where one string has a supplementary character (U+10000 and above, a surrogate pair in UTF-16) and the other has a character in U+E000–U+FFFF at the same position. Everywhere else they agree.

So text is the one family #478 can't canonicalize in SQL. Add, Intersect, Except and a canonical Union over strings stay in memory, and the union guard keeps refusing for them, unless the order changes.

Options

1. Code point order (recommended). CanonicalComparer compares code points. Then ORDER BY x COLLATE "C" matches exactly, and text joins Translate value set Intersect, Except, Add and a canonical Union for the non-text families with composed SQL #478 with no custom function and no migration. C is deterministic, so DISTINCT is byte equality, which is ordinal equality for every string PostgreSQL text can hold (it holds no lone surrogates and, since 10.0.0, no NUL). The client-side cost is small: compare ordinally, and correct only when the first differing pair is a surrogate against U+E000–U+FFFF.
2. A function that builds a UTF-16 sort key, installed through a SchemaKit contributor (SchemaKit: the contributor contract, so packages add schema objects instead of replacing EF services CodoMetis.EFCore.SchemaKit#7). It keeps ordinal order. But it's 11.x only, a DDL dependency for every consumer, a second SQL path to test, and per-element string work in every sort.
3. Keep ordinal. String set operations stay in memory, as documented today.

Does it change an answer?

Option 1 does, so it's breaking and belongs in 11.0.0:

• The element order of an affected set changes: Values, the indexer, the literal and the JSON array.
• A row written by 10.x that holds an affected pair is no longer canonical. Reads canonicalize it, but = against that column and a subscript on it answer differently until the row is rewritten. docs/migration.md gives the UPDATE that rewrites only those rows.
• Set equality, Contains, Count and the subset/overlap operators don't change: they don't depend on order.

To check

• C sorts by code point only on a UTF8 database. Decide whether a translation that sorts text refuses, or is documented, on another server encoding.
• pg_c_utf8 (PostgreSQL 17+) gives the same order. Use "C", which exists on the 16 floor.
• The AGENTS.md rules "String-backed families sort ordinal" and "Do not 'fix' this by sorting in SQL" are reworded with the change, and docs/value-sets.md says "code point order" where it says "ordinal".

Done when

• the option is decided here
• for option 1: the comparer, a contract test over every pair class (BMP below U+D800, U+E000–U+FFFF, supplementary), a seed that restores StringComparer.Ordinal and is caught, the migration section and the changelog's "what can break you" row
• for option 3: this issue closes, and Translate value set Intersect, Except, Add and a canonical Union for the non-text families with composed SQL #478's text exclusion stays as documented