Slide 1
Slide 1 text
時間枠の扱いをいい感じにする話
～ 安全に範囲を保存するために ～
PostgreSQLアンカンファレンス
Slide 2
Slide 2 text
時間枠の扱い
What is it?
Slide 3
Slide 3 text
What is it?
id begin_at end_at room_id
1 2021-07-06 11:30:00 2021-07-06 12:00:00 1
2 2021-07-06 11:30:00 2021-07-06 12:00:00 1
3 2021-07-06 11:30:00 2021-07-06 12:00:00 2
4 2021-07-06 12:00:00 2021-07-06 12:59:59 1
5 2021-07-06 13:00:00 2021-07-06 14:00:00 1
6 2021-07-06 10:00:00 2021-07-06 15:00:00 1
Slide 4
Slide 4 text
What is it?
id begin_at end_at room_id
1 2021-07-06 11:30:00 2021-07-06 12:00:00 1
2 2021-07-06 11:30:00 2021-07-06 12:00:00 1
3 2021-07-06 11:30:00 2021-07-06 12:00:00 2
4 2021-07-06 12:00:00 2021-07-06 12:59:59 1
5 2021-07-06 13:00:00 2021-07-06 14:00:00 1
6 2021-07-06 10:00:00 2021-07-06 15:00:00 1
同じルームなのに重複してる
Slide 5
Slide 5 text
What is it?
id begin_at end_at room_id
1 2021-07-06 11:30:00 2021-07-06 12:00:00 1
2 2021-07-06 11:30:00 2021-07-06 12:00:00 1
3 2021-07-06 11:30:00 2021-07-06 12:00:00 2
4 2021-07-06 12:00:00 2021-07-06 12:59:59 1
5 2021-07-06 13:00:00 2021-07-06 14:00:00 1
6 2021-07-06 10:00:00 2021-07-06 15:00:00 1
12:00:00の瞬間が重複している
Slide 6
Slide 6 text
What is it?
id begin_at end_at room_id
1 2021-07-06 11:30:00 2021-07-06 12:00:00 1
2 2021-07-06 11:30:00 2021-07-06 12:00:00 1
3 2021-07-06 11:30:00 2021-07-06 12:00:00 2
4 2021-07-06 12:00:00 2021-07-06 12:59:59 1
5 2021-07-06 13:00:00 2021-07-06 14:00:00 1
6 2021-07-06 10:00:00 2021-07-06 15:00:00 1
beginもendも空いてる時間だが組み合わせると重複する
Slide 7
Slide 7 text
時間枠の扱い
↓
むずいし、バグが出るとクリティカル
What is it?
Slide 8
Slide 8 text
いい感じにするやつを紹介します
※個人の見解です
What is it?
Slide 9
Slide 9 text
1. 自己紹介
2. 範囲型と排他制約
3. チェック制約とストアドファンクション
4. いい感じに検索する
5. まとめ
あじぇんだ
Slide 10
Slide 10 text
1. 自己紹介
2. 範囲型と排他制約
3. チェック制約とストアドファンクション
4. いい感じに検索する
5. まとめ
あじぇんだ
Slide 11
Slide 11 text
自己紹介
曽根 壮大（36歳）
Have Fun Tech LLC 代表社員
そ ね たけ とも
● 日本PostgreSQLユーザ会 勉強会分科会 担当
● 3人の子供がいます（長女、次女、長男）
● 技術的にはWeb/LL言語/RDBMSが好きです
● コミュニティが好き
Slide 12
Slide 12 text
No content
Slide 13
Slide 13 text
本書きました
Slide 14
Slide 14 text
PostgreSQLカンファレンス
Slide 15
Slide 15 text
1. 自己紹介
2. 範囲型と排他制約
3. チェック制約とストアドファンクション
4. いい感じに検索する
5. まとめ
あじぇんだ
Slide 16
Slide 16 text
範囲型と排他制約
https://soudai.hatenablog.com/entry/2017/04/16/152905
Slide 17
Slide 17 text
範囲型と排他制約
id begin_at end_at room_id
1 2021-07-06 11:30:00 2021-07-06 12:00:00 1
2 2021-07-06 11:30:00 2021-07-06 12:00:00 1
3 2021-07-06 11:30:00 2021-07-06 12:00:00 2
4 2021-07-06 12:00:00 2021-07-06 12:59:59 1
5 2021-07-06 13:00:00 2021-07-06 14:00:00 1
6 2021-07-06 10:00:00 2021-07-06 15:00:00 1
こういうデータが防げる！
Slide 18
Slide 18 text
詳細はブログ見てください
範囲型と排他制約
Slide 19
Slide 19 text
1. 自己紹介
2. 範囲型と排他制約
3. チェック制約とストアドファンクション
4. いい感じに検索する
5. まとめ
あじぇんだ
Slide 20
Slide 20 text
？？？「部屋毎に時間枠が違うんだよね」
ルームid 1 は 10:00 - 12:00
ルームid 2 は 10:00 - 15:00
の中で予約可能、みたいなやつ
チェック制約とストアドファンクション
Slide 21
Slide 21 text
チェック制約とストアドファンクション
id schedule_at room_id
1 [2021-07-06 11:30:00,2021-07-06 12:00:00) 1
2 [2021-07-06 12:00:00,2021-07-06 13:00:00) 1
3 [2021-07-06 11:30:00,2021-07-06 12:00:00) 2
4 [2021-07-06 09:30:00,2021-07-06 12:00:00) 1
room_id room_at
1 [2021-07-06 10:30:00,2021-07-06 12:00:00)
2 [2021-07-06 10:00:00,2021-07-06 15:00:00)
Slide 22
Slide 22 text
チェック制約で正しいデータを守る
チェック制約とストアドファンクション
Slide 23
Slide 23 text
チェック制約で正しいデータを守る
↓
他のテーブルの値は参照できない
チェック制約とストアドファンクション
Slide 24
Slide 24 text
チェック制約に
ストアドファンクションを使う
チェック制約とストアドファンクション
Slide 25
Slide 25 text
検索結果を制約にする CREATE FUNCTION get_valid_room(
schedule_at tstzrange,
set_room_id bigint
) RETURNS int AS
$$
DECLARE
responsed_row_count int;
BEGIN
SELECT COUNT('*') INTO responsed_row_count
FROM room
WHERE room_at @> schedule_at
AND room_id = set_room_id
RETURN responsed_row_count
;
END;
$$ LANGUAGE plpgsql;
-- ストアドファンクションをチェック制約に設定する
ALTER TABLE 予約
ADD CONSTRAINT check_room
CHECK (get_valid_room(schedule_at,room_id) = 1);
ストアドファンクションは
戻り値がある。
その戻り値を使ってチェック制約
の条件にする
`@>` は範囲の含む検索
Slide 26
Slide 26 text
チェック制約を活用すると
テーブルが減らせる
チェック制約とストアドファンクション
Slide 27
Slide 27 text
絶対に不正なデータが入らない
↓
テストやアプリケーションが楽
チェック制約とストアドファンクション
Slide 28
Slide 28 text
1. 自己紹介
2. 範囲型と排他制約
3. チェック制約とストアドファンクション
4. いい感じに検索する
5. まとめ
あじぇんだ
Slide 29
Slide 29 text
？？？「範囲型とかORMが対応してない」
いい感じに検索する
Slide 30
Slide 30 text
？？？「範囲型とかORMが対応してない」
↓
なければ作れ！
Djangoは拡張がある
いい感じに検索する
Slide 31
Slide 31 text
django.contrib.postgresは便利
https://docs.djangoproject.com/en/3.2/ref/contrib/postgres/
排他制約や範囲型を使うために必要。
配列型やJSON型も使えるぞ！
空間INDEXやGIN INDEXも扱えるので便利
ロック待ちの悪夢
Slide 32
Slide 32 text
？？？「Django以外は無いんですか？」
いい感じに検索する
Slide 33
Slide 33 text
？？？「Django以外は無いんですか？」
↓
SQLがあるじゃろ？？？
いい感じに検索する
Slide 34
Slide 34 text
正直、Django以外で
ゴリゴリ使うのはオススメしてない
いい感じに検索する
Library作るチャンスだぞ！！
Slide 35
Slide 35 text
1. 自己紹介
2. 範囲型と排他制約
3. チェック制約とストアドファンクション
4. いい感じに検索する
5. まとめ
あじぇんだ
Slide 36
Slide 36 text
PostgreSQLは便利
まとめ
Slide 37
Slide 37 text
だからこそ、正しく使うことが大切です
まとめ
Slide 38
Slide 38 text
だからこそ、正しく使うことが大切です
↓
毒にも薬にもなる
まとめ
Slide 39
Slide 39 text
実戦投入には自己責任で！！
（自分はDjangoでだけ導入してます）
まとめ
Slide 40
Slide 40 text
ご清聴ありがとうございました
まとめ