ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQLのオブジェクト識別子(OID)とキャスト

目的

オブジェクト識別子(以下、OID)とキャストについては、すでに詳細な解説記事があります。
オブジェクト識別子を活用する | Let's Postgres
本エントリではこの解説記事をベースにしつつ、理解し辛い点を補足します。

本エントリの目次を示します。

検証環境

CentOS 7
PostgreSQL 9.6

参照するPostgreSQLのドキュメントのバージョンも9.6とします。

解説

オブジェクト識別子(OID)

OIDはPostgreSQLのシステムテーブル(システムカタログ)の主キーとして使用されています。また、OIDによってオブジェクト(テーブルやインデックス、関数、演算子、データ型定義など)を一意に識別できます。例えば、pg_classカタログではOIDとテーブル・インデックス・ビューなどが紐付けられています。
参考:50.11. pg_class

OIDの別名データ型(オブジェクト識別子データ型)とキャスト

OIDには別名データ型(オブジェクト識別子データ型)があります。この一覧はマニュアルに記載されています。

型名 参照 説明 値の例
oid すべて 数値オブジェクト識別子 564182
regproc pg_proc 関数名 sum
regprocedure pg_proc 引数の型を持つ関数 sum(int4)
regoper pg_operator 演算子 +
regoperator pg_operator 引数の型を持つ演算子 *(integer,integer) or -(NONE,integer)
regclass pg_class リレーション名 pg_type
regtype pg_type データ型名 integer
regrole pg_authid ロール名 smithee
regnamespace pg_namespace 名前空間 pg_catalog
regconfig pg_ts_config テキスト検索設定 english
regdictionary pg_ts_dict テキスト検索辞書 simple
8.18. オブジェクト識別子データ型

オブジェクト識別子データ型は型キャストの構文で型に指定します。参考までに、型キャストの構文を引用します。

PostgreSQLは型キャストに2つの等価な構文を受け付けます。

CAST ( expression AS type )
expression::type

CAST構文はSQLに準拠したものです。 ::を使用する構文は、PostgreSQLで伝統的に使用されている方法です。

4.2. 評価式

ここで重要なのが、オブジェクト識別子データ型であっても、キャストの挙動は変わらない、ということです。これに関連して、解説記事では以下の記述があります。

例えばregclassへ(スキーマ名.)テーブル名を渡す(キャストする)と、OIDへ変換してくれます。逆にOIDを渡すと(スキーマ名.)テーブル名に変換してくれます。

オブジェクト識別子を活用する | Let's Postgres

この記述からはregclassへのキャストでOIDとテーブル名を相互に変換できるように見えますが、実際にはregclassへのキャストはregclass型へ変換しているにすぎません。

実際の挙動から確認します。

-- WHERE句を見ると、'pg_class'::regclassでOIDに変換されて見える。
SELECT
  oid,
  relname
FROM
  pg_class
WHERE
  oid = 'pg_class'::regclass;

 oid  | relname
------+----------
 1259 | pg_class

-- だが、SELECTしてみるとOIDを取得できない。
SELECT 'pg_class'::regclass;

 regclass
----------
 pg_class

-- OIDを取得するにはregclassへのキャストを、さらにoidへキャストする必要がある。
SELECT 'pg_class'::regclass::oid;

 oid
 ------
 1259

-- テーブル名 -> regclass -> oid となるわけでもない。
SELECT 'pg_class'::regclass::regclass;

 regclass
----------
 pg_class

参考:50.11. pg_class

regclassへのキャストで、SELECT句ではOIDを取得できず、WHERE句ではOIDに変換できたのは、暗黙的な型変換が関係しています。

まず、pg_castカタログからregclassのデータ型変換パスを確認します。

SELECT
  castsource::regtype,
  casttarget::regtype,
  castcontext
FROM
  pg_cast
WHERE
  castsource = 'regclass'::regtype
  OR casttarget = 'regclass'::regtype
ORDER BY
  castsource,
  casttarget;

    castsource     | casttarget | castcontext
-------------------+------------+-------------
 bigint            | regclass   | i
 smallint          | regclass   | i
 integer           | regclass   | i
 text              | regclass   | i
 oid               | regclass   | i
 character varying | regclass   | i
 regclass          | bigint     | a
 regclass          | integer    | a
 regclass          | oid        | i

参考:50.10. pg_cast

この結果より、text ⇒ regclass, regclass ⇒ oidへの変換があることが分かります。

