PostgreSQL 9.3の新機能: 更新可能VIEW
SRA OSS, Inc. 日本支社
石井 達夫
現在開発中のPostgreSQL 9.3ですが、徐々に新機能が出揃ってきました。その中の一つに「更新可能VIEW」("Updatable view")というのがあります。これはその名の通り、更新可能なVIEWを作る機能です。
VIEWとは？
VIEWとは、実際には存在しないテーブルのことで、CREATE VIEWというコマンドを使って定義します。VIEWは一種のマクロと考えることができ、よく使うSELECT文をVIEWとして定義することにより、複雑なSELECT文を毎回打ち込む手間を省くことができます。
たとえば、
CREATE VIEW mytable_foo AS SELECT * FROM mytable WHERE id = 'foo';
としておくと、
SELECT * FROM mytable_foo;
とするだけで、
SELECT * FROM mytable WHERE id = 'foo';
としたのと同じ結果が得られます。
VIEWを更新するには？
VIEWはこのように、一見するとテーブルのように扱うことができます。せっかくテーブルに見えるのであれば、更新もしたいですよね。しかし、今までのPostgreSQLでは、VIEWはそのままでは読み出し専用で、更新は許されませんでした。
test=# UPDATE mytable_foo SET i = 10; ERROR: cannot update view "mytable_foo" HINT: You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
メッセージにあるように、「ルール」("rule")あるいはトリガ("trigger")機能を使って更新動作を定義してあげる必要がありました。
test=# CREATE RULE mytable_foo_update AS ON UPDATE TO mytable_foo DO INSTEAD update mytable SET i = new.i, id = new.id WHERE id = 'foo'; CREATE RULE test=# UPDATE mytable_foo SET i = 10; UPDATE 1
しかし、ご覧のように、ちょっとこれは面倒です。おまけに、UPDATEだけでなく、INSERTやDELETEに対してもルールを作ってあげないと、INSERTもDELETEもできません。
9.3では更新ルールを自動設定
嬉しいことに、PostgreSQL 9.3ではルールやトリガを作らなくてもVIEWが更新できるようになります。先ほどの例で言えば、CREATE VIEWした後に、すぐに、
UPDATE mytable_foo SET i = 10;
が実行できます。
実際にどうなっているのかEXPLAINで確認してみましょう。
test=# EXPLAIN ANALYZE UPDATE mytable_foo SET i = 10;
QUERY PLAN
---------------------------------------------------------------------
Update on mytable (cost=0.00..25.38 rows=6 width=38) (actual time=0.068..0.068 rows=0 loops=1)
-> Seq Scan on mytable (cost=0.00..25.38 rows=6 width=38) (actual time=0.036..0.038 rows=1 loops=1)
Filter: (id = 'foo'::text)
Rows Removed by Filter: 1
Total runtime: 0.131 ms
(5 rows)
ご覧のように、確かにVIEWの元になっているテーブル「mytable」が自動的に更新されています。
更新可能なVIEWかどうかチェックする方法
ところで、どんな場合でも自動更新できるVIEWになるわけではありません。たとえば複数のテーブルを結合しているVIEWでは、どのテーブルを更新して良いのかシステムでは判断が付かない場合もあります。また、集約関数を使っているVIEWも駄目です。
そこで、CREATE VIEWを実行したときに、information_schemaというテーブルを参照することにより、自動更新の対象になっているかどうか判断することができます。
SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name = 'mytable_foo'; table_name | is_updatable | is_insertable_into -------------+--------------+-------------------- mytable_foo | YES | YES (1 row)
is_updatableが「YES」ならUPDATEまたはDELETEが可能で、is_insertable_intoがYESならINSERTが可能です。
自動更新になるVIEWの条件
PostgreSQL 9.3の付属マニュアル「SQLコマンド」の「CREATE VIEW」の項に、どのようなVIEWが自動更新の対象になるか詳しく書いてあるので抜粋しておきます。
- FROMリストに一個のテーブルだけが記述されていること（なお、テーブルは実在するテーブルだけでなく、更新可能なVIEWでも構いません）
- VIEWの定義の最上位レベルにWITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSETが含まれていないこと
- VIEWの定義の最上位レベルに集合操作(UNION, INTERSECT, EXCEPT)が含まれていないこと
- VIEWのSELECTに現れるカラムはテーブルのカラムへの単純な参照であること。式、リテラル、関数であってはいけません。また、システムカラムへの参照も許されません
- 同じカラムがVIEWのSELECTリストに2回以上現れないこと
- VIEWがSECURITY_BARRIER属性を持っていないこと
このような条件を満たさないVIEWでは、以前のように自分でルールやトリガを使えば更新可能にできます。ちなみに、自動的に更新可能になったVIEWに、あえて自分でルールやトリガを設定した場合は、自分で設定した方が優先されます。
最後に
商用DBでは更新可能VIEWはすでに実装されている機能ですが、これがPostgreSQLでも使えるようになるのは嬉しい限りです。PostgreSQL 9.3のリリースが待ち遠しいですね！