Planet PostgreSQL JP

お気づきの方も多いと思いますが、Planet PostgreSQL JPにてAdvent Calendarを追跡中です。・・・が、どうもYahoo PipseとGAEの組み合わせが悪いようでPipseの更新を自動で反映できず、たまに私がデータの更新日付を１年前に戻して再取得させている・・・という涙ぐましいお話。

ちなみにYahoo Pipseは下記の要領で動いています。

ATNDのコメントRSSを取得

↓

各リンクURLを収集

↓

各URLから当該ページのRSSを取得

↓

タイトルまたは本文にPostgreSQL Advent Calendarの文字列が含まれるエントリを抽出

↓

新しいRSSとして再構成

という流れになっています。初めてPipse触ったんですが、見た目のファンシーさの割にできることがわかりにくく・・・結局３時間ぐらいかかったので自前でプログラム書いた方が早かったんじゃというツッコミもありつつ、まあできたので良しと。上記の通り文字列抽出をしているのでAdvent Calendar本体の記事から程なく別目的で同様の文字列を含むエントリをアップした場合誤認識する可能性があります。なんでURLマッチしないんだと言われそうですが、いろいろあって結論からいうとPipseではUNIONできてもJOINできないんですねこれが。

というわけで各エントリをアップ頂いている皆々様には感謝の言葉に尽きますが、Yahoo Pipseからのアクセスがトップになっているからといって私に後ろ指ささないように・・・

このエントリは、PostgreSQL Advent Calendar : ATNDの12/10分です。

今回はPostgreSQLの型拡張機能の一つである、ドメイン機能を使って何が出来るのかを簡単に紹介したいと思います。
と、その前に・・・

データの制約

当たり前のことですが、RDBMSの列には「データ型」というものが設定されています。でも、データ型だけでは厳密な制約を課すことができないケースもありえます。

• ex. integer型というだけでは、その列に「負数は論理上入らない」という制約はつけられない。
• ex. text型というだけでは、その列に(電話番号のように)「数字とハイフンしか入らない」という制約はつけられない。

こうした意味的なチェックというのはアプリケーションでも勿論行えますが、データベースの機能としても存在しています。

PostgreSQLで使える制約

もちろん、PostgreSQLにもデータ型以外の様々な制約機能が存在します。

• 検査制約
  • 式によって制約を規定する。
  • 例：この列は0より大きい値でなければならない。
• 非NULL制約
  • NULLを許容しない制約を付与する。
• 一意性制約
  • 全てのレコードで値がユニークであることを規定する。
• プライマリーキー（非NULL制約＋一意性制約）
• 外部キー
  • 別テーブルのカラムと一致することを規定する。
• 排他制約

ドメインは上記の「検査制約」を付与した型を作成する機能です。
PostgreSQLではユーザが自由に型を作成することが出来ます。型の作成方法には色々な種類がありますが、その一つのパターンとして、型を作成するときに、以下ような制約を付与することができます。

• 値の範囲を列挙（ENUM）として規定した型を作る。
• 値のドメイン（設定可能な値集合）を指定した型を作る。

