Настройка сервера

В операционной системе сервера должны быть установлены локали с поддержкой русского языка: 

student$ locale -a|grep ru

ru_RU.koi8r
ru_RU.utf8
russian

Инициализация кластера баз данных в виртуальной машине курса выполнялась командой: 

postgres$ initdb --locale=ru_RU.utf8 --lc-messages=en_US.utf8

Если при запуске initdb не указывать параметры локализации, то будут использованы те, что установлены в ОС: 

student$ locale

LANG=en_US.UTF-8
LANGUAGE=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=ru_RU.UTF-8
LC_TIME=ru_RU.UTF-8
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=ru_RU.UTF-8
LC_MESSAGES=en_US.UTF8
LC_PAPER=ru_RU.UTF-8
LC_NAME=ru_RU.UTF-8
LC_ADDRESS=ru_RU.UTF-8
LC_TELEPHONE=ru_RU.UTF-8
LC_MEASUREMENT=ru_RU.UTF-8
LC_IDENTIFICATION=ru_RU.UTF-8
LC_ALL=

Среди категорий локали также присутствуют переменные, имеющие особое значение:

Несколько параметров PostgreSQL соответствуют одноименным категориям локали. Видно что их меньше чем в ОС: 

=> SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';

    name     |  setting   |  context  
-------------+------------+-----------
 lc_collate  | ru_RU.utf8 | internal
 lc_ctype    | ru_RU.utf8 | internal
 lc_messages | en_US.utf8 | superuser
 lc_monetary | ru_RU.utf8 | user
 lc_numeric  | ru_RU.utf8 | user
 lc_time     | ru_RU.utf8 | user
(6 rows)

Каждый обслуживающий процесс при запуске сбрасывает значение LC_ALL и устанавливает переменные окружения ОС для категорий локали в соответствии с конфигурационными параметрами "lc%".

При изменении параметра по ходу сеанса, обслуживающий процесс устанавливает это же значение для соответствующей переменной окружения. По значению столбца context понятно, что параметры lc_monetary, lc_numeric и lc_time может изменить любой пользователь, а lc_messages только суперпользователь. Параметры lc_ctype и lc_collate изменить невозможно.

Новые базы данных копируют параметры локализации у используемого шаблона: 

=> CREATE DATABASE admin_localization_utf8;

CREATE DATABASE

=> \list admin_localization_utf8|template1

                                        List of databases
          Name           |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-------------------------+----------+----------+------------+------------+-----------------------
 admin_localization_utf8 | postgres | UTF8     | ru_RU.utf8 | ru_RU.utf8 | 
 template1               | postgres | UTF8     | ru_RU.utf8 | ru_RU.utf8 | =c/postgres          +
                         |          |          |            |            | postgres=CTc/postgres
(2 rows)

Работа клиента и сервера в разных кодировках

Создадим базу данных с кодировкой KOI8R, а клиент по-прежнему будет использовать UTF8. Чтобы база данных успешно создалась, нужно учесть следующее: 

=> CREATE DATABASE admin_localization_koi8r
       ENCODING = 'KOI8R'
       LC_COLLATE = 'ru_RU.koi8r'
       LC_CTYPE = 'ru_RU.koi8r'
       TEMPLATE = template0;

CREATE DATABASE

=> \l admin_localization_koi8r

                                       List of databases
           Name           |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
--------------------------+----------+----------+-------------+-------------+-------------------
 admin_localization_koi8r | postgres | KOI8R    | ru_RU.koi8r | ru_RU.koi8r | 
(1 row)

Подключимся к admin_localization_koi8r и убедимся, что работает перекодировка символов. 

=> \c admin_localization_koi8r

You are now connected to database "admin_localization_koi8r" as user "postgres".

Кодировки клиента и сервера: 

=> SELECT name, setting, context FROM pg_settings WHERE name LIKE '%encoding';

      name       | setting | context  
-----------------+---------+----------
 client_encoding | KOI8R   | user
 server_encoding | KOI8R   | internal
(2 rows)

В интерактивном режиме параметр client_encoding принял бы значение кодировки клиента (переменной LC_CTYPE в ОС). Но при запуске из скрипта, как сейчас в демонстрации, он устанавливается в значение кодировки базы данных.

Явно зададим значение такое же как и у клиентской локали: 

=> \! locale | grep LC_CTYPE

LC_CTYPE="en_US.UTF-8"

=> SET client_encoding = 'UTF8';

SET

Теперь символы будут правильно конвертироваться из UTF8 в KOI8R и обратно: 

=> CREATE TABLE tab AS SELECT 'Привет, мир!'::text AS col;

SELECT 1

=> SELECT * FROM tab;

     col      
--------------
 Привет, мир!
(1 row)

Список доступных перекодировок для koi8: 

