9.4で盛り込まれる新構文とPostgreSQL本体のレプリケーション機能強化：PostgreSQLガイダンス（3）（1/4 ページ）

間もなく正式版のリリースが予定されているPostgreSQL 9.4の新機能のうち、運用は信頼性向上に関わる実装や、将来的な発展を見込んだ機能強化を見ていきます。

[高塚遥，SRA OSS, Inc.]

連載バックナンバー

　連載第1回ではPostgreSQL 9.4の注目機能である「JSONB」や「GINインデックス」を、第2回では運用時の利便性が向上する機能を中心に紹介してきました。

　今回は、新たに対応した問い合わせ構文と、レプリケーションに関する機能強化を紹介します。また、将来の機能追加のための基盤となる機能追加についても紹介します。なお、本連載では、執筆時点で公開されているベータ版を前提にして解説しています。

更新ビューのCHECKオプション

　PostgreSQLは、ビューに対して「INSERT」「DELETE」「UPDATE」の操作が実行可能です。この操作は、ビューの更新に対して、どのテーブルに反映させるかを「RULE」としてあらかじめ定義しておくことで実現しています。また、バージョン 9.3以降で、かつ、テーブル結合や集約処理を伴わないシンプルなビューであれば、ビューを定義するだけで自動的に更新可能になっています。

更新ビューを使ったユーザーごとの参照・操作の制御

　更新ビュー機能の応用として有力なのは、あるユーザーに対して、テーブルの特定の一部データだけを対象に、参照と操作を許可することです。

　具体的には、以下のようにWHERE条件の付いたビューを作り、GRANT命令でビューにSELECTとUPDATEを許可することで実現できます。

db1=# SELECT * FROM t_document ;
 id  |                data                 | is_private
-----+-------------------------------------+------------
 100 | {"title":"...", "contents":"..."}   | f
 101 | {"title":"...", "contents":"..."}   | f
 901 | {"title":"...", "contents":"..."}   | t
(3 rows)
db1=# CREATE VIEW v_public_document AS 
        SELECT * FROM t_document WHERE is_private = 'f';
db1=# GRANT UPDATE, SELECT ON v_public_document TO foo;

　ビューに権限を与えられたfooユーザーからは、次のようにして操作します。

db1=> SELECT * FROM v_public_document ;
 id  |               data                | is_private
-----+-----------------------------------+------------
 100 | {"title":"...", "contents":"..."} | f
 101 | {"title":"...", "contents":"..."} | f
(2 rows)
db1=> UPDATE v_public_document
        SET data = '{"title":"..", contetns:".."}' WHERE id = 100;

　ところが、この方式だと、以下のようなビューの範囲外の値に更新する処理を許すことになってしまいます。実行するとid=100の行はv_public_docuemntビューから消えたように見えます。

db1=> UPDATE v_public_document
        SET is_private = 't' WHERE id = 101;

　バージョン9.4から、CREATE VIEW命令にCHECKオプションが追加され、ビューで定義している範囲外の値を挿入したり、範囲外の値に更新することを禁止できるようになりました。

　CREATE VIEW命令のCHECKオプションは以下のように使用します。

db1=# CREATE VIEW v_public_document2 AS
        SELECT * FROM t_document WHERE is_private = 'f'
		WITH CHECK OPTION;

　CHECKオプションが付加されたビューの場合は、先ほどのような更新は下記の例のように、エラーとなります。

db1=> UPDATE v_public_document2
        SET is_private = 't' WHERE id = 100;
ERROR:  new row violates WITH CHECK OPTION for view "v_public_document2"
DETAIL:  Failing row contains (100, {"title":"...", "contents":"..."}, t).

　なお、CHECKオプションが使えるのは、今のところ自動的に定義された更新可能ビューだけです。RULEによって定義された更新可能ビューに対しては、CHECKオプションは使用できません。

集約クエリのFILTERオプション

　集約処理をするSELECT命令に新たな構文が加わりました。

　以下のSQL例では、さまざまな属性の付いたアクセスログのテーブル「t_access_log」を「useragent_id = 1」であるものに限定して、日ごとに件数を集計しています。

db1=# SELECT to_char(date_trunc('day', ts),'YYMMDD') t,
        count(*) FROM t_access_log WHERE useragent_id = 1
          GROUP BY t ORDER BY t;
    t   | count
--------+-------
 140101 |  1090
 140102 |   957
 140103 |  1123
 140104 |  1024
 140205 |  1011
(5 rows)

　これをFILTER構文を使って条件を選択リストに記述するように書き換えることができます。以下は、上記SQLと同じ結果になります。

db1=# SELECT to_char(date_trunc('month', dt),'YYYY-MM') t,
      count(*) FILTER (WHERE useragent_id = 1) FROM t_access_log
        GROUP BY t ORDER BY t;

　このSELECT命令のFILTER構文は、以下のようにさまざまな条件での集計を一度に行う際に便利です。

　また、以下のように記述すると、t_access_logに対する走査は全体で1回だけ行われ、条件付きの各集計はその中でそれぞれに実行されます。

　したがって、書き方が便利になっただけでなく、処理効率もアップしています。

db1=# SELECT to_char(date_trunc('day', dt),'YYMMDD') t,
        count(*) FILTER (WHERE useragent_id = 1) "IE",
        count(*) FILTER (WHERE useragent_id = 2) "Chrome",
        count(*) FILTER (WHERE lang_id = 1) "lang:Ja" ,
        count(*) "whole" FROM t_access_log
          GROUP BY t ORDER BY t;
    t   |  IE  | Chrome | lang:Ja | whole
--------+------+--------+---------+-------
 140101 | 1090 |    880 |   3522  | 10497
 140102 |  957 |    792 |   3057  |  9472
 140103 | 1123 |    893 |   3496  | 10505
 140104 | 1024 |    811 |   3268  | 10161
 140105 | 1011 |    883 |   3474  | 10518
(5 rows)