中国地方DB勉強会っていう控えめに言っても最高の勉強会があるんだけどそこで排他制約について教えてもらいました。
排他制約って雑に説明すると重なりを拒否する制約です。 僕は使った事なかったのですが勉強会の中で事例紹介を受けて、めっちゃ便利だったのでここでご紹介します。
どんなときに使うの？
実際にはどんなときに重なりを制御したいかというとよく使うのは次の2つ。
- 図面の重なり
- 時間の重なり
1つ目は幾何学的な図面を表現するときです。 実際にPostgreSQLは円や四角をSQLで表現できます。例えば地図上で特定の座標から半径100メートルの円を書き、その中に特定の円(場所)があればErrorにするような制約が書けます。 そもそもSQLで位置計算もめっちゃ便利なので是非使ってみてください。
そして2つ目が時間の範囲での重なりの表現です。
PostgreSQLには範囲型というのがあります。
これは時間や数値の範囲を1つのカラムで表現できます。
例えば有効期限を表現する時、一般的にはstart_atとend_atのそれぞれのカラムを作成するのではないでしょうか。
この場合、 今日が有効期限に含まれるかどうか の点と線の比較は簡単ですが 特定の期間が有効期限に含まれるかどうか という検索は非常に面倒です。
しかし範囲型を使うと 5 <@ int4range(1,7) のように <@ で範囲と範囲の含有が簡単に検索できます。このように色んな演算子を用意しているので簡単に表現できます。
実際の使い方
さて、ここまで読んで範囲型めっちゃ便利！！と思っていただけたと思います。 そこで例えば会議室の予約システムを作る時に範囲型を使うと次のように表現できます。
CREATE TABLE schedule
(
schedule_id SERIAL PRIMARY KEY NOT NULL,
room_name TEXT NOT NULL,
reservation_time tsrange NOT NULL
);
demo=# SELECT * FROM schedule;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
1 | soudai_room | ["2017-04-16 11:30:00","2017-04-16 12:00:00")
4 | soudai_room | ["2017-04-16 12:00:00","2017-04-16 12:30:00")
5 | soudai_room | ("2017-04-16 12:30:00","2017-04-16 12:40:00")
8 | soudai_room | ["2017-04-16 14:30:00","2017-04-16 16:00:00")
(4 行)
demo=# SELECT * FROM schedule
WHERE reservation_time @> '2017-04-16 15:30:00'::timestamp;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
8 | soudai_room | ["2017-04-16 14:30:00","2017-04-16 16:00:00")
(1 行)
demo=# SELECT * FROM schedule
WHERE
reservation_time && '[2017-04-16 11:45:00, 2017-04-16 12:10:00]'::tsrange;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
1 | soudai_room | ["2017-04-16 11:30:00","2017-04-16 12:00:00")
4 | soudai_room | ["2017-04-16 12:00:00","2017-04-16 12:30:00")
(2 行)
しかしここで大きな課題として会議室は1つしかないので会議室の予約時間(reservation_time)が被ったらErrorになって欲しいですよね。 そこでお待たせしました排他制約の出番です。
CREATE TABLE schedule
(
schedule_id SERIAL PRIMARY KEY NOT NULL,
room_name TEXT NOT NULL,
reservation_time tsrange NOT NULL,
EXCLUDE USING GIST (reservation_time WITH &&) ←排他制約を追加
);
demo=# SELECT * FROM schedule;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
1 | soudai_room | ["2017-04-16 11:30:00","2017-04-16 12:00:00")
4 | soudai_room | ["2017-04-16 12:00:00","2017-04-16 12:30:00")
5 | soudai_room | ("2017-04-16 12:30:00","2017-04-16 12:40:00")
8 | soudai_room | ["2017-04-16 14:30:00","2017-04-16 16:00:00")
(4 行)
demo=# INSERT INTO schedule
(room_name, reservation_time)
VALUES
('soudai_room', '[2017-04-16 15:30, 2017-04-16 17:00)');
ERROR: conflicting key value violates exclusion constraint "schedule_reservation_time_excl"
DETAIL: Key (reservation_time)=(["2017-04-16 15:30:00","2017-04-16 17:00:00")) conflicts with existing key (reservation_time)=(["2017-04-16 14:30:00","2017-04-16 16:00:00")).
demo=# INSERT INTO schedule
(room_name, reservation_time)
VALUES
('soudai_room', '[2017-04-16 17:30, 2017-04-16 18:00)');
INSERT 0 1
ちゃんと弾いてくれてますね！ これでUPDATEの設計にしてロックがアプリケーションパフォーマンスのボトルネックになったり、INSERTで表現する際のファントムーリード起因のbugとかを防ぐ事が出来ます。
まとめ
RDBに入っているデータは常に正しいといえる状態は精神衛生上もアプリケーションのロジック的にも平和なので排他制約使えるシーンは色々とあるな！と考えています。 とは言え僕も今回知ったばかりでまだまだ実務で使っていないので面白い使い方を模索したいと思います。
---- 2017/04/16 追記 -----
PostgreSQLで排他制約がめっちゃ便利！！ - そーだいなるらくがき帳b.hatena.ne.jpCREATE EXTENSION btree_gist; して、EXCLUDEに (room_name WITH =, reservation_time ...) ってすると、同一room_name内で時刻が重複してたらはじくとかもできますよ！(room_nameが違えば通る)
2017/04/16 20:31
めっちゃ便利！！！！！
---- 2017/04/16 追記 2 -----
PostgreSQLで排他制約がめっちゃ便利！！ - そーだいなるらくがき帳b.hatena.ne.jp
- [PostgreSQL]
- [knowhow]
単純な幾何学情報はともかく、地理的位置情報はPostGISも入れて扱うべきだというのはあまり知られていないのだろうか。
2017/04/16 22:05
みんなPostGISめっちゃ便利なのに知らない可能性あるのかって思ったので合わせて紹介します。
PostGISを使ってみよう | Let's POSTGRES
農研機構最高だぜ！（久々の定期ポスト
雑に説明するとPostGISは良い感じに幾何学的なことを出来るようにするPostgreSQLの拡張ライブラリの一つです。 例えば地球は球では無く楕円なので緯度経度から距離を計算する時に近似値計算ではズレが生じます。 なのでそこを正確に計算するためにはアレをコレしてと大変なのですがそこを良きに計らってくれたりします。 つまり正確な地図を表現したり、例えば町田は東京都に含まれるのか？みたいな複雑な形の確認も表現出来るようになります。 というわけで正確な地図を表現したい時にPostgreSQLのPostGISは最強なのでオススメです。 あと皆さんも知ってると思いますがMySQL 5.7からInnoDBでもGIS型をサポートしてます。 こちらは機能的にはまだまだPostGISほどでは無いものの、光る部分も沢山あるので将来楽しみな機能です。 ということで地図などで座標を扱う時にPostgreSQLめっちゃ便利ですのでこちらも合わせて覚えてください！！
