Коллации в PostgreSQL: glibc 2.28 переставила строки, индекс стал неверным без ошибки, а ICU и C ведут себя иначе

После обновления ОС база запускается без ошибок, запросы отвечают, но WHERE name = 'a-a' не находит существующую строку, а в UNIQUE-столбец ложится дубль. B-tree хранит ключи в порядке, который задавала strcoll библиотеки в момент CREATE INDEX (как устроены страницы и поиск). Если новая версия glibc сравнивает строки иначе, индекс остаётся физически целым, но лжёт: поиск идёт по порядку, которого больше нет. Ниже это воспроизведено на одном каталоге данных под двумя версиями glibc, затем показано, что именно об этом знает PostgreSQL 18 и чем отличаются коллации ICU и C.

DBA / 01koddo

Индекс одного порядка, прочитанный в другом

Опыт 1: том из glibc 2.24 в контейнер с glibc 2.28

Нужна одна мажорная версия PostgreSQL под двумя версиями glibc. На Docker Hub образы на Debian stretch есть только для 9.6, 10 и 11 (список тегов через API Docker Hub на 28 октября 2026), поэтому взят PostgreSQL 11.13 в postgres:11.13-stretch и postgres:11.13-buster. Версия 11 нужна только ради этой пары образов; механизм от версии PostgreSQL не зависит. Первый контейнер создаёт данные, второй читает тот же именованный том:

docker volume create dba-coll-data
docker run -d --name dba-coll-old --memory 1g -e POSTGRES_PASSWORD=pg \
  -v dba-coll-data:/var/lib/postgresql/data postgres:11.13-stretch
docker exec dba-coll-old ldd --version | head -1

Вывод ldd: ldd (Debian GLIBC 2.24-11+deb9u4) 2.24. Данные: 14 строк с дефисами, пробелами и регистром плюс 5 000 сгенерированных, индекс по name и таблица codes с первичным ключом по тексту (114 строк). Расширение amcheck ставится сразу, пока порядок ещё старый:

CREATE EXTENSION amcheck;
CREATE TABLE names (id serial PRIMARY KEY, name text NOT NULL);
INSERT INTO names (name) VALUES ('a-a'), ('a a'), ('a1'), ('a-b'), ('a b'), ('A-a'), ('1-1'), ('1 1'), ('11'), ('B-b'), ('b b'), ('ab'), ('Ab'), ('a_a');
INSERT INTO names (name)
SELECT (ARRAY['ab','Ab','a','B','cd','x'])[1 + g % 6]
    || (ARRAY['-',' ','','_','.'])[1 + g / 6 % 5]
    || (ARRAY['a','1','B','z','Z'])[1 + g / 30 % 5] || g
FROM generate_series(1, 5000) g;
CREATE INDEX names_name_idx ON names (name);
CREATE TABLE codes (code text PRIMARY KEY);
INSERT INTO codes SELECT name FROM names WHERE id <= 14 OR id % 50 = 0;
SELECT string_agg(name, ' | ' ORDER BY name) AS first_14 FROM names WHERE id <= 14;

вывод
                                   first_14                                    
-------------------------------------------------------------------------------
 11 | 1 1 | 1-1 | a1 | a a | a_a | a-a | A-a | ab | a b | a-b | Ab | b b | B-b

Контейнер останавливается штатно (docker stop dba-coll-old), и тот же том подключается к образу на buster:

docker run -d --name dba-coll-new --memory 1g -e POSTGRES_PASSWORD=pg \
  -v dba-coll-data:/var/lib/postgresql/data postgres:11.13-buster
docker exec dba-coll-new ldd --version | head -1

Вывод ldd: ldd (Debian GLIBC 2.28-10) 2.28. Те же 14 строк теперь сортируются так:

вывод
                                   first_14                                    
-------------------------------------------------------------------------------
 1 1 | 1-1 | 11 | a1 | a a | a-a | a_a | A-a | a b | a-b | ab | Ab | b b | B-b

