オブジェクト識別子データ型を活用する
笠原 辰仁
本記事は2013年のPostgreSQL Advent Calendar の 12/9 の記事です。PostgreSQLのオブジェクト識別子(OID)を上手く活用する方法を紹介します。本記事の内容はマニュアルでも詳しく書かれているので参考にしてください。
オブジェクト識別子
オブジェクト識別子とは、PostgreSQLがDB内部に格納している様々なオブジェクト(テーブルやインデックス、関数、演算子、データ型定義などなど)を一意に識別するためのIDです。PostgreSQLをよくお使いの方は、OIDという名称の方がなじみがあるかもしれません。以降はOIDと呼びます。
OIDは、PostgreSQLインスタンスで一意の値を持つ32bitの整数です。ユーザやシステム(PostgreSQL)がオブジェクトを作る際にこのOIDが発行され割り当てられます。システムテーブルや組み込みの関数、演算子、データ型などには最初から規定のOIDが割り当てられています。(ハードコーディングされています)
OIDは、あまりユーザが意識して使うものではありません。しかし、PostgreSQLがOIDを使って内部オブジェクトの管理をしているため、ユーザがPostgreSQLのオブジェクトのメタ情報などをDBから引き出したい場合に使うことがままあります。
OIDの参照
OIDは主にPostgreSQLのシステムテーブル(システムカタログ)で参照することができます。ただし、OIDは隠し列となっており、psqlの¥dコマンドなどでシステムテーブルの定義を見てもOIDは見つかりません。確認する際はSELECT の参照列に oid を記載します。
-- 例えばスキーマの一覧を見ることが出来るpg_namespaceにはoidという列は無い
postgres=# \d pg_namespace
Table "pg_catalog.pg_namespace"
Column | Type | Modifiers
----------+-----------+-----------
nspname | name | not null
nspowner | oid | not null
nspacl | aclitem[] |
Indexes:
"pg_namespace_nspname_index" UNIQUE, btree (nspname)
"pg_namespace_oid_index" UNIQUE, btree (oid)
-- SELECT でoidを明示的に指定すると見える
postgres=# SELECT oid, * FROM pg_namespace;
oid | nspname | nspowner | nspacl
-------+--------------------+----------+-------------------------------------
11 | pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
2200 | public | 10 | {postgres=UC/postgres,=UC/postgres}
11727 | information_schema | 10 | {postgres=UC/postgres,=U/postgres}
このようなoidを隠し列で持っている代表的なシステムカタログは、pg_database(DBのメタ情報)、pg_class(テーブルやインデックス、ビューのメタ情報)、pg_type(データ型のメタ情報)、pg_proc(関数のメタ情報)、pg_operator(演算子のメタ情報)などです。この他にもいくつかあります。
また、上記の例でもoid型を使っている列(nspowner)がpg_namespace中に見えています。nspownerはスキーマのオーナのユーザを示すOIDです。このように、オブジェクト名などのほとんどは、システムカタログ内ではOIDで表現されています。
オブジェジェクト識別子データ型の活用
では、本題です。PostgreSQLのシステムカタログから情報を引き出す際は、OIDを意識する必要がありそうなのは分かりました。ではどのようにOIDを使うのでしょうか？よくある使い方としては、メタ情報を知る際の結合キーとすることが挙げられます。
例えば、"myschema"スキーマにある、"test"テーブルの、"c2"列のデータ型を取得したいとしましょう。この場合、以下のようなSQLを発行することで可能です。(一例です)
SELECT a.attname, t.typname FROM pg_class c, pg_namespace n, pg_attribute a, pg_type t WHERE c.relnamespace = n.oid AND c.oid = a.attrelid AND a.atttypid = t.oid AND NOT a.attisdropped AND n.nspname = 'myschema' AND c.relname = 'test' AND a.attname = 'c2'; attname | typname ---------+--------- c2 | text
上記の例では、pg_classとpg_namespaceを結びつけて、目的のスキーマの目的のテーブルOIDを絞り込みます。そして列情報を格納しているpg_attributeでattrelid(その列を保持するテーブルのOID)とそのOIDを合致させつつ、列名と削除されていないかのフラグ(attisdropped)をもとに目的の列のメタ情報を絞り込み、その列のデータ型をpg_typeから引っ張っています。知りたいことは単純なのですが、意外と多くのテーブル結合があります。もう少し簡易化できないのでしょうか？
こんな時に役立つのがオブジェクト識別子データ型です。PostgreSQLには、ユーザが意識する情報(具体的なテーブル名やデータ型など)とOIDの橋渡しをしてくれるデータ型があります。これがオブジェジクト識別子データ型です。一覧はマニュアルにあります。よく使うデータ型はregclass, regtype, regprocedureでしょうか。例えばregclassへ(スキーマ名.)テーブル名を渡す(キャストする)と、OIDへ変換してくれます。逆にOIDを渡すと(スキーマ名.)テーブル名に変換してくれます。以下の例を示します。
-- publicスキーマのtestテーブルをOIDに変換 -- (最終的にoidへキャストしているのは人が読める数値としてのOIDにするため) =# SELECT 'public.test'::regclass::oid; oid ------- 16539 (1 row)
内部的には上記のSQLのような、渡された情報に基づくOIDやオブジェクト名を返しているだけですが、ユーザ側からすると面倒なSQLを書かなくて良いメリットがあります。実際に、先の列のデータ型を知りたいSQLは以下の様に簡素化して記述することができます。
SELECT attname, atttypid::regtype FROM pg_attribute WHERE NOT attisdropped AND attrelid = 'myschema.test'::regclass AND attname = 'c2'; attname | atttypid ---------+---------- c2 | text
上記は、'myschema.test'をregclassにキャストしてOIDに変換し、attrelid(その列を保持するテーブルのOID)の条件合致に利用しています。また、atttypid(列のデータ型にOID)をregtypeにキャストすることで、データ型名を表示させています。
オブジェジェクト識別子データ型の注意点
さて、このようにオブジェクト名とOIDを柔軟に変換してくれるオブジェジェクト識別子データ型ですが、２点ほど注意点があります。
サーチパスの影響を考慮する
オブジェジェクト識別子データ型へのキャスト時は、サーチパスを使い最初に現れたものに変換します。そのため、OIDを取得する際、スキーマ名を省いた場合はサーチパスに依存した結果となることに注意してください。これはテーブルだけでなく、関数や演算子なども同様です。OID からオブジェクト名に変換する際も同様で、サーチパスで優先的に現れるオブジェクトはスキーマ修飾がされないなどの微細な違いもあります。以下に例を示します。
-- サーチパスはpublicスキーマを優先的に見る設定で'test'テーブルのOID取得 =# SELECT 'test'::regclass::oid; oid ------- 16539 -- サーチパスを"myschema"を優先的にみる変更にして'test'テーブルのOID取得 =# SET search_path TO 'myschema'; SET -- 変わった。これはpuclicスキーマではなくmyschemaスキーマのtestテーブルを拾っているため =# SELECT 'test'::regclass::oid; oid ------- 24932 -- publicスキーマのtestテーブルは優先的に拾われなくなったのでスキーマ修飾あり =# SELECT '16539'::regclass; regclass ------------- public.test -- サーチパスで優先的にひっかかるテーブルはスキーマ名が出てこない =# SELECT '24932'::regclass; regclass ---------- test -- サーチパス設定を戻す =# SET search_path TO public, myschema; SET =# SELECT '24932'::regclass; regclass --------------- myschema.test =# SELECT '16539'::regclass; regclass ---------- test
このようにサーチパスの設定によって出力が変化するので気をつけましょう。
存在しないオブジェクトのキャスト
オブジェクト識別子データ型は、存在しないオブジェトやOIDを対象とした場合にERRORとなります。PostgreSQLへ、xxxというテーブルが存在するかどうかを確認する検索も実施されることがあると思いますが、返却値として0やNULLなどを期待している場合にはこのオブジェクト識別子データ型は不向きなので注意して下さい。あるかどうかが不明なオブジェクトのメタ情報を知りたい場合には、素直に各システムカタログの結合を利用すべきでしょう。
-- 存在しないオブジェクトのキャスト =# SELECT 'non-exists'::regclass; ERROR: relation "non-exists" does not exist LINE 1: SELECT 'non-exists'::regclass;
なお、pgpool-IIには存在しないオブジェクトにキャストをしても、0(InavlidなOID)を返してくれる関数が付随されており、これを利用することでエラーを回避することができます。
=# SELECT pgpool_regclass('non-exists');
pgpool_regclass
-----------------
0
終わりに
いかがだったでしょうか？オブジェクト識別子データ型は、PostgreSQLのシステムカタログへのアクセスを多用するシーンでは非常に有用な型です。今まで面倒なクエリを書いていた場合には、ぜひ、活用してみて下さいね。