• BPnet
  • ビジネス
  • IT
  • テクノロジー
  • 医療
  • 建設・不動産
  • TRENDY
  • WOMAN
  • ショッピング
  • 転職
  • ナショジオ
  • 日経電子版
  • PR

  • PR

  • PR

  • PR

  • PR

オープンソース

[PostgreSQLウォッチ]第3回 ついに正式リリースされた7.4の新機能を見る

石井達夫 2003/11/25 日経SYSTEMS

 11月17日,ついにPostgreSQL 7.4が正式にリリースされた。今回は7.4の新機能のうち,実用上特に重要と思われる2つの機能,「INFORMATION SCHEMA」と「配列の改良」を紹介する。

◆DB,バージョン間非互換性の対策「INFORMATION SCHEMA」

 PostgreSQLには他のデータベース・システムと同様に「システム・カタログ」というものがあり,テーブル自体に関する情報などのいわゆる「メタデータ」が取得できる。例えばpsqlの\dコマンドを使うとテーブルの列名や型などが表示されるが,これもシステム・カタログを検索しているのである。

 このように便利なシステム・カタログだが,一つ欠点がある.それは,データベース・システムの実装に深くかかわっていると言うことだ。したがって,データベース・システムの実装が変ると,システム・カタログの使い方も変わらざるを得ない。

 データベース製品が変ればシステムカタログへの問い合わせの互換性がなくなるのは当然だが,同じPostgreSQLでもメジャー・バージョンが変るとシステム・カタログの互換性がなくなってしまうのである。システム・カタログを利用するアプリケーションの典型的なものとしては前回紹介したpgAdminIIIのようなデータベース管理ツールがある。このようなツールは,PostgreSQLがメジャー・バージョンアップするたびにプログラムの修正を余儀なくされる。

システム・カタログへの「標準インターフェイス」

 このような問題を解決するのがINFORMATION SCHEMAである。INFORMATIONSCHEMAはSQL標準で定義されている機能であり,INFORMATION SCHEMAを使えば標準的な方法でメタデータに関する問い合わせが実行できる。したがって,INFORMATION SCHEMAを使った管理ツールは,INFORMATION SCHEMAを実装した他のデータベース製品でも,原理的にはそのまま利用できることになる。

 ただし,データベース製品固有の情報はINFORMATION SCHEMAに含まれていない。そのため,データベース製品固有の情報を扱う場合には,今まで通りシステム・カタログを検索する必要がある.また,INFORMATION SCHEMAはPostgreSQLではviewとして実装されており,あくまで検索専用である。INFORMATION SCHEMAを使ってメタデータを更新することはできない。

 表1にINFORMATION SCHEMAで定義されているviewの概要を示す。各viewの詳細についてはPostgreSQL付属マニュアルの「IV. Client Interfaces」の「32. The Information Schema(ファイル名はinformation-schema.html)」を参照のこと。

表1●INFORMATION SCHEMAで定義されているview

view名概要
applicable_roles現在のユーザーが所属するrole(group)のリスト
check_constraintsCHECK制約
column_domain_usagedomainとそれを使用しているテーブル,列
column_privileges列毎の権限情報
column_udt_usage現在のユーザーが所有するデータ型と列情報
columns列情報
constraint_column_usage列制約情報
constraint_table_usage現在のユーザーが所有するテーブル制約情報
data_type_privileges現在のユーザーがアクセス権を持っているオブジェクト
domain_constraintsドメインと制約
domain_udt_usage現在のユーザーが所有するドメイン
domainドメイン
element_types配列のデータ型
enabled_roles現在のユーザーが所属するグループ
key_column_usage主キー,ユニーク・キーまた外部キー
parameters関数の引数
referential_constraints参照整合性
role_columns_grants現在のユーザーが所属するグループの持つ列に関する権限
role_routine_grants現在のユーザーが所属するグループの持つ関数に関する権限
role_table_grants現在のユーザーが所属するグループの持つテーブルに関する権限
role_usage_grants現在のユーザーが所属するグループの持つUSAGE権限
routine_privileges現在のユーザーが関係する関数に関する権限
routines関数
schemata現在のユーザーが所有するスキーマ
sql_featuresサポートするSQL機能
sql_implementation_infoSQLの実装に関する情報
sql_languagesPostgreSQLがサポートするSQL標準の情報
sql_packagesPostgreSQLがサポートするSQLパッケージの情報
sql_sizingPostgreSQLの制限値
sql_sizing_profiles未使用
table_constraints現在のユーザーが所有するテーブルに関する制約情
tablesテーブル
triggersトリガ
usage_privileges現在のユーザーが関係するUSAGE権限
view_column_usage現在のユーザーが所有するテーブルから派生したviewの列情報
view_table_usage現在のユーザーが所有するテーブルから派生したviewの情報
viewview

 INFORMATION SCHEMAは,information_schemaという特別なスキーマの中にある。information_schemaはデフォルトのスキーマ・サーチパスには含まれていないので明示的にスキーマを指定してINFORMATION SCHEMAにアクセスする必要がある。例を示す。