Изменились три места: 11 ушла после 1 1 и 1-1, a-a и a_a поменялись, а ab оказалась после a b и a-b. Причина по вики PostgreSQL: в glibc 2.28, вышедшей 2018-08-01, обновили данные локалей по международным стандартам. Теперь один и тот же запрос SELECT string_agg(name, ' | ') FROM (SELECT name FROM names ORDER BY name LIMIT 8) s двумя путями: с enable_seqscan = off план — Index Only Scan using names_name_idx (старый порядок), с enable_indexscan, enable_indexonlyscan и enable_bitmapscan в off — Sort над Seq Scan (новый порядок):

вывод
 11 | 1 1 | 1-1 | a1 | a-11082 | a 11088 | a11094 | a_11100
 1 1 | 1-1 | 11 | a1 | a-11082 | a 11088 | a11094 | a_11100

Один запрос возвращает разный порядок в зависимости от плана. Дальше поиск каждой из 5 014 строк по индексу, поиск одной строки двумя путями и проверка:

SELECT count(*) AS total, count(*) FILTER (WHERE EXISTS (SELECT 1 FROM names m WHERE m.name = n.name)) AS found_by_index FROM names n;
SELECT count(*) AS a_a_by_index FROM names WHERE name = 'a-a';
SELECT count(*) AS a_a_by_seqscan FROM names WHERE name = 'a-a';
SELECT bt_index_check('names_name_idx');
SELECT bt_index_parent_check('names_name_idx');

вывод
 total | found_by_index 
-------+----------------
  5014 |           5008

 a_a_by_index 
--------------
            0

 a_a_by_seqscan 
----------------
              1

ERROR:  item order invariant violated for index "names_name_idx"
DETAIL:  Lower index tid=(1,2) (points to heap tid=(0,9)) higher index tid=(1,3) (points to heap tid=(0,8)) page lsn=0/1739AD8.
ERROR:  item order invariant violated for index "names_name_idx"
DETAIL:  Lower index tid=(1,2) (points to heap tid=(0,9)) higher index tid=(1,3) (points to heap tid=(0,8)) page lsn=0/1739AD8.

Индекс не нашёл 6 строк из 5 014, а a-a не нашёл совсем, хотя в таблице она есть. amcheck называет первую пару: запись, указывающая на кортеж (0,9), стоит выше записи на (0,8). Это id 9 и 8, то есть 11 и 1 1 из схемы. Ошибки нет нигде, кроме amcheck: запросы выполняются. Теперь уникальный ключ: в codes уже лежит a-a.

INSERT INTO codes VALUES ('a-a');
SELECT code, count(*) FROM codes GROUP BY code HAVING count(*) > 1;
REINDEX INDEX codes_pkey;

вывод
 code | count 
------+-------
 a-a  |     2

ERROR:  could not create unique index "codes_pkey"
DETAIL:  Key (code)=(a-a) is duplicated.

INSERT прошёл без ошибки: поиск дубликата в старом порядке не нашёл a-a. Починка двухшаговая: удалить лишнюю строку и пересоздать оба индекса (DELETE ... WHERE ctid = ..., затем REINDEX INDEX codes_pkey и REINDEX INDEX names_name_idx). После неё bt_index_parent_check обоих индексов возвращает пустую строку, то есть ошибок нет. REINDEX уникального индекса падает, пока дубликат не удалён, поэтому дубликаты приходится разбирать руками.

Опыт 2: что знает об этом PostgreSQL 18

В postgres:18 (18.6, glibc 2.41) версия коллации записана в каталоге, и ядро сравнивает её с версией библиотеки:

SELECT datname, datlocprovider AS prov, datcollate, datcollversion FROM pg_database ORDER BY 1;
SELECT collname, collprovider AS prov, collversion, pg_collation_actual_version(oid) AS actual
FROM pg_collation WHERE collname IN ('en_US', 'C', 'en-US-x-icu', 'pg_c_utf8') ORDER BY 1;

вывод
  datname  | prov | datcollate | datcollversion 