"SELECT 'pg_class'::regclass;"は"text ⇒ regclass"でregclass型へ変換し、その結果としてpgclass.relnameを出力したと考えられます。

次に、左辺もしくは右辺がoid型の"="演算子について、pg_operatorカタログから確認します。

SELECT
  oprname,
  oprleft::regtype,
  oprright::regtype
FROM
  pg_operator
WHERE
  oprname = '='
  AND (
    oprleft = 'oid'::regtype
    OR oprright = 'oid'::regtype);

 oprname | oprleft | oprright
---------+---------+----------
 =       | oid     | oid

参考:50.34. pg_operator

"="演算子は左辺・右辺共にoid型であることを要求していることが分かります。

"SELECT oid, relname FROM pg_class WHERE oid = 'pg_class'::regclass;"の場合、WHERE句の左辺の"oid"列がoid型ですので、右辺のregclass型が暗黙的にoid型へ変換されたものと考えられます。

PostgreSQLのマニュアルでも、以下の2つのSQLはほぼ同等であることが示されています。

-- オブジェクト識別子データ型のキャストを利用したSQL
SELECT
  *
FROM
  pg_attribute
WHERE
  attrelid = 'mytable'::regclass;

-- オブジェクト識別子データ型のキャストを利用しないSQL
SELECT
  *
FROM
  pg_attribute
WHERE
  attrelid = (
    SELECT
      oid
    FROM
      pg_class
    WHERE
      relname = 'mytable');

ただし、後者のSQLスキーマの考慮がされておらず、完全に同等ではありません。スキーマパスを考慮する前者のSQLのほうが望ましいとされています。本節の主題から外れるため、詳細は割愛します。詳しくはマニュアルをご参照ください。
参考:8.18. オブジェクト識別子データ型

オブジェクト識別子データ型のキャストの使いどころ

オブジェクト識別子データ型のキャストの使い方の一例として、システムカタログの参照が挙げられます。システムカタログにはPostgreSQL固有の情報が格納されています。例えば、テーブルや列の情報などのスキーマメタデータ、及び内部的な情報です。

pg_locksビュー(50.65. pg_locks)で現在のロックを確認するSQLの例がありました。

SELECT
  l.pid,
  l.granted,
  d.datname,
  l.locktype,
  relation,
  relation::regclass,
  transactionid,
  l.mode
FROM
  pg_locks l
  LEFT JOIN pg_database d
    ON l.database = d.oid
WHERE
  l.pid != pg_backend_pid()
ORDER BY
  l.pid;

SQLを見やすくするため、オリジナルに対して整形を行っています。

【PostgreSQL-0007】ロック(Lock)の確認方法とデットロックの解除 - OSS ERP Compiere Distribution Lab

relation列は参照先がpg_class.oidとなっており、オブジェクト識別子データ型の一覧より、regclassでキャスト可能なことが分かります。
これに対し、database列は参照先がpg_database.oidとなっており、オブジェクト識別子データ型の一覧にありません。そこで、pg_locks.databaseとpg_database.oidをキーに結合して参照しています。

regprocとregoper別名型の制限

マニュアルに以下の記述があります。

regprocとregoper別名型は、一意な(オーバーロードしていない)名前のみを入力として受け入れるため、これらの使用には限度があります。 ほとんどの場合、regprocedureまたはregoperatorを使用するのが適切です。

8.18. オブジェクト識別子データ型

この記述を実際の挙動から確認します。

-- pg_procカタログでpiとsumの定義数を確認する
SELECT
  proname,
  count(*)
FROM
  pg_proc
WHERE
  proname IN ('pi', 'sum')
GROUP BY
  proname;

 proname | count
---------+-------
 pi      |     1
 sum     |     8

-- pg_procカタログ内で関数名が一意な場合
SELECT 'pi'::regproc;

 regproc
---------
 pi

-- pg_procカタログ内で関数名が一意でない場合
SELECT 'sum'::regproc;

ERROR:  more than one function named "sum"

-- 引数の型を指定してregprocedureでキャストする
SELECT 'sum(integer)'::regprocedure;
 regprocedure
--------------
 sum(integer)

参考:50.38. pg_proc

regprocでは一意な名前を指定しないとエラーになりました。このような場合、マニュアルに記載の通り、引数の型を指定してregprocedureでキャストする必要があります。

まとめ

オブジェクト識別子データ型のキャストを活用することで、SQLを正確にそして簡潔に記述することができます。オブジェクト識別子データ型は必要な全てのパターンが用意されているわけではないため、参照先と結合することが必要な場合もあります。