確認してから書くと壊れ、制約にすれば守られる

一言でいうと

不変条件を、アプリケーションが「確認してから書く」と、確認と書き込みの間の隙間で壊れます。データベースが制約として持っていれば、その隙間はありません。制約で表現できない、複数の行にまたがるルールだけをSERIALIZABLEに任せ、そのときに対になるリトライのループは、40001だけをやり直す必要があります。

なぜ必要なのか

「このメールアドレスがなければ登録させる」をSELECTのあとにINSERTで書くと、2つのリクエストが同時に「ない」を見て、どちらも挿入します。予約時間の重なりの検査も、同じ形です。分離レベルとライトスキューは、「データベースの概念」コースのトランザクションのラボが、行ロックは「PostgreSQL応用」コースが扱います。このモジュールは、その次の問いを見ます。ルール自体をデータベースに渡すには、何を知る必要があるのか。冪等キーにUNIQUEとON CONFLICTを使う設計は、「本番バックエンドAPIキャップストーン」が扱うので、ここでは、その文が正確に何を返すのかと、UNIQUEでは表現できないルールへと広げます。

どう動くのか

UNIQUEとON CONFLICT: INSERTのドキュメントによれば、ON CONFLICT DO NOTHINGは、挿入する代わりに何もせず、DO UPDATEは、並行性が高くても、INSERTかUPDATEのどちらか一方がアトミックに行われることを保証します。落とし穴はRETURNINGです。ドキュメントは、実際に挿入または変更した行だけを返すと記しています。DO NOTHINGでスキップされた行は出てこないので、「空の結果 = すでに存在していた」であり、既存の行のidが必要なら、DO UPDATEを使う必要があります。SET句では、既存の行はテーブル名(別名)で、挿入しようとした行はEXCLUDEDで指します。両者を取り違えてEXCLUDED.visits + 1と書くと、訪問数が増えず、毎回同じ値になります。制約のドキュメントは、デフォルトでは2つのNULLを同じと見なさないので、UNIQUEがあってもNULLを含む行は重複しうること、NULLS NOT DISTINCTで変更できることを述べています。

重なりはEXCLUDEで: 「同じ部屋の予約時間が重ならない」は、等しさではなく、重なりの関係なので、UNIQUEでは書けません。排他制約は、2つの行を、指定した演算子で比較したとき、少なくとも1つが偽かNULLでなければならない、というルールで、設定すると、その種類のインデックスが自動的に作られます。範囲型のドキュメントは、[と]を含む、(と)を除く境界として使い、2引数のコンストラクターは、下を含み上を除く[)の標準形を作ると記しています。10–11時と11–12時の予約は、[)では重なりませんが、[]で指定すると、11時の1点で重なります。部屋番号のようなスカラー値の=をGiSTの中で使うには、btree_gistが必要で、そうして初めてEXCLUDE USING gist (room WITH =, 시간범위 WITH &&)(韓国語で「時間範囲」を意味する語です)という1つの制約に両方をまとめられます。CREATE TABLEのWHERE (predicate)を付けると、キャンセルされた予約のように、テーブルの一部にだけ制約をかけられます(内部では部分インデックスが作られます)。

文の途中で一時的に壊れる変更はDEFERRABLEで: 同じドキュメントの互換性の節は、遅延不可能なUNIQUEを、PostgreSQLが行を挿入または変更するたびに即時に検査し、標準は文の終わりに検査するよう定めていると記しています。2人の乗客の座席を入れ替える最初のUPDATEが、ただちに23505を受け取る理由です。DEFERRABLE INITIALLY DEFERREDは、トランザクションの終わりにだけ検査します。受け付けるのは、UNIQUE・PRIMARY KEY・EXCLUDE・外部キーだけで、NOT NULLとCHECKは遅延されません。ALTER TABLEのALTER CONSTRAINTは、現在は外部キーしか変更できないので、UNIQUEは削除して作り直す必要があります。そして、遅延可能な制約は、ON CONFLICTの判定基準(arbiter)にはなれません。登録メールアドレスに設定すると、upsertが壊れます。

複数行のルールは、分離とリトライで: 制約のドキュメントは、CHECKが、検査中の行以外の行を参照することはサポートされないと明記しています。「家族のウォレットの合計が負になってはいけない」は、ここに該当します。このようなルールはSERIALIZABLEが守り、失敗は常にSQLSTATE 40001で来ます。直列化失敗の処理の節は、どのSQLを発行するかを決めるロジックまで含めて、トランザクション全体をやり直す必要があり、そのためPostgreSQLは自動リトライを提供しないと説明しています。やり直す対象は、40001とデッドロック(40P01)で、23505・23P01は永続的なエラーの可能性があるので、より慎重に扱うよう述べています。psycopg 3は、例外のsqlstateでコードを提供し、エラーコードの付録は、メッセージの文言ではなく、コードで分岐するよう勧めています。

不変条件	置く場所	ツール
メールアドレス1つにつき会員1人	制約	UNIQUE + ON CONFLICT
同じ部屋の時間が重ならない	制約	EXCLUDE + btree_gist + [)
座席1つにつき乗客1人	制約	DEFERRABLE UNIQUE
複数行にまたがる合計の条件	分離	SERIALIZABLE + 40001のリトライ
外部メールを1回だけ	アプリケーション	冪等キー・アウトボックス

現場での姿

制約は、既存の行も検査します。ALTER TABLEのドキュメントは、制約を追加すると、通常、すべての行が条件を満たすかどうかを確認するために、テーブルを走査すると記しています。そのため、本番のテーブルにUNIQUEを1つ設定する作業は、「すでに入っている重複をどう整理するか」から始まります。予約のように削除できない記録は、削除せずにキャンセルの状態にしたあと、アクティブな行にだけ制約をかけます。リトライのループでもっともよくある事故は、except Exceptionですべてのエラーをやり直すコードです。制約違反は、何百回やり直しても同じ結果であり、回数が尽きると、失敗を飲み込んだまま、成功だと報告するコードまで出てきます。

次のラボですること

2つのセッションで二重登録を再現したあと、既存の重複を整理して、UNIQUEとON CONFLICTで防ぎます。予約テーブルで既存の重なりを見つけてキャンセルし、排他制約を設定して、座席の入れ替えをDEFERRABLEで解決します。psycopg 3で、40001だけをやり直すリトライのループを書き、採点ツールがわざと起こすエラーに対して、そのループが正しく止まるかを確認したあと、不変条件ごとに置く場所を表にまとめます。