-----------+------+------------+----------------
 postgres  | c    | en_US.utf8 | 2.41
 template0 | c    | en_US.utf8 | 
 template1 | c    | en_US.utf8 | 2.41

  collname   | prov | collversion | actual  
-------------+------+-------------+---------
 C           | c    |             | 
 en-US-x-icu | i    | 153.128     | 153.128
 en_US       | c    | 2.41        | 2.41
 pg_c_utf8   | b    | 1           | 1

У libc-коллации версия равна версии glibc, у ICU это версия данных коллации, у встроенной pg_c_utf8 она равна 1, а у C версии нет вовсе. Реальную смену библиотеки в одном контейнере не получить, поэтому она смоделирована: в базе app с индексами по умолчанию и по COLLATE "en_US" записанная версия заменена на 2.17 прямым UPDATE в pg_database и pg_collation (это правка каталога, а не реальный апгрейд). Новое подключение к такой базе:

вывод
WARNING:  database "app" has a collation version mismatch
DETAIL:  The database was created using collation version 2.17, but the operating system provides version 2.41.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE app REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

Предупреждение выдаётся при каждом подключении и не мешает работе: ошибки нет, запросы идут. Проверка стоит в CheckMyDatabase (postinit.c); запись версии базы появилась в PostgreSQL 15 (примечания к выпуску). Для отдельной коллации предупреждение стоит там, где строится структура pg_locale_t:

		if (strcmp(actual_versionstr, collversionstr) != 0)
			ereport(WARNING,
					(errmsg("collation \"%s\" has version mismatch",
							NameStr(collform->collname)),
					 errdetail("The collation in the database was created using version %s, "
							   "but the operating system provides version %s.",
							   collversionstr, actual_versionstr),

Это pg_newlocale_from_collation → create_pg_locale в pg_locale.c, строки 1131–1137. Предупреждение появляется, когда запрос сравнивает строки (WHERE name COLLATE "en_US" < 'n-5'), и не появляется, когда ORDER BY целиком идёт по индексу: план Index Only Scan using t_name_en_idx (проверен EXPLAIN) не вызывает сравнение. То есть запрос, который просто читает индекс по порядку, ничего не сообщает. Дальше выполнены SELECT count(*) FROM t WHERE name COLLATE "en_US" < 'n-5', REINDEX INDEX t_name_en_idx, ALTER COLLATION "en_US" REFRESH VERSION (дважды), REINDEX DATABASE app и ALTER DATABASE app REFRESH COLLATION VERSION. Вывод (повторы предупреждения о базе заменены на ...):

вывод
...
WARNING:  collation "en_US" has version mismatch
DETAIL:  The collation in the database was created using version 2.17, but the operating system provides version 2.41.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."en_US" REFRESH VERSION, or build PostgreSQL with the right library version.
 count 
-------
   445
...
NOTICE:  changing version from 2.17 to 2.41
NOTICE:  version has not changed
...
NOTICE:  changing version from 2.17 to 2.41

Порядок починки по документации такой: REINDEX всех объектов, которые зависят от коллации, затем ALTER COLLATION ... REFRESH VERSION или ALTER DATABASE ... REFRESH COLLATION VERSION. Команда только записывает текущую версию и не проверяет, что индексы перестроены: документация прямо говорит, что для glibc версия библиотеки служит заменителем версии коллации, и отмечает, что подход неточен.

Опыт 3: ICU и коллация C на одних данных

Десять строк в трёх коллациях (COLLATE в ORDER BY):

вывод
"C":          1 1 | 11 | A-a | Ab | B-b | a a | a-a | a1 | ab | b b
"en_US":      1 1 | 11 | a1 | a a | a-a | A-a | ab | Ab | b b | B-b
"en-US-x-icu": 1 1 | 11 | a a | a-a | A-a | a1 | ab | Ab | b b | B-b

C сравнивает байты: все заглавные раньше всех строчных, пробел раньше дефиса. en_US из glibc и ICU различаются уже в этих десяти строках (a1 стоит до a a у glibc и после A-a у ICU), то есть переход с одной на другую тоже меняет порядок и требует REINDEX. Время сортировки 1 млн строк (таблица big, 50 МБ, 1 000 000 различных строк из md5 с разделителем и суффиксом, work_mem = 256MB, max_parallel_workers_per_gather = 0, три запуска EXPLAIN (ANALYZE) на коллацию, все сортировки quicksort в памяти):

Сортировка en_US в 5,9 раза медленнее C и в 3,8 раза медленнее ICU. Один из механизмов виден в коде. В varstr_cmp для C сравнение сводится к memcmp, для остальных коллаций это memcmp на равенство и затем pg_strncoll. Кроме того, abbreviated keys (сокращённый ключ в Datum, по которому сортировка сравнивает без обращения к строке) для libc отключены категорически:

#ifdef TRUST_STRXFRM
	.strxfrm_is_safe = true,
#else
	.strxfrm_is_safe = false,
#endif

Это строки 102–106 pg_locale_libc.c; комментарий над ними объясняет, что strcoll() и strxfrm() в glibc для многих локалей возвращают несогласованные результаты. В varstr_sortsupport (varlena.c) это превращается в

		if (!pg_strxfrm_enabled(locale))
			abbreviate = false;

У ICU в pg_locale_icu.c стоит .strxfrm_is_safe = true (строка 125), поэтому сокращённые ключи для ICU возможны. Доля сокращённых ключей в разнице 2 226 и 584 мс в опыте отдельно не измерялась: сравнивались только коллации целиком, а у них различаются и библиотека, и алгоритм.

Что из этого следует

• Обновление ОС или смена образа с другой glibc ломает текстовые индексы молча. Опыт 1 показал промах поиска и дубль в UNIQUE без единой ошибки. Порядок действий: до открытия записи прогнать bt_index_check по индексам на текстовых столбцах (amcheck ставится как расширение), затем REINDEX (для живой базы — REINDEX CONCURRENTLY); у уникальных индексов перед этим искать дубликаты.
• Тот же механизм у реплики на хосте с другой glibc. Физическая реплика читает те же страницы индекса, что и первичный сервер, как том в опыте 1; библиотека проверяется на обоих хостах (ldd --version) до первого переключения.
• Предупреждение о версии — только сигнал. В PostgreSQL 15 и новее оно есть у базы, у коллации оно срабатывает на сравнении строк, а запрос, который только читает индекс по порядку, молчит (опыт 2). Строку collation version mismatch стоит искать в логе и заводить на неё алерт.
• REFRESH VERSION ничего не чинит. Он только обновляет запись в каталоге; сначала REINDEX, потом REFRESH, иначе предупреждение пропадёт, а испорченный индекс останется.
• Выбор коллации — компромисс. C и pg_c_utf8 сортируют по байтам, дают порядок, независимый от библиотеки, и в опыте 3 быстрее glibc в 5,9 раза; порядок при этом не языковой (заглавные раньше строчных). ICU даёт языковой порядок с тем же collversion (153.128 в опыте): при обновлении библиотеки ICU действует тот же порядок REINDEX, затем REFRESH, так что смена провайдера меняет причину проблемы, а не снимает её. Выбирают при создании базы: смена коллации потом означает REINDEX всего.

Чек-лист

1. Узнай, под какой коллацией живут индексы: SELECT datname, datcollate, datcollversion FROM pg_database, SELECT collname, collversion, pg_collation_actual_version(oid) FROM pg_collation.
2. Перед обновлением ОС, образа или переездом на другой хост сравни ldd --version на обеих сторонах и сохрани результат bt_index_check по текстовым индексам.
3. После перехода прогони amcheck ещё раз; при ошибке item order invariant violated — REINDEX, у уникальных ключей сначала разбери дубликаты.
4. Только после REINDEX выполни ALTER COLLATION ... REFRESH VERSION и ALTER DATABASE ... REFRESH COLLATION VERSION.
5. Убери контейнеры опыта: docker rm -f -v dba-coll-old dba-coll-new и docker volume rm dba-coll-data.