SELECT column_name,data_type FROM information_schema.columns;
 明示的にinformation_schemaを指定したくない場合は,information_schemaをスキーマ・サーチパスの先頭に追加する。
SET search_path TO 'information_schema','$user','public';
 もちろんpostgresql.confで設定してもよい。

 ただし,この方法ではテーブルなどのオブジェクトを新しく定義する際には,明示的にスキーマを指定しないとinformation_schemaの中にオブジェクトを作ってしまうことになる。注意されたい。

固有のデータ型を使用

 INFORMATION SCHEMAでは,いくつかの固有のデータ型が使われている。これらはDOMAINを使って定義されている。

表2●INFORMATION SCHEMA固有のデータ型

データ型コメント
cardinal_number0または正の整数
character_datavarchar
sql_identifiervarchar
time_stamptimestamp(2) without time zone

 INFORMATION SCHEMAの使用例として,テーブルを構成する列の情報を検索してみよう。

 以下のようなテーブルがあったとする。

CREATE TABLE t1(i INTEGER PRIMARY KEY,j TEXT);
 列名とデータ型は以下のように検索できる。
test=# SELECT column_name,data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 't1';
column_name | data_type
-----------+----------
 i       | integer
 j       | text
(2 rows)

配列に大きな改良,制約のかなりの部分が解消

 PostgreSQLでは配列が使える。例えば,

CRETAE TABLE t1(i INTEGER[]);
 とすると,整数型の配列を持つテーブルを作ることができる。配列をうまく使うと,データの要素の数があらかじめわからないが,かといって独立したテーブルにするほどでもないデータをうまく扱うことができるので重宝する。一方で配列にはいろいろな制約があり,普通のデータと同じようには使えなかった。

 7.4では配列に大きな改良が加えられ,これらの制約のかなりの部分が解消された。

配列でインデックスが利用可能に

 まず,配列型の列にインデックスを設定することができるようになった。

CRATE TABLE t1(i INTEGER[]);
CREATE INDEX t1index on t1(i);
 このようにインデックスを設定しておくと,次のような問い合わせインデックスの使用が考慮される。
SELECT * FROM t1 WHERE i < '{1,2,3}';
 ここで「{1,2,3}」は配列要素として1,2,3を持つ配列を表すPostgreSQLの記法 である。

 ただし,次のように個別の配列要素に対する問い合わせでは残念ながらインデックスは使用されない。

SELECT * FROM t1 WHERE i[1] = 1;

配列要素全体にわたる検索も可能

 次のような配列を含む行があったとき,

行1: {1,2,3}
行2: {2,3,4}
行3: {3,4,5}
 2を配列要素としてどこかに含む行(この場合,行1と行2)を検索するような問い合わせが可能となった。このための述語として,「ANY」が使える(ほかに,「SOME」があるがこれはANYと同じ)。ANYの使用例を示す。
CRATE TABLE t1(i INTEGER[]);
INSERT INTO t1 VALUES('{1,2,3}');
INSERT INTO t1 VALUES('{2,3,4}');
INSERT INTO t1 VALUES('{3,4,5}');
INSERT INTO t1 VALUES('{2,2,2}');

