Лечение проблем LC_COLLATE при использовании UTF-8 в PostgreSQL под FreeBSD

lexa - 22/Фев/2008 13:39

Как я уже писал, при использовании UTF-8 в PostgreSQL под FreeBSD результат больно бъется: сортировка русских букв неправильная (касается это только буквы ё). Причина - в кривой locale под FreeBSD (и Mac OS X).

Естественно, на эти грабли уже много раз наступали и для PostgreSQL 8.1 существуют патчи имени Palle Girgensohn, позволяющие использовать IBM-овскую библиотеку ICU в которой все сделано правильно для безумного количества языков.

Если наложить патчи Palle на PostgreSQL 8.3, то они даже наложатся (с точностью до того, что часть файлов переехала в другие каталоги). После минимального редактирования все соберется и будет запускаться. Правда результат получается неработающий: преобразования к верхнему-нижнему регистрам в PostgreSQL переделали, в результате все компилируется и уверенно не работает (падает).

С другой стороны, преобразования upper/lower для моей ситуации и не очень нужны: LC_CTYPE для UTF-8 в FreeBSD сделан правильно, проблемы исключительно с сортировками (LC_COLLATE). Поэтому от патчей преобразования регистра можно и отказаться. В результате получилось работоспособное (с виду) решение, которое можно применять.

В случае FreeBSD лично я предпочитаю сборку из ports, поэтому процедура такая:

  • Установить библиотеку ICU (ports/devel/icu).
  • Скачать pg830-patch-icu-varlena.gz, распаковать, поместить в ports/databases/postgresql83-server/files
  • запустить make && make install

Инициализировать базу данных нужно командой

initdb -E UTF8 --locale=ru_RU.UTF-8
Указание locale обязательно, без нее счастья не будет.

Мелкие замечания:

  • Патчится configure.in, который потом на самом деле не используется. Так было у Palle, мне не жалко.
  • Я - не port maintainer и делать все правильно (с выбором этой возможности при первом make) мне было лень. Положили патч в files - будет все с ICU, не положили - не будет.
  • Наличие установленной ports/devel/icu не проверяется: скрипт configure не менялся. При этом #define WITH_ICU 1 и нужные библиотеки просто вписаны в соответствующие include/makefiles

По всей видимости, патчи (возможно, с небольшими изменениями) подойдут и для PostgreSQL 8.2 (а более старые версии на сегодня неинтересны).

Comments

maxcom (not verified) - 22/Фев/2008 10:30

tsearch то после этого пашет?

lexa - 22/Фев/2008 10:48

Я не проверял, за полной ненадобностью.

Но патчится только сравнение строк (начинает правильно работать collate), должен работать.

Gaver (not verified) - 25/Фев/2008 02:49

А если с кодировкой все в порядке, это к чему? Или глюки не обязательно должны быть?

lexa - 25/Фев/2008 08:43

Оно не все в порядке :)
Буква ё куда сортируется ?

alexz (not verified) - 26/Фев/2008 22:42

А почему бы просто не сгенерить LC_COLLATE для ru_RU.UTF-8 c нужным порядком сортировки?
Я посмотрел, например, как сортирует access и msde, и сделал так же.
только чуть лучше :) (Прописные буквы всегда меньше строчных, т.е. получим типа "А,а,Б,б,В,в...", а не "А,а,б,Б,в,В...")
Вроде работает...

lexa - 26/Фев/2008 22:44

А FreeBSD-шный strcoll не умеет многобайтных кодировок, ему от этого collate не полегчает.

alexz (not verified) - 27/Фев/2008 10:56

Не знаю, кто там что умеет, а что не умеет,
но man colldef утверждает
An order list element can be represented in any one of the following
ways:
o The symbol itself (for example, a for the lower-case letter a).
...
o The symbol chain (for example: abc, c, \xf1b\xf2)
...
Т.е. элементом по которому выполняется сортировка может быть как одиночный
символ, так и _последовательность_символов_. А это как раз подходит в случае
с utf-8. Кстати, такая фича по-моему используется в испанском LC_COLLATE
(сдвоенные LL, RR вроде как у них считаются за один символ).
Чтобы не быть голословным:
Welcome to psql 8.3.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# \l
List of databases
Name | Owner | Encoding
-----------+-------+----------
postgres | pgsql | UTF8
template0 | pgsql | UTF8
template1 | pgsql | UTF8
test | pgsql | UTF8
(4 rows)