さて、個々の列に対して前述の検査制約を付与することは元々可能なのですが、では制約付きの型を作ると何が嬉しいか？という疑問もあるかもしれません。
一番の理由は「制約付きの型を作るのはメンテナンス性向上のため」なのかなと思っています。
例えば、E-mailアドレスなど、あちこちのテーブルに散在しそうな一般的なデータなどは、ドメインとして制約を記述しておくことで、制約自体のルール変更箇所をまとめられます。
あと、教科書に従ったデータベース設計をするなら、それなりに出番はあるんじゃないかとも・・・(概念設計時にカラムの取りうる値域をきちんと定義したら、それが列挙なりドメインなりになるのではないかと）

ドメインを作成してみる。

前置きが長くなりましたが、ここからは実際にドメインを作成してみます。

CREATE DOMAIN

ドメインを作る場合には、CREATE DOMAINというコマンドを使います。このコマンドは基本型だけでなく、既に作成したドメインに対しても使うことができます(つまり、ドメインはネストできます)。
形式は

CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

また、constraintは

[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK (expression) }

という形式です。詳細はCREATE DOMAINを見てください。
では実際に作成してみます。

3の倍数と3が付く数字のときだけエラーになる整数型

そういえば、ちょっと前に「世界のナベアツ」が「3の倍数と3が付く数字のときだけアホになります」というネタをやってましたが、それにあやかって(?)「3の倍数と3が付く数字のときだけエラーになる」整数型のドメイン「nabeatsu」を定義してみようと思います。

まず、こんな感じでドメインを作成してみます。

CREATE DOMAIN nabeatsu AS INTEGER
CHECK (
  NOT (VALUE % 3 = 0 OR
  substring(VALUE::text from '3') is not null)
);

CHECK (・・・)の部分で、「3の倍数と3が付く数字のときだけエラーになる」チェックをかけています。

ドメインを作成したので、早速使ってみましょう。

CREATE TABLE hoge (
id int,
data nabeatsu
);

psqlの \d コマンドで確認すると、data列は基底となったintegerではなく、作成したドメインnabeatsuの型として表示されます。

      Table "public.hoge"
 Column |   Type   | Modifiers
--------+----------+-----------
 id     | integer  |
 data   | nabeatsu |

テーブルを定義したので、実際にデータを挿入して、本当にdata列が「3の倍数と3が付く数字のときだけエラーになる」のか見てみます。

INSERT INTO hoge VALUES (1, 1);
INSERT 0 1
INSERT INTO hoge VALUES (2, 3);
ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"
STATEMENT:  INSERT INTO hoge VALUES (2, 3);
psql:nabeatsu.sql:20: ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"
INSERT INTO hoge VALUES (3, 6);
ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"
STATEMENT:  INSERT INTO hoge VALUES (3, 6);
psql:nabeatsu.sql:21: ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"
INSERT INTO hoge VALUES (4, 10);
INSERT 0 1
INSERT INTO hoge VALUES (5, 13);
ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"
STATEMENT:  INSERT INTO hoge VALUES (5, 13);
psql:nabeatsu.sql:23: ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

この例では、dataに対して1, 3, 6, 10, 13という数値を挿入しようとしていますが、3, 6, 13は「3の倍数と3が付く数字」なのでエラーになります。成功ですね。
このときのエラーは
ERROR: value for domain nabeatsu violates check constraint "nabeatsu_check"
のように表示されます。constraintの名称は「ドメイン名 + "_" + check」という名称になるようです。

XMLの妥当性検証を行うXML型

これだけじゃアレなので、もう少し実用的そうなものを考えてみます。

PostgreSQLにもXML型があり、XMLデータを挿入したり、XPathで検索したり出来ます。
現状PostgreSQL本体の機能としては、XMLの形式として正しいかどうか(Wel-formedな形式かどうか)は、チェックしていますが、XMLの妥当性検証(XMLの構造、値の内容等が正しいものかを検証する)を行う機能は残念ながら入っていません。
(XMLの妥当性検証については、それだけで膨大な説明が必要なので詳細は割愛します・・・)

ということで今度はXMLの妥当性検証を行うドメインをつくってみようと思います。
これはSQL定義だけでは出来ないので、事前に以下のようなPostgreSQL関数をC言語で作成しておきます。

• 入力としてXML型とXML Schema(妥当性検証の定義が記述されたXML)を入力として、trueかfalseを返却する関数を定義する。
• 関数内でlibxml2のXML Schema Validation APIを呼び出して妥当性検証を行う。
• 妥当性検証が成功したらtrueを、失敗したらfalseを返却する。

この関数のコードは割愛しますが、C言語で組んでも100 stepもないような簡単なものです。

次に上記のC言語関数 valid_xml をCREATE FUNCTIONコマンドを使ってPostgreSQLに登録します。

CREATE OR REPLACE FUNCTION valid_xml(xml, text)
  RETURNS boolean
AS '$libdir/valid_xml', 'valid_xml'
LANGUAGE 'C' IMMUTABLE STRICT;

で、ドメインを定義して、そのドメインを用いたテーブル定義を行います。

test=# CREATE DOMAIN test_xsd AS xml
test-# CHECK (
test(#   valid_xml(VALUE, '/tmp/test.xsd')
test(# );
CREATE DOMAIN
test=# CREATE TABLE vxml_table (data test_xsd);
CREATE TABLE
test=# \d vxml_table
   Table "public.vxml_table"
 Column |   Type   | Modifiers
--------+----------+-----------
 data   | test_xsd |

valid_xml関数の第2引数には、XML Schemaファイルのパスを記述します。例えば今回の例では、以下のようなXML SchemaファイルがPostgreSQLサーバの /tmp/test.xsd に存在するとします。

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xs:element name="A">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="B"/>
        <xs:element ref="C"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="B" type="xs:NCName"/>
  <xs:element name="C" type="xs:NCName"/>
</xs:schema>

XML Schemaの細かい説明は省きますが、この定義は要するに A要素の下にはB要素→C要素が順に存在しなければならないというルールを示しています。

では、XMLを挿入してみます。

test=# INSERT INTO vxml_table VALUES ('<A><B>bb</B><C>cc</C></A>');
INSERT 0 1
test=# INSERT INTO vxml_table VALUES ('<A><B>bb</B><D>dd</D></A>');
ERROR:  value for domain test_xsd violates check constraint "test_xsd_check"
test=#

最初の例も2番目の例もXMLの形式としては正しいのですが、2番目のXMLはエラーになっていまいます。このエラーはドメインのCHECK制約によるものなのですが、なぜCHECK制約に引っかかったのかというと、2番目のXMLは「A要素のD要素がある」から、XMLスキーマ定義に合わなかったと判断されたためです。成功ですね。
PostgreSQL本体では、XML Schemaによる妥当性検証機能はありませんが、ドメインを使うことで、アプリケーション側ではなく、PostgreSQL側で妥当性検証を行う拡張も可能になりました。
ドメイン万歳ですね！

---

明日の担当は sakai_k さんです。よろしくお願いします。

このエントリはPostgreSQL Advent Calendarの12/7分です。

現在開発中のPostgreSQL 9.2でSynchronized Snapshot(同期スナップショット？)なる機能が導入されます。開発の動機はpg_dumpallで複数のデータベースのバックアップを一貫性のある内容で取りたいということのようで、あまり派手な機能ではないのですが、開発中の pgsql_fdw と組み合わせて面白いことできないかな、と思って調べたので内容を整理してみます。

そもそも「Snapshot(スナップショット)」って何？

PostgreSQLにおける「スナップショット」とは、MVCC で使われる情報の一つで、大雑把に言えばあるトランザクションからどの時点でのデータが可視であるかを判定する基準です。Synchronized Snapshotでは、このスナップショットを複数のトランザクションで共有することによって同一内容のデータを参照できるようになります。

スナップショットを同期する流れ

参照するデータの準備

まず、参照するデータを用意します。デフォルトの分離レベルはREAD COMMITTEDなので、この時点で他のトランザクションから1,000件が参照できます。

<Session 1-データ操作>
postgres=# CREATE TABLE foo (
postgres-# 	id int,
postgres-# 	name text,
postgres-# 	CONSTRAINT foo_pkey PRIMARY KEY (id)
postgres-# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
postgres=# INSERT INTO foo SELECT id, to_char(id, '00000000') FROM generate_series(1, 1000) id;
INSERT 0 1000

同期元トランザクションの開始

まず、基準となるトランザクションがいないことには話が始まりませんので開始します。今回はデータを作成したトランザクションとは別に同期元トランザクションを開始してみます。この時点で参照できるのは1,000件です。

<Session 2-同期元>
postgres=# BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
postgres=# SELECT count(*) FROM foo;
 count
-------
  1000
(1 row)

スナップショットのエクスポート

同期元のトランザクションでpg_export_snapshot()関数を実行してスナップショットをエクスポートします。関数の戻り値がスナップショット識別子なので、後で使うまで覚えておきましょう。なお、READ COMMITTED分離レベルの場合は、トランザクション進行中に複数回実行すると異なるスナップショット識別子が返り、複数の別トランザクションでそれぞれの時点のデータを参照することもできます。

<Session 2-同期元>
postgres=# select pg_export_snapshot();
 pg_export_snapshot
--------------------
 000007CE-1
(1 row)

データ追加

同期元とは別のセッションで、先ほどのテーブルにデータを1,000件追加してコミットしておきます。同期元トランザクションはREAD COMMITTED分離レベルなので、参照できるデータは2,000件になります。

<Session 1-データ操作>
postgres=# INSERT INTO foo SELECT id, to_char(id, '00000000') FROM generate_series(1001, 2000) id;
INSERT 0 1000
<Session 2-同期元>
postgres=# SELECT count(*) FROM foo;
 count
-------
  2000
(1 row)

同期先トランザクションの開始

別のセッションでトランザクションを開始します。が、同期元との間に分離レベルの制約があります。

同期元	同期先
SERIALIZABLE	REPEATABLE READまたはSERIALIZABLE
REPEATABLE READ	REPEATABLE READ
READ COMMITTED	REPEATABLE READ

ここでは同期元がREAD COMMITTEDなので、REPEATABLE READを使います。

<Session 3-同期先1>
postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN

スナップショットのインポート

同期先のトランザクションで先ほど取得したスナップショット識別子を指定してpg_import_snapshot()関数を実行します…と言いたいところですが、インポートにはSET TRANSACTION SNAPSHOT文を使うことになっています。エクスポートした時点と同じく参照できるのは1,000件が参照ですね。

<Session 3-同期先1>
postgres=# SET TRANSACTION SNAPSHOT '000007CE-1';
SET
postgres=# SELECT count(*) FROM foo;
 count
-------
  1000
(1 row)

なお、SET TRANSACTION SNAPSHOT文はそのトランザクションの最初のクエリでなければならない、という制約があります。私は最初にpsqlでSET TRANSACTION文を打つときに補完機能を使ってしまい、裏でSELECT文が発行されてトランザクション開始からやりなおすはめになりました…

別のスナップショットをエクスポート/インポート

同期元トランザクションから2,000件参照できる状態でエクスポートしたスナップショットを使うと、同じデータが参照できます。

<Session 2-同期元>
postgres=# select pg_export_snapshot();
 pg_export_snapshot
--------------------
 000007BB-1
(1 row)
<Session 4-同期先2>
postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# SET TRANSACTION SNAPSHOT '000007CE-1';
SET
postgres=# SELECT count(*) FROM foo;
 count
-------
  2000
(1 row)

まとまってないまとめ

• トランザクション分離レベルの制約があるので、利用シーンは限られるかも。まだ英語のみですが、詳細はPostgreSQL: Documentation: Manuals: System Administration Functionsにあります。
• psqlの補完機能は裏でSQL文を発行するので、SET TRANSACTION SNAPSHOT文に使っちゃダメ。
• スナップショットは実は$PGDATA/pg_snapshotにテキストファイルで保存されている。書き換えは試してませんがきっと未定義動作でしょう。
• あくまで「スナップショット同期」であって同一トランザクションになるわけではないので、別トランザクションが更新した未コミットのデータは見えない(当たり前ですが)。これができればpgsql_fdwとか非同期クエリとか組み合わせてパラレルクエリができるかな、と思ってたんですがそうは問屋が卸さないようです。

このエントリ読んだ方で何か面白い使い道を思いついた方がいたら、ぜひ教えてください。m(_ _)m

明日のPostgreSQL Advent Calendarは〜♪

fujii_masaoさんです。

PostgreSQLはテーブルの結合 (ジョイン, JOIN) が得意です。ぶっちゃけ、JOINを使わないのであれば他のDBを使ったほうが良いと思っているくらいです。で、そんなJOINなのですが、書き方に流派がいくつかあるようです。以下のようなテーブルを考えて：

CREATE TABLE item (item_id integer, price integer);
CREATE TABLE list (list_id integer, item_id integer, amount integer);

1. FROM にテーブルをならべて WHERE で条件を書く。Oracle流？

SELECT list_id, item.item_id, amount * price
  FROM item, list WHERE item.item_id = list.item_id

2. JOIN ～ ON 構文。実は括弧は要らない。

SELECT list_id, item.item_id, amount * price
  FROM item JOIN list ON item.item_id = list.item_id

3. JOIN ～ USING () 構文。こちらは括弧が必須。

SELECT list_id, item_id, amount * price
  FROM item JOIN list USING (item_id)

個人的には、3.がキータイプ数を減らせておススメ。「列名はテーブルを跨っても一意な名前を付ける必要がある」という制約もありますが、それはそれでミスを避けやすい気がします。また JOIN を使う 2 と 3 の書き方は、後から LEFT JOIN 等に変更する必要が出た場合でも、SQL文を大幅に書き換えずに済むのが地味に嬉しいです。ただ、SQL Server 等、USING が使えないDBもあるようなので、業界ではマイナーな書き方かもしれません。

このエントリはPostgreSQL Advent Calendarに参加しています。12/4(日)担当也。ヨーロッパ中部時間ではまだ12/3(土)ですが。

---

RDBMSで行レベルのアクセス制御を実現する方法として、利用者に対して直接のアクセス権を付与せずに、特定のビューを通してだけアクセスを許可するのはしばしば使われるテクニックです。

ですが、場合によっては不可視な行の中身を参照できてしまうというのは、あまり広く認知されている訳ではないようです。

ので、問題のポイントと、現在開発中の PostgreSQL v9.2 に提案しているアイデアをご紹介します。

ユーザ定義関数のCOST値による問題

ここでは、以下の表を例に考えてみます。

customerテーブル

列名	型	制約
cid	int	primary key
cname	text	not null
cmail	text
cpasswd	text

customerテーブルには全顧客の情報が格納されているため、利用者は自分自身の情報しか見る事ができないよう設定しましょう。

（便宜上 PostgreSQL ユーザ名が cname に対応するものとします）

postgres=# CREATE VIEW my_account AS SELECT * FROM customer
                  WHERE cname = getpgusername();
CREATE VIEW
postgres=# GRANT SELECT ON my_account TO public;
GRANT

本来、このテーブルには 3ユーザ分の情報が格納されているのですが、

postgres=# SELECT * FROM customer;
 cid | cname |       cmail       | cpasswd
-----+-------+-------------------+----------
 101 | alice | alice@example.com | abcdef
 102 | bob   | bob@example.com   | xyz123
 103 | eve   | eve@example.com   | deadbeaf
(3 rows)

確かに、自分自身の情報しか参照できないように見えます。

postgres=# SET SESSION AUTHORIZATION alice;
SET
postgres=> SELECT * FROM customer;
ERROR:  permission denied for relation customer
postgres=> SELECT * FROM my_account;
 cid | cname |       cmail       | cpasswd
-----+-------+-------------------+---------
 101 | alice | alice@example.com | abcdef
(1 row)

しかし、利用者がSQL関数を定義できる場合、面白い事が起こります。

（publicスキーマはデフォルトでCREATE権限を全体に与えている事に注意！）

postgres=> CREATE FUNCTION f_leak(text) RETURNS bool LANGUAGE plpgsql
           COST 0.00000001
           AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
CREATE FUNCTION

postgres=> SELECT * FROM my_account WHERE f_leak(cmail);
NOTICE:  f_leak => alice@example.com
NOTICE:  f_leak => bob@example.com
NOTICE:  f_leak => eve@example.com
 cid | cname |       cmail       | cpasswd
-----+-------+-------------------+---------
 101 | alice | alice@example.com | abcdef
(1 row)

おっと、何か見えてはならないモノが見えたようです。

どういう事なのでしょうか、ちょっと EXPLAIN で調べてみましょう。

postgres=> EXPLAIN SELECT * FROM my_account WHERE f_leak(cmail);
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on customer  (cost=0.00..20.85 rows=1 width=100)
   Filter: (f_leak(cmail) AND (cname = (getpgusername())::text))
(2 rows)

この実行計画はVIEWの本体である customer テーブルをスキャンしていますが、利用者が付与した f_leak() とVIEWの条件を順にチェックしています。

問題は、副作用を持つ f_leak() の実行コストが非常に小さな値に設定されているため、オプティマイザは cname = getpgusername() より先にf_leak()を実行して不必要な条件の判断を省略した方が得策であると判断して、関数の実行順序を並べ替えている事です。その結果、不可視であるべき行の内容が引数としてf_leak()に渡され、それが利用者に漏えいしている訳です。

JOINと条件句の分配に伴う問題

同様に、VIEWによる行レベルアクセス制御を破るシナリオはもう一つ知られています。

先ほどの customer テーブルに加えて、もう一つテーブルを追加して考察を進めてみましょう。

creditテーブル

列名	型	制約
cid	int	references customer(cid)
number	text
expired	date

この credit テーブルは顧客のクレジットカード番号を保持しています。先ほどの my_account ビューと同様に、自分自身のレコードだけを参照できるようなVIEWを定義してみましょう。

postgres=# SELECT * FROM customer;
 cid | cname |       cmail       | cpasswd
-----+-------+-------------------+----------
 101 | alice | alice@example.com | abcdef
 102 | bob   | bob@example.com   | xyz123
 103 | eve   | eve@example.com   | deadbeaf
(3 rows)

postgres=# SELECT * FROM credit;
 cid |       number        |  expired
-----+---------------------+------------
 101 | 1111-2222-3333-4444 | 2014-02-28
 102 | 5555-6666-7777-8888 | 2013-10-30
 102 | 1234-5678-1234-5678 | 2015-06-30
 103 | 0987-6543-2109-8765 | 2014-08-31
(4 rows)

postgres=# CREATE VIEW my_credit AS SELECT cname, cmail, credit.*
           FROM customer NATURAL JOIN credit WHERE cname = getpgusername();
CREATE VIEW
postgres=# GRANT SELECT ON my_credit TO public;
GRANT

おや、やっぱり何かおかしいようです。

postgres=# SET SESSION AUTHORIZATION alice;
SET
postgres=> SELECT * FROM my_credit;
 cname |       cmail       | cid |       number        |  expired
-------+-------------------+-----+---------------------+------------
 alice | alice@example.com | 101 | 1111-2222-3333-4444 | 2014-02-28
(1 row)

postgres=> SELECT * FROM my_credit WHERE f_leak(number);
NOTICE:  f_leak => 1111-2222-3333-4444
NOTICE:  f_leak => 5555-6666-7777-8888
NOTICE:  f_leak => 1234-5678-1234-5678
NOTICE:  f_leak => 0987-6543-2109-8765
 cname |       cmail       | cid |       number        |  expired
-------+-------------------+-----+---------------------+------------
 alice | alice@example.com | 101 | 1111-2222-3333-4444 | 2014-02-28
(1 row)

もう一度 EXPLAIN で実行計画を眺めてみましょう。

postgres=> EXPLAIN SELECT * FROM my_credit WHERE f_leak(number);
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=20.89..43.96 rows=2 width=104)
   Hash Cond: (credit.cid = customer.cid)
   ->  Seq Scan on credit  (cost=0.00..21.60 rows=387 width=40)
         Filter: f_leak(number)
   ->  Hash  (cost=20.85..20.85 rows=3 width=68)
         ->  Seq Scan on customer  (cost=0.00..20.85 rows=3 width=68)
               Filter: (cname = (getpgusername())::text)
(7 rows)

困ったことに、『creditテーブルをf_leak()条件でスキャンした結果』と『customerテーブルをcname = getpgusername()条件でスキャンした結果』がJOINされています。

オプティマイザはJOINすべき行を最小化するよう条件句を分配するのですが、f_leak()関数は credit テーブルの number 列のみ、cname = getpgusername() 条件は customer テーブルの cname 列のみに依存しています。そのため、JOINの完了を待つ事なく個々のテーブルをスキャンする時点で条件句を実行した方が、JOINすべき行数を減らす事ができます。

その結果、副作用を持つf_leak()がcreditテーブルのスキャン計画に push-down され、最初の例と同様に、不可視であるべき行の内容がf_leak()に渡され、それが利用者に漏えいしてしまっています。

この２つの問題は共に、オプティマイザがVIEWの境界を越えて関数の実行順序を入れ替えている事が原因です。これは性能観点からは優れた実装ですが、セキュリティを目的としたVIEW定義という観点では問題です。

一方で、VIEW内部で使われている関数を全て評価してから、その外部から与えられた関数を評価するという実装は、安全ですが、性能上無視できない性能劣化をもたらします。例えば、1万行 x 1万行のテーブルをJOINする場合、外部から与えられた関数をテーブルスキャンの時点で評価する事で片方の行数を1万行から100行に絞り込めるとしたら、9900万行分のJOIN処理を省略する事ができます。

次に、PostgreSQL v9.2に向けて提案されている Leaky View 問題への対策を紹介しましょう。

VIEW の security_barrier 属性と最適化の抑制

ここからは、私の提案している「Fix Leaky View Problemパッチ」の解説です。

前節で考察したように、VIEWを行レベルアクセス制御の目的で利用する場合には、パフォーマンスとセキュリティのトレードオフが存在します。安全側に倒せば許容できない程の性能劣化を招く可能性があり、一方、性能最適であれば情報漏えいの危険があります。

Fix Leaky Views Problem パッチは、CREATE VIEW構文でWITH(...)句を用いてオプション値を指定することを許容します。構文は以下の通りです。

CREATE VIEW view_name [WITH (options[,...])] AS select_statement;
options:
  security_barrier[= true|false]

security_barrier オプションは、VIEWが行レベルアクセス制御を目的として定義されていることを示す属性です。これを指定することで、一部のクエリ最適化を抑制する事が可能になります。

この設計に至るまでには長い議論があったのですが、結局、パフォーマンスとセキュリティのどちらが重要であるのかを判断できるのはVIEWを定義する人のみである、というシンプルな結論にたどり着いたのでした。

VIEWにsecurity_barrier属性が付与されている時、VIEWの内側で使用されている全ての関数・条件句は、VIEWの外側から与えられた関数・条件句よりも先に実行される事が保証されます。

では、実際に試してみましょう。以下で定義する my_account_secure と my_credit_secure は、先ほどの2つの例で使用したVIEWにsecurity_barrier属性を付加したものです。

postgres=# CREATE VIEW my_credit_secure WITH (security_barrier) AS
           SELECT cname, cmail, credit.* FROM customer NATURAL JOIN credit
           WHERE cname = getpgusername();
CREATE VIEW
postgres=# GRANT SELECT ON my_account_secure TO public;
GRANT
postgres=# CREATE VIEW my_account_secure WITH (security_barrier) AS
           SELECT * FROM customer WHERE cname = getpgusername();
CREATE VIEW
postgres=# GRANT SELECT ON my_credit_secure TO public;
GRANT

動作結果は以下のようになりました。"f_leak => ..." と表示されている内容は、クエリによって本来参照可能なデータの範囲内に収まっている事が分かります。

postgres=# SET SESSION AUTHORIZATION alice;
SET
postgres=> SELECT * FROM my_account_secure WHERE f_leak(cmail);
NOTICE:  f_leak => alice@example.com
 cid | cname |       cmail       | cpasswd
-----+-------+-------------------+---------
 101 | alice | alice@example.com | abcdef
(1 row)

postgres=> SELECT * FROM my_credit_secure WHERE f_leak(number);
NOTICE:  f_leak => 1111-2222-3333-4444
 cname |       cmail       | cid |       number        |  expired
-------+-------------------+-----+---------------------+------------
 alice | alice@example.com | 101 | 1111-2222-3333-4444 | 2014-02-28
(1 row)

では、VIEWにsecurity_barrier属性を付加することで、クエリ実行計画にどのように変化しているのでしょうか？先ほどと同じように、EXPLAIN構文で調べてみましょう。

postgres=> EXPLAIN SELECT * FROM my_account_secure WHERE f_leak(cmail);
                               QUERY PLAN
-------------------------------------------------------------------------
 Subquery Scan on my_account_secure  (cost=0.00..20.88 rows=1 width=100)
   Filter: f_leak(my_account_secure.cmail)
   ->  Seq Scan on customer  (cost=0.00..20.85 rows=3 width=100)
         Filter: (cname = (getpgusername())::text)
(4 rows)

f_leak()関数の評価は cname = getpgusername() 条件で customer テーブルをスキャンした後に行われる事が分かります。オプティマイザは security_viwe 属性を持ったVIEWの内側に条件句を push-down しなくなりました。

もう一つの例も同様です。

postgres=> EXPLAIN SELECT * FROM my_credit_secure WHERE f_leak(cmail);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Subquery Scan on my_credit_secure  (cost=20.89..46.96 rows=2 width=104)
   Filter: f_leak(my_credit_secure.cmail)
   ->  Hash Join  (cost=20.89..46.90 rows=6 width=104)
         Hash Cond: (credit.cid = customer.cid)
         ->  Seq Scan on credit  (cost=0.00..21.60 rows=1160 width=40)
         ->  Hash  (cost=20.85..20.85 rows=3 width=68)
               ->  Seq Scan on customer  (cost=0.00..20.85 rows=3 width=68)
                     Filter: (cname = (getpgusername())::text)
(8 rows)

パッチ自体の動作原理は極めて単純です。

PostgreSQLは、一旦、VIEWに対するクエリを内部的にサブクエリに書き換えます。その後、オプティマイザがクエリ実行計画を作成する際に、"シンプル"なサブクエリ（OFFSET/LIMIT句を含まない…など）であれば、性能最適の観点からサブクエリをJOINを用いてフラット化(Pull-Up)します。

その後で、条件句はオプティマイザによって性能上最適な位置に振り分けられるため、VIEWの内側/外側といった区別はもはや意味を持たなくなります。

VIEWのsecurity_barrier属性は、この際の条件に作用します。RangeTblEntry構造体のsecurity_barrierは、関連するサブクエリがVIEWに由来し、かつ、VIEWのsecurity_barrier属性がtrueである場合にセットされます。

以下の処理では、security_barrier属性が false だとpull_up_simple_subquery()は呼ばれないため、サブクエリのフラット化は抑制されます。

--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -543,6 +543,7 @@ pull_up_subqueries(PlannerInfo *root, Node *jtnode,
         */
        if (rte->rtekind == RTE_SUBQUERY &&
            is_simple_subquery(rte->subquery) &&
+           !rte->security_barrier &&
            (containing_appendrel == NULL ||
             is_safe_append_member(rte->subquery)))
            return pull_up_simple_subquery(root, jtnode, rte,

さらにもう一ヶ所。条件句に与える引数が特定のサブクエリにだけ依存している場合、オプティマイザはこの条件句の実行をサブクエリ処理の中に移動(Push-Down)しようとしますが、同様にサブクエリが security_view 属性つきのVIEWに由来する時は、これをスキップします。

@@ -763,6 +769,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
      Node       *clause = (Node *) rinfo->clause;

      if (!rinfo->pseudoconstant &&
+         !rte->security_barrier &&
          qual_is_pushdown_safe(subquery, rti, clause, differentTypes))
      {
          /* Push it down */

この２ヶ所の処理を追加することによって、これまで見たような、VIEWを行レベルアクセス制御の目的に使用する場合の問題を回避する事ができます。

FUNCTION の leakproof 属性

Leaky View問題はVIEWのsecurity_barrier属性によって解決する事ができるのですが、これは一部のクエリ最適化を無効化するために、場合によっては、そのためのコストが看過できないほど大きい事もあります。

例えば、アプリケーションの設計上、以下のようなVIEWを定義し、VIEWの外側から条件句（主キーによる絞込みなど）を与えて使いたいというケースを考えてみましょう。

CREATE VIEW valid_credit WITH (security_barrier) AS
    SELECT * FROM credit WHERE card_is_valid(number, expired);

SELECT * FROM valid_credit WHERE cid = <customer-id>;

この場合、card_is_valid関数と、VIEWの外部から与えた cid = <customer-id> を用いて credit テーブルをスキャンした結果が利用者には返されます。ですが、VIEWにはsecurity_barrier属性が設定されているため、常にcard_is_valid関数が先に実行されます。

この制限は cid 列にインデックスが設定されていても同様です。したがってインデックス・スキャンが選択されるべき状況でも全件スキャンが選択されてしまいます。ああ困った、困った。

Fix Leaky View ProblemパッチはPart-1とPart-2から構成されており、Part-1は前述の security_barrier 属性の実装を、Part-2ではその例外を設定する機能を実装しています。

Part-2によって、先ほどのオプティマイザへの変更は一部修正されます。

--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -769,7 +769,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
        Node       *clause = (Node *) rinfo->clause;

        if (!rinfo->pseudoconstant &&
-           !rte->security_barrier &&
+           (!rte->security_barrier ||
+            !contain_leakable_functions(clause)) &&
            qual_is_pushdown_safe(subquery, rti, clause, differentTypes))
        {
            /* Push it down */

サブクエリがsecurity_barrier属性付きのVIEWに由来するとき、このif文は条件句のPush-Downを抑止しますが、Part-2パッチは条件句(clause)が leakable-functions （つまり情報を漏えいする可能性のある関数）を含んでいなければ、サブクエリへの条件句のPush-Downを許可するように修正します。

では、関数が情報を漏えいする可能性の有無をどのように設定するか。

それには、CREATE FUNCTION構文に新たに追加されるLEAKPROOF属性を使用します。

例えば、以下のように使用します。LEAKPROOFを指定することで、この関数に情報漏えいの恐れがないという事を明示的に指定できますが、これは同時に、潜在的に不可視の行の内容を参照することを可能にするため、関数のLEAKPROOF属性をセットするには特権ユーザの権限が必要です。

（SE-PostgreSQLでも db_procedure:{install}権限をチェックする予定です）

CREATE FUNCTION is_positive(int) RETURNS bool LANGUAGE plpgsql
    LEAKPROOF
    AS 'BEGIN RETURN $1 > 0; END';

一部のビルトイン関数の中でも、明らかに情報漏えいのリスクがない関数については、デフォルトでLEAKPROOF属性がセットされています。

（全部で2400個程あるため、網羅的なチェックはこれからですが…。）

例えば、32bit Integer同士の大小比較を行う int4gt 関数は、以下のように実装されています。

Datum
int4gt(PG_FUNCTION_ARGS)
{
    int32       arg1 = PG_GETARG_INT32(0);
    int32       arg2 = PG_GETARG_INT32(1);

    PG_RETURN_BOOL(arg1 > arg2);
}

この実装に情報漏えいの危険はありませんので、DB初期化時にLEAKPROOF属性はセットされています。

その他にも、現在のパッチでは各種ビルトインタイプの等価・大小比較演算子の実装として利用されている関数にLEAKPROOF属性がついています。実際に試してみましょう。

postgres=# SET SESSION AUTHORIZATION bob;
SET
postgres=> SELECT * FROM my_credit;
 cname |      cmail      | cid |       number        |  expired
-------+-----------------+-----+---------------------+------------
 bob   | bob@example.com | 102 | 5555-6666-7777-8888 | 2013-10-30
 bob   | bob@example.com | 102 | 1234-5678-1234-5678 | 2015-06-30
(2 rows)

ユーザ bob は2枚のクレジットカードを持っています。リッチメンですね。

では、２つの条件句を付加してみます。一つは先ほどのf_leak()関数、もう一つは expired < '2014-01-01' という Date 型の大小比較演算です。

postgres=> SELECT * FROM my_credit_secure WHERE f_leak(number) AND expired < '2014-01-01';
NOTICE:  f_leak => 5555-6666-7777-8888
 cname |      cmail      | cid |       number        |  expired
-------+-----------------+-----+---------------------+------------
 bob   | bob@example.com | 102 | 5555-6666-7777-8888 | 2013-10-30
(1 row)

NOTICEメッセージが一行だけ表示されているという事は、大小比較演算はf_leak()関数よりも先に実行されたようです。EXPLAINで実行計画を見てみましょう。

postgres=> EXPLAIN SELECT * FROM my_credit_secure WHERE f_leak(number) AND expired < '2014-01-01';
                                QUERY PLAN
---------------------------------------------------------------------------
 Subquery Scan on my_credit_secure  (cost=1.06..27.06 rows=1 width=104)
   Filter: f_leak(my_credit_secure.number)
   ->  Hash Join  (cost=1.06..27.04 rows=2 width=104)
         Hash Cond: (credit.cid = customer.cid)
         ->  Seq Scan on credit  (cost=0.00..24.50 rows=387 width=40)
               Filter: (expired < '2014-01-01'::date)
         ->  Hash  (cost=1.05..1.05 rows=1 width=68)
               ->  Seq Scan on customer  (cost=0.00..1.05 rows=1 width=68)
                     Filter: (cname = (getpgusername())::text)
(9 rows)

見ての通り、expired < '2014-01-01' 条件句が credit テーブルのスキャンに結びついているのと比較して、f_leak()関数はmy_credit_secureビューの内側にPush-Downされていません。これが LEAKPROOF 属性の有無による違いです。もし credit テーブルにインデックスが設定されていれば、Push-Downされた条件句により、全件スキャンの代わりにインデックス・スキャンが選択されるかもしれません。

まとめ

確かこの問題は、かれこれ2年以上議論を続けてきた息の長い問題です。

2009年9月4日のセキュアOS塾『SE-PostgreSQL vs Oracle Label Security』の資料の中で言及があります。（p.34）

http://sepgsql.googlecode.com/files/090904-jsosjk04-sepgsql-vs-ols.pdf

開発コミュニティとしての方向性は、概ね上で紹介した形で収束しつつありますが、まだ v9.2 の新機能として紹介できるかどうか、は分からない状況です。が、SE-PostgreSQLの行レベルアクセス制御機能を実現するためにもマージしておきたい機能ですので、なんとかcommitできるよう頑張りたいところです。

最後に『じゃあ、既存のシステムではどうやって対策したら良いのよ？』という質問に対して一つTIPSを紹介しておきたいと思います。

Q. PostgreSQL v9.1以前のバージョンでLeaky View問題を防ぐにはどうしたらよいか？

A. クエリに OFFSET 0 を付ける

オプティマイザがサブクエリをフラット化、または、条件句をPush-Downする時、サブクエリにOFFSET/LIMIT句が含まれている場合はそれを断念する、という事を思い出してください。

OFFSET 0は結果セットの先頭から値を読むという意味ですので、本来は何の意味もありません。ですが、ここまで説明した条件句の実行順序に起因する問題を防ぐには簡便な方法です。

ただし、関数のLEAKPROOF属性に相当する機能はありませんので、その点でトレードオフは必要になります。

---

PostgreSQL Advent Calendar向けに記事を書くにあたり、MySQL、MS SQL Server、Oracle Databaseなど他のRDBMSの挙動はどうなっているのか調べたかったのですが、時間がありませんでした。特に Oracle は勝手にWHERE句に条件をくっつけるVirtual Private Databaseという機能を持っていますので気になります。

これらは、追って調査したいと思います。きっと。いつの日か。アディオス、アミーゴ。

さて、翌 12/5(月) は笠原さんです。よろしく〜

PostgreSQL Advent Calendar 12/3 です。

PostgreSQL 9.1ではSQL/MED規格の一部である 外部データラッパ (FDW: Foreign Data Wrapper) がサポートされました。これを使うと、Postgresの普通のテーブル以外の外部リソースもテーブルの形で検索、参照ができます。その拡張機能の一つに www_fdw があります。これは、Web APIの結果をテーブルとして取得するための、汎用フレームワークです。今回は、この www_fdw を使って、Google API のジオコーディング (地名 → 緯度・経度 変換) をしてみます。

環境構築

PostgreSQL 9.2dev で試しましたが、もちろん 9.1 でも動作すると思います。なお、XML機能を使うので、PostgreSQL本体を野良ビルドしている方は configure 時に --with-libxml をお忘れなく。

www_fdw を PGXN から取得するため、pgxn client を先にインストールしています。その後、www_fdw のダウンロード & インストールをし、テスト用に www_db データベースにログインします。

$ sudo easy_install pgxnclient
$ sudo yum install curl-devel
$ pgxn install www_fdw
$ pg_ctl start
$ createdb www_db
$ psql -d www_db

もし pgxn install の際に pg_config が見つからない旨のエラーが出る場合には、PATHが初期値にリセットされてるのかもしれません。自分は一時的にデフォルトパスに pg_config を置いてごまかしました。

$ sudo ln -s $PGDIR/9.2/bin/pg_config /bin/pg_config

Google Geocoding FDW の作成

www_fdw は Web API を使うための汎用フレームワークで、以下の2つをやってくれます。個別のAPIに対応させるには若干のカスタマイズが必要で、特に変換関数を用意するケースは多いと思われます。

• WHERE句の条件をURLに変換し、HTTPリクエストを投げる。
• HTTPレスポンスをテーブルにマッピングするために、変換関数を通す。

まずはEXTENSIONをインストールし、外部サーバを定義します。Google ジオコーディングAPIでは結果の形式が XML と JSON を選べますが、今回は、後で値を取り出せるよう xml を指定しておきます。なお、plv8jsを使えば、JSONからでも手軽に値を取り出せるかもしれません。

=# CREATE EXTENSION www_fdw;
=# CREATE SERVER google_geo_server FOREIGN DATA WRAPPER www_fdw OPTIONS (
     uri 'http://maps.google.com/maps/geo?output=xml',
     response_type 'xml',
     response_deserialize_callback 'google_geo_deserialize'
   );
=# CREATE USER MAPPING FOR current_user SERVER google_geo_server;
=# CREATE FOREIGN TABLE google_geo (
     q        text,
     address  text,
     north    float8,
     south    float8,
     east     float8,
     west     float8
   ) SERVER google_geo_server;

さて、マッピングの中心になるのが google_geo_deserialize 関数です。今回はXML出力を想定しているので、(xml型) ⇒ (テーブル型) の変換を行います。若干ごちゃごちゃしていますが、実際には、愚直にXMLからフィールドを取り出しているだけです。

=# CREATE FUNCTION google_geo_deserialize(options WWWFdwOptions, response xml)
    RETURNS SETOF google_geo AS $$
SELECT
  (
    (xpath('/ns:kml/ns:Response/ns:name/text()', $2,
       ARRAY[ARRAY['ns', 'http://earth.google.com/kml/2.0']]))[1]::text,
    (xpath('/Placemark/address/text()', x))[1]::text,
    (xpath('/Placemark/ExtendedData/LatLonBox/@north', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@south', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@east', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@west', x))[1]::text::float8
  )::google_geo
FROM unnest(
  xpath('/ns:kml/ns:Response/ns:Placemark', $2,
    ARRAY[ARRAY['ns', 'http://earth.google.com/kml/2.0']])
) AS x
$$ LANGUAGE sql IMMUTABLE STRICT;

ジオコーディングを使ってみる

それでは実際に google_geo 外部表にアクセスしてみます。列 q に地名がずらりと並んでいるような仮想的なテーブルを想像してください。その中の地名の1つを取り出すイメージです。

=# \x
=# SELECT * FROM google_geo WHERE q = '東京タワー';
-[ RECORD 1 ]---------------------------------------
q       | 東京タワー
address | Tokyo Tower, Minato, Tokyo 105-0011, Japan
north   | 35.6676749
south   | 35.6495431
east    | 139.7614544
west    | 139.7294396

無事、緯度経度が取り出せました。さらに座標で検索を行う場合には point や box 型として扱ってもよいですね。

まとめ

www_fdw を使うと、PostgreSQL から直接 Web API を使うことができます。「入力はURLパラメータ、出力はXML」というタイプのAPIならば手間無く扱えます。また、それら以外の形式でも入出力のマッピング関数をカスタマイズできます。汎用フレームワークのため、ぎりぎりのチューニングは難しいかもしれませんが、HTTPを話せる多くのサービスと繋げられるのは夢が膨らみますね。twitter_fdw on www_fdw や、HTTPサーバモードのあるNoSQL DB (groonga 等) への簡易的なアクセス方法としても使えそうです。

PostgreSQL Advent Calendar 12/2。もちろん日本時間で進行してます。

さてあまりディープな話しても仕方ないかなと思い、今話題絶頂渋谷女子高生の間でも噂になっていると言われるHeroku Postgresを早速使ってみた感想。ご存じない方のために念のため説明しておくと、HerokuがクラウドサービスとしてPostgreSQL単品で使えるサービスをこの間から提供しています。Database As A Serviceというやつか。

Heroku Postgres

まずはログイン。Webサービスの方は無料から始められるけど、Postgresは最低月200ドルの有料サービスなのでクレジットカードを登録。Postgresの方とWebとでシングルサインオンです。

ログインしてデータベース作成。マジで２クリックぐらいです。できあがるとこんな感じ。

フルAjaxでさくさく動きます。データベース名やユーザ名、パスワードはランダム文字列が勝ってに割り振られますが、右上のConnection Settingの矢印からpsqlを選ぶとコピペでそのままシェルキックできる文字列を作ってくれます。ついでにJDBCやActiveRecord用文字列も作ってくれます。

SSL必須なのでソースインストールでpsql使っているようなマニアックな方はぜひ--with-opensslでビルドしなおしてください。っていうかこのオプション指定したことなかったアルよ。あ、おしゃれな六本木のお兄さんはもちろんpgAdminとかで接続してね。

psqlでログインすると何のからくりもないバニラPostgresです。\cでtemplate1にもつながります。おもむろにCREATE DATABASEすると権限がないとおこられます。\lはこんな感じ。

dv2jnpm7g28edxm=> \l
                                               List of databases
      Name       |      Owner      | Encoding |   Collate   |    Ctype    |          Access privileges          
-----------------+-----------------+----------+-------------+-------------+-------------------------------------
 dv2jnpm7g28edxm | uv25d4bx5cii4b0 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | uv25d4bx5cii4b0=CTc/uv25d4bx5cii4b0+
                 |                 |          |             |             | collectd=c/uv25d4bx5cii4b0
 postgres        | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres
 template0       | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                        +
                 |                 |          |             |             | postgres=CTc/postgres
 template1       | postgres        | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                        +
                 |                 |          |             |             | postgres=CTc/postgres
(4 rows)

9.0でDO構文使えますので

dv2jnpm7g28edxm=> do $$ declare begin raise 'hoge'; end; $$;
ERROR:  hoge

とかも動きました。

システムカタログもpg_authidとか以外は普通に見えるので、pg_pltemplateをのぞいてみました。

dv2jnpm7g28edxm=> select * from pg_pltemplate;
  tmplname  | tmpltrusted | tmpldbacreate |      tmplhandler       |        tmplinline        |   tmplvalidator   |    tmpllibrary    | tmplacl 
------------+-------------+---------------+------------------------+--------------------------+-------------------+-------------------+---------
 plpgsql    | t           | t             | plpgsql_call_handler   | plpgsql_inline_handler   | plpgsql_validator | $libdir/plpgsql   | 
 pltcl      | t           | t             | pltcl_call_handler     |                          |                   | $libdir/pltcl     | 
 pltclu     | f           | f             | pltclu_call_handler    |                          |                   | $libdir/pltcl     | 
 plperl     | t           | t             | plperl_call_handler    | plperl_inline_handler    | plperl_validator  | $libdir/plperl    | 
 plperlu    | f           | f             | plperl_call_handler    | plperl_inline_handler    | plperl_validator  | $libdir/plperl    | 
 plpythonu  | f           | f             | plpython_call_handler  | plpython_inline_handler  |                   | $libdir/plpython  | 
 plpython2u | f           | f             | plpython_call_handler  | plpython_inline_handler  |                   | $libdir/plpython2 | 
 plpython3u | f           | f             | plpython3_call_handler | plpython3_inline_handler |                   | $libdir/plpython3 | 
(8 rows)

ん？先日の話だと「安全じゃない(untrusted)言語なんて導入できない」とか言ってたような。

dv2jnpm7g28edxm=> create language plperl;
ERROR:  could not access file "$libdir/plperl": No such file or directory

なるほど。共有オブジェクト置いてないんですね。っていうかそれならpg_pltemplateからはずしとけよって気もしますが。パッケージインストールなのかな？

そのうちここにplv8jsが並ぶわけですね。わかります。

dv2jnpm7g28edxm=> create table t(a, b, c) as select i, i % 100, repeat('x', 100 + i % 100) from generate_series(1, 1000000)i;
SELECT 1000000
Time: 23165.819 ms

dv2jnpm7g28edxm=> select pg_size_pretty(pg_database_size('dv2jnpm7g28edxm'));
 pg_size_pretty 
----------------
 189 MB
(1 row)

Time: 107.376 ms

dv2jnpm7g28edxm=> select count(*) from t;
  count  
---------
 1000000
(1 row)

Time: 937.279 ms

手元のMacBookでやったらCTASは４秒ぐらいで終わったのでやっぱり書き込みはちょっと遅いですね。もちろんWAL設定が手元はデフォルトなんですが。ただ参照は手元と同じぐらいかちょっと早いぐらい。キャッシュが1.7Gでテーブル作成は自由にできて参照もこのぐらい出るのならば、なんかもうこれでいいかって気がします。ていうかこれで最低ランクなんですが、最高ランクのMechaって誰が月6400ドルも払って数十ギガのキャッシュのせるのか。面白いから用意してみたって匂いがぷんぷんするw。

さてさて目玉はレプリケーション。ポチットな。

一回失敗しましたが、二回目で完成。ログもAjaxかWebSocketか知らないけどWeb画面開いたまま更新されていきます。もちろんこのFollowerも一台につき200ドル／月。

バックアップもワンクリック。リストアは新しいDBインスタンスになる模様。金稼ぐなー。バックアップのダウンロードもリンクのクリックでダウンロード始まります。形式は-Fcですね。

あ、ちなみに料金は月極の時間単位なので、１１月３１日の夜にDB作って早速２ドル請求が来ました。逆に言うとこうして実験してインスタンス破棄すれば200ドルもいらないわけです。ていうかこのぐらいの性能でレプリケーション、バックアップ、リストア、ログ管理等等を全く気にしなくていいとすれば、月200でサーバ＋DBAなんか雇えないわけで、十分ペイする気がします。

そんなわけで長くなりましたが、Heroku Postgres使ってみました。

明日はitagaki.takahiroさんです。よろしう。

P.S.

pg_settingsをコピペしときます。興味のある人はご覧あれ。

dv2jnpm7g28edxm=> select name, setting from pg_settings;
              name               |                                                                  setting                                                                   
---------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 allow_system_table_mods         | off
 application_name                | psql
 archive_command                 | test -f /etc/postgresql/9.0/main/wal-e.d/ARCHIVING_OFF || envdir /etc/postgresql/9.0/resource6279_heroku_com/wal-e.d/env wal-e wal-push %p
 archive_mode                    | on
 archive_timeout                 | 60
 array_nulls                     | on
 authentication_timeout          | 60
 autovacuum                      | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold    | 50
 autovacuum_freeze_max_age       | 200000000
 autovacuum_max_workers          | 3
 autovacuum_naptime              | 60
 autovacuum_vacuum_cost_delay    | 20
 autovacuum_vacuum_cost_limit    | -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold     | 50
 backslash_quote                 | safe_encoding
 bgwriter_delay                  | 200
 bgwriter_lru_maxpages           | 100
 bgwriter_lru_multiplier         | 2
 block_size                      | 8192
 bonjour                         | off
 bonjour_name                    | 
 bytea_output                    | hex
 check_function_bodies           | on
 checkpoint_completion_target    | 0.7
 checkpoint_segments             | 40
 checkpoint_timeout              | 300
 checkpoint_warning              | 30
 client_encoding                 | UTF8
 client_min_messages             | notice
 commit_delay                    | 0
 commit_siblings                 | 5
 constraint_exclusion            | partition
 cpu_index_tuple_cost            | 0.001
 cpu_operator_cost               | 0.0005
 cpu_tuple_cost                  | 0.003
 cursor_tuple_fraction           | 0.1
 custom_variable_classes         | 
 DateStyle                       | ISO, MDY
 db_user_namespace               | off
 deadlock_timeout                | 1000
 debug_assertions                | off
 debug_pretty_print              | on
 debug_print_parse               | off
 debug_print_plan                | off
 debug_print_rewritten           | off
 default_statistics_target       | 100
 default_tablespace              | 
 default_text_search_config      | pg_catalog.english
 default_transaction_isolation   | read committed
 default_transaction_read_only   | off
 default_with_oids               | off
 effective_cache_size            | 191250
 effective_io_concurrency        | 1
 enable_bitmapscan               | on
 enable_hashagg                  | on
 enable_hashjoin                 | on
 enable_indexscan                | on
 enable_material                 | on
 enable_mergejoin                | on
 enable_nestloop                 | on
 enable_seqscan                  | on
 enable_sort                     | on
 enable_tidscan                  | on
 escape_string_warning           | on
 extra_float_digits              | 0
 from_collapse_limit             | 8
 fsync                           | on
 full_page_writes                | on
 geqo                            | on
 geqo_effort                     | 5
 geqo_generations                | 0
 geqo_pool_size                  | 0
 geqo_seed                       | 0
 geqo_selection_bias             | 2
 geqo_threshold                  | 12
 gin_fuzzy_search_limit          | 0
 hot_standby                     | on
 ignore_system_indexes           | off
 integer_datetimes               | on
 IntervalStyle                   | postgres
 join_collapse_limit             | 8
 krb_caseins_users               | off
 krb_srvname                     | postgres
 lc_collate                      | en_US.UTF-8
 lc_ctype                        | en_US.UTF-8
 lc_messages                     | en_US.UTF-8
 lc_monetary                     | en_US.UTF-8
 lc_numeric                      | en_US.UTF-8
 lc_time                         | en_US.UTF-8
 listen_addresses                | *
 lo_compat_privileges            | off
 local_preload_libraries         | 
 log_autovacuum_min_duration     | -1
 log_checkpoints                 | on
 log_connections                 | off
 log_destination                 | syslog
 log_disconnections              | off
 log_duration                    | off
 log_error_verbosity             | default
 log_executor_stats              | off
 log_hostname                    | off
 log_line_prefix                 | %u [ONYX] 
 log_lock_waits                  | off
 log_min_duration_statement      | 50
 log_min_error_statement         | error
 log_min_messages                | notice
 log_parser_stats                | off
 log_planner_stats               | off
 log_rotation_age                | 1440
 log_rotation_size               | 10240
 log_statement                   | none
 log_statement_stats             | off
 log_temp_files                  | -1
 log_timezone                    | UTC
 log_truncate_on_rotation        | off
 logging_collector               | on
 maintenance_work_mem            | 65536
 max_connections                 | 500
 max_files_per_process           | 1000
 max_function_args               | 100
 max_identifier_length           | 63
 max_index_keys                  | 32
 max_locks_per_transaction       | 64
 max_prepared_transactions       | 500
 max_stack_depth                 | 2048
 max_standby_archive_delay       | -1
 max_standby_streaming_delay     | -1
 max_wal_senders                 | 10
 password_encryption             | on
 plpgsql.variable_conflict       | error
 port                            | 5432
 post_auth_delay                 | 0
 pre_auth_delay                  | 0
 random_page_cost                | 4
 search_path                     | "$user",public
 segment_size                    | 131072
 seq_page_cost                   | 1
 server_encoding                 | UTF8
 server_version                  | 9.0.5
 server_version_num              | 90005
 session_replication_role        | origin
 shared_buffers                  | 53120
 silent_mode                     | off
 sql_inheritance                 | on
 ssl                             | on
 ssl_renegotiation_limit         | 524288
 standard_conforming_strings     | off
 statement_timeout               | 0
 superuser_reserved_connections  | 3
 synchronize_seqscans            | on
 synchronous_commit              | on
 syslog_facility                 | local0
 syslog_ident                    | resource6279_heroku_com
 tcp_keepalives_count            | 9
 tcp_keepalives_idle             | 7200
 tcp_keepalives_interval         | 75
 temp_buffers                    | 1024
 temp_tablespaces                | 
 TimeZone                        | UTC
 timezone_abbreviations          | Default
 trace_notify                    | off
 trace_recovery_messages         | log
 trace_sort                      | off
 track_activities                | on
 track_activity_query_size       | 1024
 track_counts                    | on
 track_functions                 | none
 transaction_isolation           | read committed
 transaction_read_only           | off
 transform_null_equals           | off
 unix_socket_group               | 
 unix_socket_permissions         | 511
 update_process_title            | on
 vacuum_cost_delay               | 0
 vacuum_cost_limit               | 200
 vacuum_cost_page_dirty          | 20
 vacuum_cost_page_hit            | 1
 vacuum_cost_page_miss           | 10
 vacuum_defer_cleanup_age        | 0
 vacuum_freeze_min_age           | 50000000
 vacuum_freeze_table_age         | 150000000
 wal_block_size                  | 8192
 wal_buffers                     | 1024
 wal_keep_segments               | 127
 wal_level                       | hot_standby
 wal_segment_size                | 2048
 wal_sender_delay                | 200
 wal_sync_method                 | fdatasync
 wal_writer_delay                | 200
 work_mem                        | 102400
 xmlbinary                       | base64
 xmloption                       | content
 zero_damaged_pages              | off
(196 rows)

うーん。shared_buffers=53120ってことは8KBかけて400MBですね。キャッシュって何のことなんだろう。。。

まあ今に始まったことではないかもしれませんが。。。

SQL Server 2012の全貌が見えてきた！新ライセンス体系、高可用性、見える化、インメモリ、そしてビッグデータ（2/3）:企業のIT・経営・ビジネスをつなぐ情報サイト EnterpriseZine (EZ)

Enterpriseエディションならではの機能としてカラムベースのインデックス処理技術である「カラムストアインデックス」がある。Excel/SharePointのアドインである多次元インメモリ機能「PowerPivot」のカラムベースエンジン(VertiPack)をベースに、列(カラム)単位のインデックス格納を実現、データの圧縮率を大幅に高め、パフォーマンスを劇的に向上させる機能だ。斎藤氏によれば「件数にもよるが、フルテーブルスキャンに比べて100倍以上は高速になる」とのこと。日次集計など参照処理を行うデータウェアハウジングにおいて大幅な処理性能向上が期待できる。

カラム単位のインデックスって普通じゃね？インデックスって普通カラムに対して貼るものだよね？？五味さんって以前もPostgreSQLかなんかの記事で意味不明なことを書いていた記憶があるからまあ記者なんてそんなもんだよねと思っていたら、Microsoftのページにも「Columnstore Index」って書いてあったなんだかなと思ったり。

SQL Server Columnstore Index FAQ - TechNet Articles - Home - TechNet Wiki

でよく読んでみると列圧縮（カラムコンプレッション）のことなんだと思う。ただしGreenplumのそれと違って元のテーブルはそのまま保存して列圧縮したデータを別に作るんだろうと思われる。なので「インデックス」と呼んでいるんだと思うんだけど、それって本質的にインデックスじゃないよね？？　どっちかっていうとVerticaのマテリアライズドビューに近い気がする。だいたいあれですよ、Clustered Indexって奴だって、それ意味あるの？？みたいな話でしょ。

ちなみに列圧縮はBIデータベースでは割とホットでして、元々列指向のVerticaやSybaseはともかく、Netizza、Teradata、Greenplumもサポート（予定）しており、ないと列強に加われないという明治時代の軍艦みたいなものです。あ、Oracle（Exadata）もサポートするとかしたとか。

Teradata Columnar and Teradata 14 compression : DBMS?2 : DataBase Management System Services

Jim Gray亡き今というか彼がいたからSQL ServerがBIっぽくなっちゃっただけで、MySQLみたいに「そういうのは関係ないですから」っていう方向を進めばよかったのに。

ジム・グレイ - Wikipedia

閑話休題。

AlwaysOnでは、最大4台のセカンダリサーバに複製可能で、自動/手動によるフェールオーバーが可能だ。セカンダリは同期モードと非同期モードに設定することができ、同期モードに設定すると「アクティブセカンダリ」として稼働させることができる。いつでもプライマリに取って代われるセカンダリ、という位置づけだ。これにより処理を負荷分散させながらコンピューティングリソースを使うことが可能で、高い可用性を維持できるという。デモでは東京⇔大阪間でのフェールオーバーが行われた場合、ダウンタイム15秒程度でセカンダリがプライマリのロールを引き継いで稼働するデモが実演された。ほぼリアルタイムの同期といっていいだろう。

15秒！？15秒でリアルタイムって呼んでいいの？？何千件というトランザクションが消える（失敗する）と思うんですけど、いいんですね。それでいいんですね。WALをディスクに書くか、メモリに書くかですったもんだ議論しているPostgreSQLのレプリケーションはがんばりすぎですかね。もちろんフェイルオーバーが透過的にできるのは大事なことだと思いますけど、15秒ってひどくないですか。しかも最大4台とか。「カスケードしてもいい？」とかおちゃめなことを言ってるFujiiさんが泣きます。

まー総じてSQL Serverのテクノロジっていつの時代もこんなかんじで「えっ？」って話が多いような気がするけど、実際自分がその業界に行ってみてわかった、SQL ServerをBIで使ってるところはないに等しい（笑） Teradata、Oracle、Netizza、Greenplum、たまにSybaseとかAsterとかVerticaとかいう話は聞くけど、SQL Serverはないね。それでも大企業のバックエンドでWindowsサーバーと抱き合わせで使ってる人たち、かわいそうになあ、っていつも思うけど。Jとか。レプリケーションの怠慢っぷりに何度となく泣かされておりましたもので。まったくMSの誇大広告には目に余るものがある・・・

PG9.1と9.2の索引ページを比べて、9.2の新機能を拾ってみました。この他、マルチコアでのスケーラビリティが大幅に向上しているはずですが、性能なので索引には載りませんね。

• Range Types : 範囲型
• Cascading Replication : レプリケーションのカスケード構成
• index-only scan : いわゆる covering index
• pg_export_snapshot : MVCCスナップショットのエクスポート/インポート
• serial2, smallserial : 連番 (1..32767)
• temp_file_limit : 一時ファイルのクォータ

時間があるときに追ってみます。

PostgreSQL 9.2 で「範囲型 (Range Types)」が採用されます。範囲型は、{ 下限, 上限 } をペアで扱うデータ型で、数直線上の「範囲」を表現することができます。また、配列と同様、要素として各種スカラー型を選べます。整数の範囲、数値の範囲、日時の範囲など、用途にあわせて型を選択できます。

9.0 で追加された「排他制約」は、これまでは主に地理データ (box, circle 等) の重なりのチェックに使えましたが、9.2 では「数値や時間の幅」を保証することもできるようになります。用途としては、「有効期限開始 / 終了」を持っているようなデータは結構多いような気がしますし、用途も広がったのではないでしょうか。

以下、範囲型、範囲型関数、排他制約を使った簡単な例です。[1] と [2] は、同じ部屋で予約時間帯が重なっているのでエラーになりますが、[1] と [3] は部屋が異なるので問題ないことがわかります。

=# CREATE EXTENSION btree_gist;
=# CREATE TABLE reservation (
     room   text,
     during tsrange, -- range of timestamp
     EXCLUDE USING gist (room WITH =, during WITH &&)
   );
=# INSERT INTO reservation VALUES ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)'); -- [1]
=# INSERT INTO reservation VALUES ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)'); -- [2]
ERROR:  conflicting key value violates exclusion constraint "reservation_room_during_excl"
=# INSERT INTO reservation VALUES ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)'); -- [3]
-- lower/upperで下限/上限を取り出し
=# SELECT room, lower(during), upper(during) FROM reservation;
 room |        lower        |        upper
------+---------------------+---------------------
 123A | 2010-01-01 14:00:00 | 2010-01-01 15:00:00
 123B | 2010-01-01 14:30:00 | 2010-01-01 15:30:00
(2 rows)

• Range Types
• Range Functions and Operators

SFPUG二回目。今日はHerokuオフィスで直々にPostgreSQLの話を聞いてきましたよ。

彼らはpure PostgreSQLを使っていて、ソースには全く手を入れていない。が、デモを見る限りAmazonにPostgreSQLインスタンスをワンクリックで立ち上げてWALもばっちり保存（自作のWAL-Eとやらを使ってるらしい）。ストリーミングレプリケーションとホットスタンバイもWebの管理画面からワンクリックで追加。DBのスナップショットもワンクリック。まさにこれぞ象の群れと呼ぶべきかもしれない。

写真貼りますけどオフィスはサンフランシスコ市中にあっておしゃれなレンガ作りの建物を３フロアゆったり使ってオサレな感じ。雰囲気的にはCookPadが近いと思うけど、Ruby使いはいつもオサレだね。そんなオサレな連中がPostgreSQLに注目しているというのがまたホット。MySQLには全く興味がないってさ。

そんな彼らが今最も注目しているのがpl/v8。今俺触ってないって何回も言ってるのに「よくするにはどうしたらいいか」「このプロジェクトこそが今我々に最も必要だ」などとアツく語られてしまいました。やっぱHerokuのターゲットが小〜中規模のWebアプリをさくさく作ることにあるため、JavaScriptこそが最も使い勝手がいいらしい。おまけに速い（と彼らは思ってる）。メンバーの一人が作ったプレゼン↓

http://pgeu-plv8.herokuapp.com/

そんなわけでシャレで始めたplv8ですがHerokuだけでなく随分いろんな人が興味持ってるらしいしplv8開発者は絶賛期待されております。9.2か9.3にはJSONデータ型も入れてHerokuのデファクトにしたいってさ。そうなるとpl/perlやpl/pythonどころじゃなくなるかもねー

んだば

今日はJPUGの勉強会のために市ヶ谷へ遠征。

市ヶ谷の駅で降りるのは初めてだ。

今日のお題は3つ。

• EXPLAINING EXPLAIN(3)
  • これまでのおさらい＋最新情報＋チューニング例
• ロックとWAL
  • さまざまなレイヤでの排他制御の話
  • LWLock横取り問題
  • かなりムズイ話だが、なかなか面白い。続きが聞きたいところ。
• 全文検索
  • 前回のおさらい
  • pg_tgrmの良い点・悪い点
    • 2文字以下だとインデックスが効かない問題があるのか・・・日本語だと厳しい。
  • textsearch_sennaの問題点(更新・削除に完全に対応出来ない)
  • みんなが求める全文検索は何か？というディスカッション

勉強後は恒例の懇親会。今回は2次会まで行く。

• 次回の発表にエントリしておく。テーマはFDW。
• 山手線内側はやっぱりオサレだよね・・・
• @cstyles_jp 氏の超絶な開発現場の話が面白すぎるｗ
  • でもそんな開発は俺には無理。技術的にも精神的にも無理。
• 本って書いてもなかなか売れないね～
  • そこで萌え絵コラボですよ。
  • 「もし女子高生がPostgreSQLのStorage Managerを読んだら」
    • 誰か書いてくれｗ
• アウェイで飲んで横浜へ戻るのはやっぱりきつい・・・
  • つーか、中央線がなんかgdgdだったし。
  • アキバで京浜東北線に乗り換えて帰還。磯子に運ばれないように意識を保つのが大変ｗ

10/29 (土) の「第21回しくみ分科会+アプリケーション分科会勉強会」で、全文検索の続きを話します。pg_trgmにも無視できない問題があった話と、そろそろ引退させたいものの代わりが見つからない textsearch_senna を中心に。

最近は大陸間で電話することが多くて「今あっち何時？」的な脳みそが足りない自分は苦労するわけです。タイムゾーンで時差を教えてくれる手っ取り早い方法がPostgreSQLに内蔵されたOlson Databaseをつつくこと。

-- PDT (Pacific Daylight Time)で何時だっけ？
SELECT timezone('PDT', '2011-10-17 15:40:00');
      timezone       
---------------------
 2011-10-16 23:40:00
(1 row)

-- その９時間後は？
SELECT timezone('PDT', '2011-10-17 15:40:00 JST'::timestamptz + interval '9 hours');
      timezone       
---------------------
 2011-10-17 08:40:00
(1 row)

タイムゾーンの指定の仕方はpg_timezone_names/pg_timezone_abbrevsから。

TABLE pg_timezone_names;
TABLE pg_timezone_abbrevs;

Olsonデータはだいぶ昔から採用しているみたいですが、実装はまるで最近のFDWですね。

余談ですが、世界で一番早く朝を迎えるのはどこでしょう。

SELECT * FROM pg_timezone_names ORDER BY utc_offset DESC LIMIT 3;
        name        | abbrev | utc_offset | is_dst 
--------------------+--------+------------+--------
 Etc/GMT-14         | GMT-14 | 14:00:00   | f
 Pacific/Kiritimati | LINT   | 14:00:00   | f
 Pacific/Chatham    | CHADT  | 13:45:00   | t
(3 rows)

Kiritimati、GMT+14ってフライングしすぎ。

というわけで、時間の計算は下手なアプリをダウンロードするよりpsqlの方がよっぽど早い。ぜひお試しあれ。

9.1の途中で動かなくなっていた件にやっと追従。意外とすんなりは行きませんでした。

http://archives.postgresql.org/pgsql-committers/2011-10/msg00125.php

• Relation->rd_operator がいつの間にかなくなっていた。
• INIT_FORKNUM, MAIN_FORKNUM に少なくとも1ページを作成しておかないと怒られる。

特に後者の仕様って、何のつもりなんでしょう？ファイルが無いと問題があるケースでも見つかったんでしたっけ？

また、EXTENSION 化も考えたんですが、現状インストールスクリプト内で、pg_am への行の追加をしているんですよね。CREATE EXTENSION で INSERT の発行は可能そうなのですが、DROP EXTENSION は単なる依存オブジェクトの DROP なので、DELETE を発行できなさそう。テーブルを書き換えるタイプの EXTENSION は、9.1時点ではうまく対応できない気がします。

PostgreSQL 9.1.0 が無事リリースされました。

• ニュースリリース
• Let's Postgres 解説

とりあえず一番の注意は、文字列のエスケープに関する「Change the default value of standard_conforming_strings to on」ですね。リリースノートにもでっかくWarningがあるんですが、そこまでしてまで設定を変えたかった部分なのか…。

最近は仮想化がらみで VMware vFabric や Red Hat Enterprise Virtualization 用のデータベースとしてニュースにものぼり、単に "Poor Man's Oracle" 以上の対抗馬になりつつあるんじゃないでしょうか。

「Foreign Data Wrapper for Oracle (Beta) released」というニュースが流れたので、チェックしてみました。

oracle_fdw は PostgreSQL 9.1 の SQL/MED API を利用した拡張機能 (EXTENSION) で、Oracle のテーブルを、PostgreSQL からビューのように扱うことができるようです。コードを見る限り WHERE 句の push-down (条件の引き継ぎ) を頑張っているようで、各種演算子や関数の一部も Oracle 側に SQL として受け渡せるとのこと。データ型変換も一通り揃っており、「ちょっと Oracle のデータを持ってきて PostgreSQL で加工/検索」という用途には十分使えそうです。

ライセンスもBSDなので、今後のC言語ベースの FDW のお手本にもなりそうです。ちなみに、mysql_fdw も開発中のようですが、こちらは未完成で、クエリも全部 "SELECT * FROM tbl" になってしまいます。そして、未だに postgresql_fdw (PostgreSQL → PostgreSQL) は出てきていないという不思議な状況……。

https://raw.github.com/mxcl/homebrew/c32bea0f198170da22bf31a3a0cb7522153da44e/Library/Formula/postgresql.rb

にあるFormulaをpostgresql8.rbとして保存して下記のように書き換える。

class Postgresql <Formula
→
class Postgresql8 <Formula

  url 'http://ftp2.uk.postgresql.org/sites/ftp.postgresql.org/source/v8.4.4/postgresql-8.4.4.tar.bz2’
→
  url 'http://ftp2.uk.postgresql.org/sites/ftp.postgresql.org/source/v8.4.8/postgresql-8.4.8.tar.bz2’

  md5 '4bf2448ad965bca3940df648c02194df'
→  
  md5 '4603e8ea30cee97189b62b39022f2043'

  depends_on 'libxml2' if MACOS_VERSION < 10.6 # Leopard libxml is too old
→
  depends_on 'libxml2' if MACOS_VERSION < 10.7 # Leopard libxml is too old

    ENV.libxml2 if MACOS_VERSION >= 10.6
→
    ENV.libxml2 if MACOS_VERSION >= 10.7

インストール、DB作成、手動起動、ログ参照、DB接続、シャットダウンしてみる。

/usr/local/bin% brew install postgresql8
/usr/local/bin% mkdir /Users/bose999/postgresql8-data
/usr/local/bin% mkdir /Users/bose999/postgresql8-log
/usr/local/bin% /usr/local/bin/initdb /Users/bose999/postgresql8-data
/usr/local/bin% /usr/local/bin/pg_ctl -D /Users/bose999/postgresql8-data -l /Users/bose999/postgresql8-log/server.log start
/usr/local/bin% cat /Users/bose999/postgresql8-log/server.log
LOG:  database system was shut down at 2011-07-27 09:01:25 JST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
/usr/local/bin% psql -d postgres
psql (8.4.8)
Type "help" for help.

postgres=# select * from user;
 current_user 
--------------
 bose999
(1 row)

postgres-# \q
/usr/local/bin% /usr/local/bin/pg_ctl -D /Users/matakeda/postgresql8-data stop -s -m fast