PostgreSQLのロケール(lc collate,lc ctype)の確認と変更

とあるシステムの移行検証中に、日本語のソート順が現行DB(Ver8.1.3)と新DB(Ver9.3.3)で異なる事象が発生。調査の結果、DBのロケールの違いにより、ソート順が異なっていたことが判明した。その時の顛末を書こうと思う。

まずは、状況を確認。

対象DBに接続して以下の確認用SQLを実行

ロケール確認用SQL 　

select name,setting,context from pg_settings where name like 'lc%';

[現行DBのロケール]

name | setting | context
-------------+-------------+-----------
lc_collate | ja_JP.UTF-8 | internal
lc_ctype | ja_JP.UTF-8 | internal
lc_messages | ja_JP.UTF-8 | superuser
lc_monetary | ja_JP.UTF-8 | user
lc_numeric | ja_JP.UTF-8 | user
lc_time | ja_JP.UTF-8 | user

[新DBのロケール]

name | setting | context
-------------+-------------+-----------
lc_collate | C | internal
lc_ctype | C | internal
lc_messages | C | superuser
lc_monetary | C | user
lc_numeric | C | user
lc_time | C | user

ソート順に影響するロケールは「lc_collate」と「lc_ctype」でこの二つはDB作成後に変更は出来ないため、変更するためにはDBを再作成する必要がある。最近のバージョンではDBの最再作成で変更することは可能であるが、古いバージョンの場合は、initdbからやり直さなければならなかったので、一安心した。
但し新バージョンでもDBを再作成せずに回避する方法はある。その回避方法は後で説明する。

とりあえぅ修正しないといけないので、以下の手順で修正した。

DBをpg_dumpallでエクスポートしてからロケールを修正してDBを作成後、データをインポートする。

１．DBの定義をエクスポート

pg_dumpall -c -s -f db_dump_all_s.dmp

２．DBのデータをエクスポート

　pg_dumpall -a -v -f db_dump_all_a.dmp

３．１でエクスポートしたDBの定義を書き換える

CREATE DATABA文を書き換えて保存する。

　CREATE DATABASE db WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8';

　↓

　CREATE DATABASE db WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'ja_JP.UTF-8' LC_CTYPE = 'ja_JP.UTF-8';

４．３で書き換えたDBの定義を実行する

　psql -f db_dump_all_s.dmp postgres -L db_dump_all_s.log postgres

５．２でエクスポートしたDBデータをインポートする。

　psql -f db_dump_all_a.dmp postgres -L db_dump_all_a.log postgres

以上

ここで問題が発生、pg_dumpallではキャストの定義がエクスポートされない
CREATE CASTをして独自のキャストを定義していたのだが、そのキャストがエクスポートされていなかった。
少しググると、この問題はちらほらネットでも出てきていて、バグのような気もする。
気を取り直して、CREATE CASTでキャストを追加する。

■後処理

以上の処理で「lc_collate」と「lc_ctype」は変更され、ソート順序は指定した設定値に基づきソートされる。
それ以外についてはpostgresql.confで設定を行う。
もし、postgresql.confを変更できない場合は、ロールに設定をする事も可能である。

　alter role ROLE01 set lc_monetary = 'ja_JP.UTF-8';
　alter role ROLE01 set lc_numeric = 'ja_JP.UTF-8';
　alter role ROLE01 set lc_time = 'ja_JP.UTF-8';

設定変更したユーザでログインした場合に、設定値が反映される。

＜それぞれの設定の説明＞
lc_messagesは出力メッセージのロケール
lc_monetary 通貨の書式でmoney型を使用している場合に影響する
lc_numeric 数値の書式で、日本語と英語は同じ、ヨーロッパ圏で異なる
lc_time 日付の書式 TMMonth や TMDay を指定した場合の月名や曜日名に影響

DBを再作成せずに対応する方法だが、以下の２つの方法で対応可能である。

回避策1　SQLを修正してカラムにロケールを指定する

　こうなっているSQLを⇒　ORDER BY title DESC
　
　このように変更する⇒　ORDER BY title COLLATE “ja_JP.utf8” DESC

回避策2　テーブルのカラムにロケールを個別に指定する

　ALTER TABLE items ALTER COLUMN title TYPE VARCHAR COLLATE "ja_JP.utf8";

■参考
Herokuのpostgresで日本語カラムのソートが正しくできない　http://qa.atmarkit.co.jp/q/2205

ロケール(国際化と地域化)　http://lets.postgresql.jp/documents/technical/text-processing/2

pg_dump not including custom CAST based on table types?http://www.postgresql.org/message-id/j7k2cu$2u5e$1@news.hub.org

【おすすめ参考書】

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 Software Design plus

posted with amazlet at 17.07.04

技術評論社 (2014-09-17)
売り上げランキング: 34,018

Amazon.co.jpで詳細を見る

 

photo by Brian Snelson