SELECT * FROM t1 WHERE 2 = ANY(i);
  i  
---------
{1,2,3}
{2,3,4}
(2 rows)
 また,
SELECT * FROM t1 WHERE 3 < ANY(i);
 は,一つ以上の配列要素が3よりも大きい配列を含む行を検索するので,実行 結果は
  i  
---------
{2,3,4}
{3,4,5}
(2 rows)
となる。

 他に,「ALL」という述語がある。ALLはすべての配列要素を検査する。

SELECT * FROM t1 WHERE 2 = ALL(i);
  i  
---------
{2,2,2}
(1 row)

SELECT * FROM t1 WHERE 3 < ALL(i);
は,すべての配列要素が2よりも大きい配列を含む行を検索するので,実行結果は
  i  
---------
{3,4,5}
(1 row)
となる。

新しい配列定義構文

 PostgreSQL 7.4では,「ARRAY」という新しい構文要素を使って配列を定義できる。従来の「{}」を使う方法に比べると,SQL文が書きやすく,かつわかりやすい。

 例えば,今まではTEXT型の配列にデータを挿入する場合,

INSERT INTO t2 VALUES('{"aaa","bbb"}');
 と書かなければならかった。PostgreSQLでは,通常文字列は'aaa'のように表記するので,これは例外のように見え,理解しにくい。ARRAYを使えば次のように文字列を'を使って普通に表記できる。
INSERT INTO t2 VALUES(ARRAY['aaa','bbb']);
ちなみに,ARRAYを使うと2次元配列もわかりやすく定義できる.
SELECT ARRAY[[1,2],[3,4]];
 array  
---------------
{{1,2},{3,4}}
(1 row)

豊富になった配列操作関数

 また,配列を結合する「||」が追加された。

SELECT ARRAY[1,2] || ARRAY[3,4];
?column?
-----------
{1,2,3,4}
(1 row)
 次元の違う配列の結合も可能である。
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
   ?column?
---------------------
{{5,6},{1,2},{3,4}}
(1 row)
 また,以下のような配列操作関数も追加された。

表3●配列操作関数
array_append配列を結合
array_lower配列の下限添字を返す
array_prepend配列の先頭に要素を挿入
array_to_string配列の要素を結合して文字列として返す
array_upper配列の上限添字を返す
string_to_array文字列を配列に変換

 PostgreSQL 7.4では,期待されたレプリケーションのような大きな機能追加はなかったものの,着実に使いやすくなっている。また,前々回でお知らせしたように,PostgreSQLの一部の性能的な弱点も克服されており,実用性の高いリリースと言えよう。なお,性能向上については,PostgreSQLウォッチの第1回で検証を行い,測定結果を公開しているので,あわせてご覧いただきたい。

 なお,7.4からはPostgreSQLのリリースアナウンスを行う「Regional Contact」と呼ばれるボランティアが全世界で組織されており,リリースと同時にあらかじめ各国語に翻訳されたリリース文がメディアなどに流れることになっている。PostgreSQLコミュニティの新しい試みとして注目したい(ちなみに,日本でのOfficial Regional Contactは日本PostgreSQLユーザ会が行うことになっている)。

石井達夫(Tasuo Ishii)

■著者紹介
石井達夫(いしい・たつお)氏
1984年,SRA入社。主にUNIX関連の開発に従事するかたわら,95年からPostgreSQLのメーリング・リストを主宰。現在はオープンソースソリューション部でPostgreSQL関連のビジネス活動を技術支援。著書に『PostgreSQL完全攻略ガイド』(技術評論社),『PHPxPostgreSQLで作る最強Webシステム』(技術評論社),『PostgreSQL構築・運用ガイド』(日経BP,共著)などがある。日本PostgreSQLユーザ会理事長。

あなたにお薦め

連載新着

連載目次を見る

今のおすすめ記事

ITpro SPECIALPR

What’s New!

経営

アプリケーション/DB/ミドルウエア

クラウド

運用管理

設計/開発

サーバー/ストレージ

クライアント/OA機器

ネットワーク/通信サービス

セキュリティ

もっと見る