OID 別名型を使って PostgreSQL の内部情報を取り出す

こちらは PostgreSQL Advent Calendar 2021 の 21 日目の記事です。

今回は、PostgreSQL の内部情報を調べるときに役立つ、regclass や regnamespace などの OID 別名型 を紹介します。

PostgreSQL では、テーブルやスキーマなど、すべてのオブジェクトには OID と呼ばれる識別子が割り振られていて、システムカタログと呼ばれる内部情報が入ったテーブルでは、この OID が至るところに出てきます。

例えば、システムカタログ pg_class にはテーブルやインデックスなど、リレーションの情報が入っています。

=# SELECT oid, relname, relnamespace, relowner FROM pg_class;
  oid  |                    relname                    | relnamespace | relowner
-------+-----------------------------------------------+--------------+----------
 16391 | pgbench_accounts                              |         2200 |       10
 16394 | pgbench_branches                              |         2200 |       10
 16385 | pgbench_history                               |         2200 |       10
 16388 | pgbench_tellers                               |         2200 |       10
 16401 | pgbench_branches_pkey                         |         2200 |       10
 16403 | pgbench_tellers_pkey                          |         2200 |       10
 16405 | pgbench_accounts_pkey                         |         2200 |       10
  2619 | pg_statistic                                  |           11 |       10
  1247 | pg_type                                       |           11 |       10
  2836 | pg_toast_1255                                 |           99 |       10
(以下略)

oid 列はリレーション自体の OID、relname 列はリレーションの名前、relnamespace 列はリレーションを含むスキーマの OID、relowner 列はリレーションを所有するロールの OID を表します。が、OID はただの数字なので、スキーマやロールの OID を見ても、どのスキーマでどのロールか分かりません。

OID をオブジェクトの名前に変換するには、スキーマやロールの情報が入ったシステムカタログがあるので、それらと結合すればできます。

=# SELECT c.oid, c.relname, n.nspname, r.rolname FROM pg_class AS c
-#   JOIN pg_namespace AS n ON c.relnamespace = n.oid
-#   JOIN pg_roles AS r ON c.relowner = r.oid;

  oid  |                    relname                    |      nspname       | rolname
-------+-----------------------------------------------+--------------------+----------
 16391 | pgbench_accounts                              | public             | postgres
 16394 | pgbench_branches                              | public             | postgres
 16385 | pgbench_history                               | public             | postgres
 16388 | pgbench_tellers                               | public             | postgres
 16401 | pgbench_branches_pkey                         | public             | postgres
 16403 | pgbench_tellers_pkey                          | public             | postgres
 16405 | pgbench_accounts_pkey                         | public             | postgres
  2619 | pg_statistic                                  | pg_catalog         | postgres
  1247 | pg_type                                       | pg_catalog         | postgres
  2836 | pg_toast_1255                                 | pg_toast           | postgres
(以下略)

ただ、見てのとおり、SQL が少々長いと思ったと思います。これをシンプルに書き換えるのが OID 別名型です。

=# SELECT oid, relname, relnamespace::regnamespace, relowner::regrole FROM pg_class;
  oid  |                    relname                    |    relnamespace    | relowner
-------+-----------------------------------------------+--------------------+----------
 16391 | pgbench_accounts                              | public             | postgres
 16394 | pgbench_branches                              | public             | postgres
 16385 | pgbench_history                               | public             | postgres
 16388 | pgbench_tellers                               | public             | postgres
 16401 | pgbench_branches_pkey                         | public             | postgres
 16403 | pgbench_tellers_pkey                          | public             | postgres
 16405 | pgbench_accounts_pkey                         | public             | postgres
  2619 | pg_statistic                                  | pg_catalog         | postgres
  1247 | pg_type                                       | pg_catalog         | postgres
  2836 | pg_toast_1255                                 | pg_toast           | postgres
(以下略)

OID をオブジェクトに応じた OID 別名型、スキーマであれば regnamespace 型、ロールであれば regrole 型にキャストすることでオブジェクトの名前に変換できます。

また、逆にオブジェクトの名前を OID 別名型にキャストすると、OID に変換できるので、例えば、public スキーマに含まれるリレーションを取り出すとき、副問い合わせを使わずに条件式を書くことができます。

=# SELECT oid, relname, relnamespace::regnamespace, relowner::regrole FROM pg_class
-#   WHERE relnamespace = 'public'::regnamespace;
  oid  |        relname        | relnamespace | relowner
-------+-----------------------+--------------+----------
 16391 | pgbench_accounts      | public       | postgres
 16394 | pgbench_branches      | public       | postgres
 16385 | pgbench_history       | public       | postgres
 16388 | pgbench_tellers       | public       | postgres
 16401 | pgbench_branches_pkey | public       | postgres
 16403 | pgbench_tellers_pkey  | public       | postgres
 16405 | pgbench_accounts_pkey | public       | postgres
(7 行)

OID 別名型には全部で以下のものがあります。今回紹介した型以外ではリレーションの変換に regclass 型をよく使います。

型名	参照先のシステムカタログ	オブジェクト
regclass	pg_class	リレーション
regcollation	pg_collation	照合順序 (バージョン 13 以降)
regconfig	pg_ts_config	テキスト検索設定
regdictionary	pg_ts_dict	テキスト検索辞書
regnamespace	pg_namespace	スキーマ
regoper	pg_operator	演算子
regoperator	pg_operator	引数の型をもつ演算子
regproc	pg_proc	関数
regprocedure	pg_proc	引数の型をもつ関数
regrole	pg_authid	ロール
regtype	pg_type	型

OID 別名型を使わなくても SQL は書けますが、使えばシンプルに書くことができるので、知らなかった人は覚えておくと、何かの役に立つと思います。