キャストを追加してバージョン互換性を保つには
NTT オープンソースソフトウェアセンタ 板垣 貴裕
PostgreSQL 8.3 では型変換のチェックが厳密になるよう仕様変更されました。特に文字列型への暗黙の型変換が無くなったため、以前のバージョンで動作していたアプリケーションであっても 8.3 上ではエラーが発生するかもしれません。もちろん、一番確実な対応方法は、型を厳密に扱うようアプリケーションやSQLを書き換えることですが、アプリケーションの規模が大きかったり、そもそも書き換えができないケースもあるのではないかと思います。できれば、せっかく高速化された 8.3 へはバージョンアップしたいですし、互換性を保つ手軽な方法は何か無いでしょうか?
型エラーのメッセージ
型変換でエラーが発生している場合の典型的なエラーメッセージには、以下のようなものがあります。もし 8.3 へのバージョンアップ後にこれらのエラーが出力されるようならば、キャストの互換性が無くなったことが原因だと考えられます。HINT としても、「(SQLを書き換えて) キャストを追加する必要があるかもしれません」と提示されています。
比較演算子の不在：
ERROR: operator does not exist: integer = text
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
関数の引数の型の不一致：
ERROR: function substr(...) does not exist
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
文字列との暗黙のキャストの追加
PostgreSQL は元来さまざまなユーザ定義オブジェクトを追加できるように設計されてきました。キャスト (CAST, 型変換) もその1つです。ここでは、ユーザ定義のキャストを追加することで、互換性問題に対応してみます。
ではさっそく、整数型 (integer) から文字列型 (text) へのキャストを追加してみましょう。まず整数型から文字列型へ変換する関数を定義 (CREATE FUNCTION) し、続いてその関数を使うキャストを定義 (CREATE CAST) します。
-- 変換関数の定義
=# CREATE FUNCTION int4text(int4) RETURNS text AS
'SELECT textin(int4out($1))' LANGUAGE sql IMMUTABLE STRICT;
-- 暗黙的キャストの定義
=# CREATE CAST (int4 AS text)
WITH FUNCTION int4text(int4) AS IMPLICIT;
|| 演算子の追加対応
この際、文字列の連結 ( || 演算子) でエラーになる場合があります。
=# SELECT 'foo' || 77;
ERROR: operator is not unique: unknown || integer
HINT: Could not choose a best candidate operator.
You might need to add explicit type casts
この問題は、|| 演算子にも「text || int4」版を追加するとアプリケーションの修正が要りません。
=# CREATE FUNCTION textint4cat(text, int4) RETURNS text
AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
=# CREATE OPERATOR || (PROCEDURE = textint4cat,
LEFTARG = text, RIGHTARG = int4);
=# SELECT 'foo' || 77;
?column?
----------
foo77
(1 row)
動作の説明
SQL は文字列ですし、PostgreSQL に格納されたデータを取得する場合にも値は文字列として取得されます。そのため、すべての型には「文字列から」「文字列へ」の変換関数が用意されており、それぞれ「入力関数」「出力関数」と呼ばれます。上記の例では、integer型の出力関数 : int4out と、text型の入力関数 : textin を使って、以下のような流れで変換を行っています。
関数 int4text() の動作 : integer型 → (int4out) → 内部的な文字列 → (textin) → text型
入力関数と出力関数はシステムカタログ pg_type で管理されており、以下のようなSQLで取得できます。型を条件として検索する際、システム型の regtype を使うと記述がスッキリします。
=# SELECT oid, typname, typinput, typoutput
FROM pg_catalog.pg_type
WHERE oid IN ('text'::regtype, 'integer'::regtype);
oid | typname | typinput | typoutput
-----+---------+----------+-----------
23 | int4 | int4in | int4out
25 | text | textin | textout
(2 rows)
integer型以外のキャストの定義が必要になった場合でも、これを利用すれば必要な処理を調べられます。例えば、numeric型から文字列型へのキャストも追加してみましょう。
=# SELECT oid, typname, typinput, typoutput
FROM pg_catalog.pg_type WHERE oid = 'numeric'::regtype;
oid | typname | typinput | typoutput
------+---------+------------+-------------
1700 | numeric | numeric_in | numeric_out
(1 row)
=# CREATE FUNCTION numeric_text(numeric) RETURNS text AS
'SELECT textin(numeric_out($1))' LANGUAGE sql IMMUTABLE STRICT;
=# CREATE CAST (numeric AS text)
WITH FUNCTION numeric_text(numeric) AS IMPLICIT;
バージョン 8.4 の場合
ちなみに、バージョン 8.4 では、文字列型との型変換が簡単に定義できるよう構文が拡張されています。INOUT属性を指定することで、変換関数が不要になります。8.2 から一足飛びに 8.4 へバージョンアップする機会があれば、使ってみてください。
バージョン 8.4 は 2009年度の第1四半期にリリースされる予定です。この記事の執筆時点では、まだ開発が続いています。
-- 暗黙的キャストの定義 (PostgreSQL 8.4 向け) =# CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;
CREATE CASTをもっと詳しく
CREATE CAST について、もう少し詳しく見ていきましょう。構文については「PostgreSQL文書 CREATE CAST」を参照してください。CREATE CAST では、「変換処理」と「自動変換」の2つの属性を指定できるようになっています。
変換処理
変換処理は以下から選択します。
- WITH FUNCTION
- 別に用意した変換関数を使います。
- WITHOUT FUNCTION
- バイナリ表現に互換性のある型同士の変換に使います。あまり利用頻度は高くないと思いますが、oid型やxid型をint4型として扱う必要があった際に使った経験があります。
- WITH INOUT
- (バージョン8.4以降) 入出力関数を使用して、文字列を経由した変換を行います。
自動変換
自動変換の可否には以下の指定ができます。
- (指定無し)
- 暗黙的な型変換は行われません。明示的なキャスト (CAST構文や :: 演算子) を使った場合のみ変換されます。
- AS ASSIGNMENT
- INSERT や UPDATE での代入でのみ自動的に変換されます。
- AS IMPLICIT
- 多くの場合で自動的に変換されます。ただし、条件句で型変換が発生すると、インデックスを使えないなどのトラブルの原因になるので注意が必要です。
既定のキャストの再定義
登録されたキャストの一覧は、psql から \dC メタコマンドで参照できます。
=# \dC
List of casts
Source type | Target type | Function | Implicit?
-------------+-------------------+----------+---------------
"char" | character | bpchar | in assignment
"char" | character varying | text | in assignment
(以下略)
内部的な動作としては、CREATE CAST はキャストを管理するシステムカタログ pg_cast へ新しい行を追加 (INSERT) します。また、DROP CAST ではカタログから行を削除 (DELETE) します。上記の psql メタコマンドも実際にはシステムカタログを整形して表示しているだけですので、直接カタログを参照 (SELECT) しても、等価な情報を取得することができます。ただし、型や変換関数の名前の代わりに、それらの OID (オブジェクトID) が表示されます。
=# SELECT * FROM pg_catalog.pg_cast;
castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
20 | 21 | 714 | a | f
20 | 23 | 480 | a | f
(以下略)
PostgreSQL には残念ながら ALTER CAST 文が無いので、既存のキャストの定義を変更することはできません。しかし、pg_cast システムカタログを直接 UPDATE する (!) ことで、動作をカスタマイズすることも可能です。
それでは、整数 (integer) を真偽値 (boolean) として格納する際、自動的に変換されるようにしてみましょう。PostgreSQLではこの変換は明示的にキャストした場合のみ変換されますが、他DBMS製品では暗黙的に変換されるものも多いようです。まずは、該当する行を探します。
=# SELECT castsource, casttarget, castfunc, castcontext
FROM pg_catalog.pg_cast
WHERE castsource = 'integer'::regtype
AND casttarget = 'boolean'::regtype;
castsource | casttarget | castfunc | castcontext
------------+------------+----------+-------------
23 | 16 | 2557 | e
(1 row)
castcontext が e (explicit; 明示的キャストのみ) になっています。これを a (assignment; 代入時のみ) に変更します。e, a の他には i (implicit; 暗黙的キャスト) も使用できます。
=# UPDATE pg_catalog.pg_cast
SET castcontext = 'a'
WHERE castsource = 'integer'::regtype
AND casttarget = 'boolean'::regtype;
UPDATE 1
これで代入時に自動的にキャストされるようになりました。試しに boolean を含むテーブルを作成し、整数 1, 2, 0 を挿入してみます。挿入には成功し、0以外は真とみなされていることが分かります。
=# CREATE TABLE tbl (b boolean); CREATE TABLE =# INSERT INTO tbl VALUES(1), (2), (0); INSERT 0 3 =# SELECT * FROM tbl; b --- t t f (3 rows)
注意：システムカタログを直接操作する場合、操作を誤るとデータを壊してしまうかもしれません。重要なデータを含むデータベースに対しては、細心の注意を払って行ってください。カタログを操作する専用の DDLコマンド (CREATE, DROP, ALTER など) が別に用意されている場合には、できる限りそちらを使用してください。
最後に
今回は、暗黙のキャストを追加することで PostgreSQL の以前のバージョンとの互換性を維持しました。ただし、必ずしもアプリケーションが期待する変換結果になるとは限らないので、検索漏れの可能性に注意してください。たとえば、先頭に空白や0で埋めた右詰された数字を保持する文字列と整数 (例：000001 と 1) は、文字列として比較するか、整数として比較するかで結果が変化してしまいます。
さらに、キャストが発生することでインデックスが使用されない場合もあり、性能トラブルの原因にもなります。つまり、バージョン 8.2 以前では、「一応はエラー無く動作しているものの、実際にはインデックスが使われず性能が低い」というトラブルが発生する可能性がありました。8.3 ではエラー扱いになったことは、SQLのミスを早期に発見できるという観点では、むしろメリットと言えるかもしれません。
システムカタログの構成や使われ方を見ていると、PostgreSQL の内部的な動作が垣間見えます。システムカタログを調べることは、PostgreSQL がどのように動作しているのかを理解するための取っ掛かりになるかと思います。PostgreSQL について深く理解したい方は、ぜひ挑戦してみてください。
(2009年11月26日更新）