=> \dcS *koi8*

                              List of conversions
   Schema   |          Name          |    Source     |  Destination  | Default? 
------------+------------------------+---------------+---------------+----------
 pg_catalog | iso_8859_5_to_koi8_r   | ISO_8859_5    | KOI8R         | yes
 pg_catalog | koi8_r_to_iso_8859_5   | KOI8R         | ISO_8859_5    | yes
 pg_catalog | koi8_r_to_mic          | KOI8R         | MULE_INTERNAL | yes
 pg_catalog | koi8_r_to_utf8         | KOI8R         | UTF8          | yes
 pg_catalog | koi8_r_to_windows_1251 | KOI8R         | WIN1251       | yes
 pg_catalog | koi8_r_to_windows_866  | KOI8R         | WIN866        | yes
 pg_catalog | koi8_u_to_utf8         | KOI8U         | UTF8          | yes
 pg_catalog | mic_to_koi8_r          | MULE_INTERNAL | KOI8R         | yes
 pg_catalog | utf8_to_koi8_r         | UTF8          | KOI8R         | yes
 pg_catalog | utf8_to_koi8_u         | UTF8          | KOI8U         | yes
 pg_catalog | windows_1251_to_koi8_r | WIN1251       | KOI8R         | yes
 pg_catalog | windows_866_to_koi8_r  | WIN866        | KOI8R         | yes
(12 rows)


=> \c admin_localization_utf8

You are now connected to database "admin_localization_utf8" as user "postgres".

Правила сортировки

При инициализации кластера в системном каталоге каждой базы данных создаются специальные объекты - правила сортировки. Они создаются на основе информации об установленных в ОС локалях.

Правила сортировки для русского языка, полученные из ОС: 

=> \dOS+ ru*

                                  List of collations
   Schema   |    Name     |  Collate   |   Ctype    | Provider |     Description      
------------+-------------+------------+------------+----------+----------------------
 pg_catalog | ru-BY-x-icu | ru-BY      | ru-BY      | icu      | Russian (Belarus)
 pg_catalog | ru-KG-x-icu | ru-KG      | ru-KG      | icu      | Russian (Kyrgyzstan)
 pg_catalog | ru-KZ-x-icu | ru-KZ      | ru-KZ      | icu      | Russian (Kazakhstan)
 pg_catalog | ru-MD-x-icu | ru-MD      | ru-MD      | icu      | Russian (Moldova)
 pg_catalog | ru-RU-x-icu | ru-RU      | ru-RU      | icu      | Russian (Russia)
 pg_catalog | ru-UA-x-icu | ru-UA      | ru-UA      | icu      | Russian (Ukraine)
 pg_catalog | ru-x-icu    | ru         | ru         | icu      | Russian
 pg_catalog | ru_RU       | ru_RU.utf8 | ru_RU.utf8 | libc     | 
 pg_catalog | ru_RU.utf8  | ru_RU.utf8 | ru_RU.utf8 | libc     | 
(9 rows)

Правила сортировки для провайдера ICU создаются только в том случае, если сервер собран с параметром --with-icu: 

=> SELECT setting FROM pg_config() WHERE name = 'CONFIGURE';

                                   setting                                    
------------------------------------------------------------------------------
 '--prefix=/usr/local/pgsql' '--with-pgport=5432' '--enable-nls' '--with-icu'
(1 row)

Правила сортировки ICU

Сортировка для правила ru-x-icu отличается от используемой по умолчанию сортировки для правила ru_RU. Кириллица идет раньше латиницы: 

=> WITH t(c) AS (
    VALUES('а'),('б'),('в'),('a'),('b'),('c'),('1'),('2'),('3')
) 
SELECT string_agg(t.c,',' ORDER BY t.c) AS "default",
       string_agg(t.c,',' ORDER BY t.c COLLATE "ru-x-icu") AS "ru-x-icu"
  FROM t \gx

-[ RECORD 1 ]---------------
default  | 1,2,3,a,b,c,а,б,в
ru-x-icu | 1,2,3,а,б,в,a,b,c

Ключевое слово COLLATE после выражения (в ORDER BY) явно задает правило сортировки. По умолчанию используется правило с именем default, соответствующее параметрам локали базы данных.

Возможности библиотеки ICU позволяют по-разному настраивать сортировку отдельных групп символов.

Создадим новое правило сортировки, где латинские символы идут раньше символов кириллицы: 

=> CREATE COLLATION latn_cyrl 
    (provider = icu, locale = 'ru-RU-u-kr-latn-cyrl');

CREATE COLLATION

Еще одно, где в дополнении к предыдущему буквы идут раньше цифр: 

=> CREATE COLLATION latn_cyrl_digit 
    (provider = icu, locale = 'ru-RU-u-kr-latn-cyrl-digit');

CREATE COLLATION

Проверяем сортировку: 

