Migrations SQL : `sort` en locale UTF-8 change l’ordre

Des migrations SQL appliquées dans l’ordre de leurs noms de fichiers ne s’appliquent pas dans le même ordre partout : sort en locale UTF-8 ignore la ponctuation, et place 20260512_newsletter_fix_unique.sql avant 20260512_newsletter.sql, donc le correctif avant la table qu’il corrige. Sur une app de signalements (Supabase self-hosted, PostgreSQL 15), je l’ai découvert en créant la première base depuis les seules migrations du dépôt. Les tests, eux, restaient verts : Node trie par code de caractère. Le correctif : LC_ALL=C sort, et un test qui refuse les paires de noms ambiguës.

Sans table de suivi, l’ordre dépend du trieur

Les migrations de ce projet sont des fichiers .sql datés, joués à la main avec psql, sans table de suivi (la table où un outil de migration note ce qui a été appliqué). L’ordre d’application est donc celui des noms de fichiers, tel que le calcule l’outil qui les liste.

Deux fichiers du même jour suffisent à créer l’ambiguïté :

20260512_newsletter.sql              ← crée la table
20260512_newsletter_fix_unique.sql   ← remplace un index de cette table

Démonstration : `sort` en locale UTF-8 contre `LC_ALL=C sort`

Reproductible sur n’importe quel Linux avec la locale fr_FR.UTF-8 installée (testé avec GNU coreutils 9.4, glibc 2.39, bash 5.2) :

$ printf '%s\n' 20260512_newsletter.sql 20260512_newsletter_fix_unique.sql > m.txt

$ LC_ALL=fr_FR.UTF-8 sort m.txt
20260512_newsletter_fix_unique.sql
20260512_newsletter.sql

$ LC_ALL=C sort m.txt
20260512_newsletter.sql
20260512_newsletter_fix_unique.sql

en_US.UTF-8 donne le même résultat. Et ls comme le glob de bash (for f in *.sql) suivent eux aussi la locale : correctif en premier.

Pourquoi : la collation est la règle de comparaison de chaînes d’une locale. Celle de glibc pour les locales UTF-8 ignore la ponctuation au premier niveau de comparaison : elle compare 20260512newsletterfixuniquesql à 20260512newslettersql, et f passe avant s. En locale C, la comparaison se fait octet par octet : . (46) précède _ (95), et la table passe en premier.

Côté Node, Array.prototype.sort() sans comparateur compare les chaînes par unités de code UTF-16, comme LC_ALL=C pour des noms ASCII (vérifié avec Node 24) :

$ node -e 'console.log(["20260512_newsletter_fix_unique.sql","20260512_newsletter.sql"].sort())'
[ '20260512_newsletter.sql', '20260512_newsletter_fix_unique.sql' ]

Trieur	Ordre obtenu	Résultat
`sort`, `ls`, glob bash en `fr_FR.UTF-8`	correctif, puis table	échec
`LC_ALL=C sort`	table, puis correctif	OK
Node `.sort()` (harnais de tests)	table, puis correctif	OK

Le harnais de tests RLS trie en JavaScript : tests verts, apply cassé. Sur le poste, la boucle shell s’arrêtait sur relation "public.newsletter" does not exist.

Un ordre de migrations qui dépend de la locale de celui qui les applique n’est pas un ordre : c’est un tirage.

Le correctif : lever l’ambiguïté, puis l’interdire

Trois mesures :

Renommer : le correctif et sa v2 ont été redatés en 20260513_… et 20260514_…. Lever l’ambiguïté vaut mieux que compter sur un drapeau qu’on oubliera.
Tester : un test Vitest compare l’ordre par code ([...noms].sort()) à un ordre qui ignore ., _ et -, et échoue sur toute divergence. Un second cas vérifie qu’il détecte encore une paire ambiguë fictive.
Documenter : la procédure d’application passe à LC_ALL=C sort :

for f in $(ls supabase/migrations/<nouvelles>.sql | LC_ALL=C sort); do
  psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f "$f"
done

Au passage, une table historique qu’aucune migration ne créait, alors qu’une autre lui ajoutait une colonne, est désormais créée par 00000000_legacy_reports.sql : un préfixe qui la place en tête quel que soit le tri. Après la livraison, la suite RLS passait toujours : 322 tests, dont les quatre nouveaux.

Rejouer une ancienne migration défait une policy sans erreur

Même famille de défaut, autre symptôme. Sur la base de recette, un rejeu partiel de la liste a fait rejouer 20260428_manager_schema.sql après 20260706_100_rls_perf_optimize.sql. Cette dernière avait remplacé plusieurs policies permissives par des versions fusionnées, et une migration suivante avait resserré la policy de lecture des signalements. Le rejeu a recréé les policies d’origine :

les create policy réussissent, puisque leurs noms n’existent plus ;
les create table échouent sans conséquence, la table est déjà là.

PostgreSQL combine les policies permissives d’une même commande en OR : l’ancienne policy, plus large, rendait le resserrement inopérant. Aucune erreur, aucun test rouge : une base reconstruite depuis le dépôt n’a pas la divergence. Écart mesuré : 18 policies en trop et 10 manquantes par rapport à une base fraîchement migrée.

Le contrôle qui l’aurait détecté prend dix secondes : comparer l’inventaire pg_policies de la cible à celui d’une base neuve.

Q="select tablename||'|'||policyname||'|'||cmd from pg_policies
   where schemaname='public' order by 1"
psql "$TARGET_URL" -tAc "$Q" > cible.txt
psql "$FRESH_URL"  -tAc "$Q" > attendu.txt   # base reconstruite depuis le dépôt
diff attendu.txt cible.txt                     # toute différence est une dérive

⚠️ Sur ces mêmes migrations de newsletter, deux autres pièges : supabase-js rejette onConflict: "lower(email),source" avec column 'lower' does not exist (il attend des noms de colonnes, pas une expression), et ON CONFLICT sur un index unique partiel exige de répéter sa clause WHERE, ce que l’upsert de supabase-js ne sait pas faire.

D’autres pièges RLS du même projet : un exists de policy filtré par la RLS d’une autre table et permission denied for table, un GRANT et pas la RLS.

Lessons learned

sort en locale UTF-8 ignore la ponctuation. Deux noms dont l’un préfixe l’autre peuvent s’inverser, et ls comme le glob de bash suivent la même règle.
Sans table de suivi, l’ordre des migrations dépend du trieur. LC_ALL=C sort donne l’ordre par octet, le même que .sort() en Node.
Des tests qui trient autrement que la prod ne prouvent rien sur l’ordre. Un test qui refuse les paires ambiguës ferme la question pour de bon.
Rejouer une ancienne migration peut défaire une policy sans erreur. create policy réussit si le nom a disparu, create table échoue sans conséquence.
L’inventaire pg_policies comparé à une base neuve détecte la dérive. C’est le seul contrôle qui voit une policy permissive revenue.