test=# \encoding
KOI8
test=# create table t (s varchar(50));
CREATE TABLE
test=# insert into t values('МжК');
INSERT 0 1
test=# insert into t values('мЁд');
INSERT 0 1
test=# insert into t values('МеДвЕдЬ');
INSERT 0 1
test=# insert into t values('масло');
INSERT 0 1
test=# insert into t values('мясо');
INSERT 0 1
test=# select * from t order by 1;
s
---------
масло
МеДвЕдЬ
мЁд
МжК
мясо
(5 rows)

test=# \encoding utf8
test=# select * from t order by 1;
s
---------
п+п│я│п│п+
п°п│п│п│пп│п-
п+п│п│
п°пTп
п+я│я│п+
(5 rows)

Вроде корректно сортирует :)

lexa - 27/Фев/2008 11:11

initdb -E UTF8 --locale=ru_RU.UTF-8 --lc-messages=C

create table aa (bb varchar(10));
... inserts...
lexa=# select bb from aa order by 1;
bb
----
аа
ее
яя
ёё

Дайте я угадаю, у вас Линукс ?
Да и с испанским в FreeBSD тоже ничего хорошего:

lexa@home-gw:~# ls -l /usr/share/locale/es_ES.UTF-8/LC_COLLATE
lrwxrwxr-x 1 root wheel 28 23 18:34 /usr/share/locale/es_ES.UTF-8/LC_COLLATE -> ../la_LN.US-ASCII/LC_COLLATE

alexz (not verified) - 27/Фев/2008 11:29

initdb -D /data/db/postgresql/data -E UTF-8 --locale=ru_RU.UTF-8 --lc-collate=ru_RU.UTF-8.SQL

ru_RU.UTF-8.SQL просто создана для того, чтобы не портить ru_RU.UTF-8, и содержит сделанный мной LC_COLLATE
С буквой "ё" у меня в тесте, как видите, все нормально.
Нет, не угадали :)
gw# uname -a
FreeBSD gw.<скрыто>.ru 6.3-RELEASE FreeBSD 6.3-RELEASE #0: Sat Feb 2 19:27:45 MSK 2008 alex@gw.<скрыто>.ru:/usr/obj/usr/src/sys/KERNEL i386

Я имел в виду не испанский utf-8, а обычный es_ES.ISO8859-1
Просто похоже на то, что в испанском, грубо говоря, есть символы однобайтовые, а есть двухбайтовые (LL, RR, которые обозначают один звук), что в целом похоже на ситуацию с utf-8. См. /usr/src/share/colldef/es_ES.ISO8859-1.src.
При этом, предполагаю, у них сортировка работает корректно.

lexa - 27/Фев/2008 11:50

Ну я, честно говоря, прочитал вот этот вот комментарий в исходнике
wcscoll.c
* Placeholder implementation of wcscoll(). Attempts to use the single-byte * collation ordering where possible, and falls back on wcscmp() in locales * with extended character sets.

Потом пошел, почитал wcscmp.c, выругался, дальше разбираться не стал.

А можно попросить исходник ru.utf-8.sql для опытов ? Можно, если хотите, у меня на ftp его положить для счастья грядущих поколений.

Идеально было бы, конечно, продавить это через FreeBSD team, хотя у меня особого желания заниматься этим нет.

alexz (not verified) - 27/Фев/2008 12:21

Конечно можно. Куда послать/выложить?

lexa - 27/Фев/2008 12:57

Мой email общеизвестен: lexa@lexa.ru
А дальше - в зависимости от вашего желания, если хочется общедоступности, а выложить надолго некуда - можно выложить у меня (на ftp или в blog.lexa.ru/files/). Как удобнее...