=> WITH t(c) AS (
    VALUES('а'),('б'),('в'),('a'),('b'),('c'),('1'),('2'),('3')
) 
SELECT string_agg(t.c,',' ORDER BY t.c) AS "default",
       string_agg(t.c,',' ORDER BY t.c COLLATE "ru-x-icu") AS "ru-x-icu",
       string_agg(t.c,',' ORDER BY t.c COLLATE "latn_cyrl") AS "latn_cyrl",
       string_agg(t.c,',' ORDER BY t.c COLLATE "latn_cyrl_digit") AS "latn_cyrl_digit"
  FROM t \gx

-[ RECORD 1 ]---+------------------
default         | 1,2,3,a,b,c,а,б,в
ru-x-icu        | 1,2,3,а,б,в,a,b,c
latn_cyrl       | 1,2,3,a,b,c,а,б,в
latn_cyrl_digit | a,b,c,а,б,в,1,2,3

При создании правила сортировки ICU в системном каталоге сохраняется версия правила, переданная операционной системой. Если в ОС версия правила изменится, то каждый раз при использовании правила будет выдаваться предупреждение о том, что версии не совпадают.

Проверить соответствие версий можно запросом: 

=> SELECT c.collname, c.collversion AS version, pg_collation_actual_version(c.oid) AS actual_version 
   FROM pg_collation c 
   WHERE c.collname LIKE 'ru%' AND c.collprovider = 'i';

  collname   |   version   | actual_version 
-------------+-------------+----------------
 ru-BY-x-icu | 153.56.27.1 | 153.56.27.1
 ru-KG-x-icu | 153.56.27.1 | 153.56.27.1
 ru-KZ-x-icu | 153.56.27.1 | 153.56.27.1
 ru-MD-x-icu | 153.56.27.1 | 153.56.27.1
 ru-RU-x-icu | 153.56.27.1 | 153.56.27.1
 ru-UA-x-icu | 153.56.27.1 | 153.56.27.1
 ru-x-icu    | 153.56.27.1 | 153.56.27.1
(7 rows)

Изменение правила сортировки может привести к некорректной работе индексов. Если подобное произошло, рекомендуется пересоздать индексы с новой версией правила сортировки. Также следует проверить ограничения CHECK и табличные триггеры на использование измененных правил сортировки. После этого можно обновить версию в системном каталоге командой ALTER COLLATION ... REFRESH VERSION и предупреждения перестанут выдаваться.

Запрос для получения списка объектов, которые зависят от требующих обновления правил сортировки: 

=> SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
          pg_describe_object(classid, objid, objsubid) AS "Object"
   FROM pg_depend d JOIN pg_collation c
        ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
   WHERE c.collversion <> pg_collation_actual_version(c.oid)
   ORDER BY 1, 2;

 Collation | Object 
-----------+--------
(0 rows)

В итоге. К любому выражению, где сортируются или сравниваются текстовые данные, применяется некоторое правило сортировки. В общем случае правило сортировки выражения определяется следующим образом:

Даты и LC_TIME

Параметр конфигурации, отвечающий за соответствующую категорию локали: 

=> SET lc_time = 'ru_RU.UTF8';

SET

Для использования названий месяцев и дней недели на русском языке в форматной маске функции to_char используется префикс TM: 

=> SELECT to_char(current_date, 'TMDay, DD TMMonth YYYY');

           to_char           
-----------------------------
 Понедельник, 12 Август 2019
(1 row)

То же самое для американского английского: 

=> SET lc_time = 'en_US.UTF8';

SET

=> SELECT to_char(current_date, 'TMDay, DD TMMonth YYYY');

        to_char         
------------------------
 Monday, 12 August 2019
(1 row)

Функции to_date/to_char позволяют в явном виде указать формат даты и времени при вводе/выводе значений. На неявное преобразование даты в текст и наоборот влияет параметр datestyle. Вот несколько примеров: 

=> SET datestyle = 'Postgres'; SELECT current_setting('datestyle') AS datestyle, now();

SET
   datestyle   |                 now                 
---------------+-------------------------------------
 Postgres, DMY | Mon 12 Aug 17:18:00.743155 2019 MSK
(1 row)


=> SET datestyle = 'SQL, DMY'; SELECT current_setting('datestyle') AS datestyle, now();

SET
 datestyle |              now               
-----------+--------------------------------
 SQL, DMY  | 12/08/2019 17:18:00.794851 MSK
(1 row)


=> SET datestyle = 'SQL, MDY'; SELECT current_setting('datestyle') AS datestyle, now();

SET
 datestyle |              now               
-----------+--------------------------------
 SQL, MDY  | 08/12/2019 17:18:00.849333 MSK
(1 row)


=> RESET datestyle; SELECT current_setting('datestyle') AS datestyle, now();

RESET
 datestyle |              now              
-----------+-------------------------------
 ISO, DMY  | 2019-08-12 17:18:00.899257+03
(1 row)

Числа и LC_NUMERIC

Параметр конфигурации, отвечающий за соответствующую категорию локали: 

=> SET lc_numeric = 'ru_RU.UTF8';

SET

Разделители групп разрядов (G), а также целой и дробной части (D), принятые в России: 

=> SELECT to_char('12345'::numeric, '999G999D00');

   to_char   
-------------
   12 345,00
(1 row)

То же для США: 

=> SET lc_numeric = 'en_US.UTF8';

SET

=> SELECT to_char('12345'::numeric, '999G999D00');

   to_char   
-------------
   12,345.00
(1 row)

Денежные единицы и LC_MONETARY

Параметр конфигурации, отвечающий за соответствующую категорию локали: 

=> SET lc_monetary = 'ru_RU.UTF8';

SET

Денежный тип данных MONEY добавляет к сумме код валюты: 

=> SELECT '12345'::money;

     money     
---------------
 12 345.00 руб
(1 row)

Но нужно учитывать, что в таблице с таким типом можно хранить только одну валюту, и та будет меняться вместе с LC_MONETARY: 

=> SET lc_monetary = 'en_US.UTF8';

SET

=> SELECT '12345'::money;

   money    
------------
 $12,345.00
(1 row)

Сообщения сервера и клиента

Сообщения сервера можно выводить на разных языках. Предварительно нужно убедиться, что PostgreSQL собран с поддержкой NLS: 

=> SELECT setting FROM pg_config() WHERE name = 'CONFIGURE';

                                   setting                                    
------------------------------------------------------------------------------
 '--prefix=/usr/local/pgsql' '--with-pgport=5432' '--enable-nls' '--with-icu'
(1 row)

Список доступных языков и файлы сообщений здесь: 

=> SELECT setting FROM pg_config() WHERE name = 'LOCALEDIR';

            setting            
-------------------------------
 /usr/local/pgsql/share/locale
(1 row)


postgres$ ls -C /usr/local/pgsql/share/locale

cs  de	es  fr	he  it	ja  ko	nb  pl	pt_BR  ro  ru  sv  ta  tr  zh_CN  zh_TW

Для записи в журнал будем использовать ошибочную команду: 

=> select1;

ERROR:  syntax error at or near "select1"
LINE 1: select1;
        ^

postgres$ tail -n 2 /home/postgres/logfile

2019-08-12 17:18:01.383 MSK [15901] ERROR:  syntax error at or near "select1" at character 1
2019-08-12 17:18:01.383 MSK [15901] STATEMENT:  select1;

В журнал сервера записывается такое же сообщение об ошибке и сама ошибочная команда.

Для вывода сообщений на русском языке надо изменить параметр lc_messages.

Однако если при старте сервера установлена переменная LANGUAGE, то сообщения сервера будут выдаваться именно на этом языке, а параметр lc_messages будет игнорироваться: 

postgres$ echo $LANGUAGE

en_US

=> SET lc_messages TO 'ru_RU.UTF8';

SET

=> select1;

ERROR:  syntax error at or near "select1"
LINE 1: select1;
        ^

Перезапустим сервер, сбросив переменную среды LANGUAGE. 

=> \q

postgres$ pg_ctl -w -D /usr/local/pgsql/data stop

waiting for server to shut down.... done
server stopped

postgres$ unset LANGUAGE

postgres$ pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data start

waiting for server to start.... done
server started

student$ psql -d admin_localization_utf8

=> SET lc_messages TO 'ru_RU.UTF8';

SET

=> select1;

ОШИБКА:  ошибка синтаксиса (примерное положение: "select1")
LINE 1: select1;
        ^

postgres$ tail -n 2 /home/postgres/logfile

2019-08-12 17:18:01.901 MSK [16854] ОШИБКА:  ошибка синтаксиса (примерное положение: "select1") (символ 1)
2019-08-12 17:18:01.901 MSK [16854] ОПЕРАТОР:  select1;

Теперь сообщение выводится клиенту и в журнал на русском языке.

А почему осталась одна строка на английском языке?

Эту строку выводит клиент psql. Язык сообщений клиента определяется переменной ОС LC_MESSAGES: 

=> \! echo $LC_MESSAGES

en_US.UTF8

Для полной русификации установим русский язык и для сообщений клиента. Мы используем клиент (psql), поставляемый c сервером, поэтому можно не проверять, что клиент собран с поддержкой NLS. 

=> \q

student$ export LC_MESSAGES=ru_RU.UTF8

student$ psql 

=> SET lc_messages TO 'ru_RU.UTF8';

SET

=> select1;

ОШИБКА:  ошибка синтаксиса (примерное положение: "select1")
СТРОКА 1: select1;
          ^

Теперь все сообщения на русском языке.

Конец демонстрации.