朝に承認した二件、昼には一件しか残っていない

目標

承認を受けた2件を、SQLだけでキャンセルします。承認時点の観測をテーブルに残し、顧客・番号・バージョン・数量・状態がすべてそのままのときだけ変更されるUPDATEを作り、その間に別の担当者が変更した1件では0行しか変わらないことを、実際のデータベースで確認します。

なぜ重要なのか

読み取りで見た契約(承認対象はid・revision・qtyで、顧客を区別するtenantは別)を、ここではテーブルと関数にして作ります。承認画面を見た時刻と実行する時刻の間には、いつも時間が流れます。その間をテーブルロックで塞ぐと他の業務が止まるので、代わりに承認時点の観測を小さなデータとして残し、適用するときにその観測がまだ有効かを条件で比較します。比較が食い違ったら、値を現在の値に黙って変えるのではなく、その件を残して、改めて判断を受ける必要があります。このラボのテーブルは主キーが(tenant, id)なので、条件から顧客を外すと他の顧客の注文まで一緒にキャンセルされてしまうことも、同じ場所で明らかになります。

すぐ次のモジュールのfde-revision-labは、同じ契約をPythonで8つの関数として実装します。このラボはその前に、契約そのものがデータベースの中でどんな形になるかを、SQL一層でまず見ます。

想定所要時間は70分です。期限が切れる前に＋時間を押して、セッションを延長してください。セッションが終わると、/root以下のファイルはすべて消えます。

環境

PostgreSQL 16がPod内ですでに動いています。接続はpsql -X -U lab -d labdbで、ホストは127.0.0.1です(export PGHOST=127.0.0.1)。インターネットや追加のインストールは不要です。

このラボは、スキーマscopeの中だけで作業します。publicの既存のラボデータや他のスキーマを変更しないでください。成果物はすべて/root/scopeの下に置きます。採点ツールは、ファイルに書かれた数字を、生きているテーブルにもう一度問い合わせて照合し、関数のステップでは、自分のトランザクション内にサンプルを用意してあなたの関数を呼び出したあと、ロールバックします。そのため、何度採点してもデータは変わりません。

ステップ

/root/scope/01-schema.sqlを作成し、スキーマscopeとテーブル2つを作ってください。scope.ordersはtenant・id・qty・state・revisionの5列で、主キーは(tenant, id)の組み合わせです。stateはpending・paid・cancelledのみ、qtyは1から1000、revisionは0以上です。6行を入れます。blue/1(数量7、pending、revision 3)、blue/2(数量4、pending、revision 1)、blue/3(数量9、pending、revision 0)、blue/4(数量5、paid、revision 2)、green/1(数量7、pending、revision 3)、green/2(数量4、pending、revision 1)です。scope.baselineは同じ5列に同じ主キーを持たせ、いま作ったscope.ordersをそのままコピーして埋めます。朝に照会画面が見せていた、その時点の観測です。SQLを適用したあと、/root/scope/01-schema.txtにrows・tenants・pk・baseline_rowsの4行を残してください。
/root/scope/02-approval.sqlでテーブルscope.approvalを作ってください。change_id・tenant・id・revision・qtyの5列で、主キーは(change_id, tenant, id)です。変更IDはchg-rain-01とし、顧客blueの注文1と注文2のうち、朝の観測でpendingだったものだけ(scope.baseline)を選んで、そのときのrevision・qtyと一緒に入れてください。入れる前に同じchange_idの既存の行を削除して、何度実行しても結果が同じになるようにします。そのあと/root/scope/02-approval.txtにchange_id・targets・digestの3行を残してください。digestは、承認行をid順にid:revision:qtyの形で書き、カンマでつないだ文字列です。
/root/scope/03-validate.sqlで関数scope.validate_targets(p jsonb) returns jsonbを作ってください。入力は承認対象の配列です。配列でない、要素が0個または16個を超える、要素がオブジェクトでない、キーがid・revision・qtyの3つでない、3つの値のどれかがJSONの数値でない(真偽値と文字列は数値ではありません)、整数でない、idが1から2147483647の範囲外、revisionが2147483646を超える、qtyが1から1000の範囲外、同じidが2回出てくる、のいずれかに当てはまる場合は、SQLSTATE22023で例外を投げます。通過した場合は、id昇順に並べた新しい配列を返し、各要素はid・revision・qtyの3つのキーだけを持ちます。
/root/scope/04-apply.sqlで関数scope.apply_change(p_change_id text) returns table(changed integer, skipped integer)を作ってください。その変更IDの承認行が1つもなければ、SQLSTATE22023で例外を投げます。あればscope.ordersを更新しますが、tenant・id・revision・qtyが承認時の値と同じで、stateがpendingの行だけを変更します。変更された行は、stateがcancelledになり、revisionが1増えます。changedは実際に変更された行数、skippedは承認対象数からchangedを引いた値です。このステップでは関数だけを作り、chg-rain-01には適用しないでください。
昼の間に、別の担当者がblueの注文2の数量を5に直しました。正常な業務変更なので、revisionも1増えます。/root/scope/05-drift.sqlでその変更を作りますが、revisionがまだ1のときだけ適用されるように条件を付けて、何度実行しても結果が同じになるようにしてください。そのあと/root/scope/05-drift.txtに、tenant・id・new_qty・new_revision・approved_qty・approved_revisionの6行を残してください。前の3つは現在のscope.ordersから、あとの2つはscope.approvalから取り出します。
chg-rain-01を実際に適用してください。select * from scope.apply_change('chg-rain-01')です。結果を/root/scope/06-apply.txtに、changed・skipped・applied_id・blocked_id・other_tenant_changedの5行で残してください。applied_idは実際にキャンセルされた注文番号、blocked_idは承認対象ですが変更されなかった注文番号です。other_tenant_changedはgreen顧客の行のうち、朝の観測と変わった行の数で、scope.baselineとscope.ordersを突き合わせて数える必要があります。
/root/scope/07-reconcile.sqlで関数scope.reconcile(p_change_id text) returns table(id integer, verdict text)を作ってください。承認行ごとに、現在のscope.ordersを見て判定を付けます。同じ顧客・番号の行がなければmissing、あって、stateがcancelledで、revisionが承認時の値+1で、qtyが承認時の値と同じならmatching、それ以外はdriftedです。結果はid昇順で、承認にない番号は含めません。読み取りだけを行う関数でなければなりません。作成後、chg-rain-01で呼び出し、/root/scope/07-reconcile.txtにmatching・drifted・missing・verdictsの4行を残してください。verdictsは、判定をid順にid:판정(プレースホルダーは判定です)の形で書き、カンマでつないだ文字列です。
最後に、顧客に送る照合表を/root/scope/08-report.txtに6行で作ってください。approved_targets・applied・drifted・missing・changed_rows_total・changed_outside_approvalです。前の4つはステップ7の関数から、changed_rows_totalはscope.baselineとscope.ordersを突き合わせて、qty・state・revisionのどれか1つでも違っている行の数として、changed_outside_approvalはその違っている行のうちchg-rain-01の承認対象でない行の数として取り出します。6つの値はすべて、手で書かずにクエリ結果をそのまま入れてください。

参考

SQLファイルはpsql -X -U lab -d labdb -q -f 파일이름(プレースホルダーはファイル名です)で適用します。証拠ファイルの数字は手で書かず、クエリ結果をリダイレクトしてください。
よくあるミス1: 条件に顧客を入れ忘れてしまいます。主キーが(tenant, id)なので、番号だけ合わせると、2つの顧客の行が一緒に該当します。
よくあるミス2: 変更された行数を、UPDATEの前のSELECTで数えてしまいます。その間に値がまた変わる可能性があります。RETURNINGをCTEで包んで数えてください。
公式ドキュメント: UPDATE、JSONの関数、エラーコード。

変更対象のテーブルと朝の観測を一緒に残す

/root/scope/01-schema.sqlを作成し、スキーマscopeとテーブル2つを作ってください。scope.ordersはtenant・id・qty・state・revisionの5列で、主キーは(tenant, id)の組み合わせです。stateはpending・paid・cancelledのみ、qtyは1から1000、revisionは0以上です。6行を入れます。blue/1(数量7、pending、revision 3)、blue/2(数量4、pending、revision 1)、blue/3(数量9、pending、revision 0)、blue/4(数量5、paid、revision 2)、green/1(数量7、pending、revision 3)、green/2(数量4、pending、revision 1)です。scope.baselineは同じ5列に同じ主キーを持たせ、いま作ったscope.ordersをそのままコピーして埋めます。朝に照会画面が見せていた、その時点の観測です。SQLを適用したあと、/root/scope/01-schema.txtにrows・tenants・pk・baseline_rowsの4行を残してください。

主キーをidだけにすると、greenの注文1をそもそも入れられません。顧客が違えば同じ注文番号が存在しうる、というのがこのテーブルの前提です。再実行しても同じ結果になるように、create table if not existsとon conflict do nothingを使ってください。pkの行には、主キーの列名を順番にカンマでつないで書きます。

承認時点の観測をテーブルに固定する

/root/scope/02-approval.sqlでテーブルscope.approvalを作ってください。change_id・tenant・id・revision・qtyの5列で、主キーは(change_id, tenant, id)です。変更IDはchg-rain-01とし、顧客blueの注文1と注文2のうち、朝の観測でpendingだったものだけ(scope.baseline)を選んで、そのときのrevision・qtyと一緒に入れてください。入れる前に同じchange_idの既存の行を削除して、何度実行しても結果が同じになるようにします。そのあと/root/scope/02-approval.txtにchange_id・targets・digestの3行を残してください。digestは、承認行をid順にid:revision:qtyの形で書き、カンマでつないだ文字列です。

承認スナップショットには、現在の値ではなく、承認した瞬間の値を入れます。そのため、読み取り元はscope.ordersではなくscope.baselineです。digestはstring_aggにorder byを付けて作れば、行の順序が変わっても同じ文字列になります。

重複・空のリスト・数値でないものを、書き込む前に弾く

/root/scope/03-validate.sqlで関数scope.validate_targets(p jsonb) returns jsonbを作ってください。入力は承認対象の配列です。配列でない、要素が0個または16個を超える、要素がオブジェクトでない、キーがid・revision・qtyの3つでない、3つの値のどれかがJSONの数値でない(真偽値と文字列は数値ではありません)、整数でない、idが1から2147483647の範囲外、revisionが2147483646を超える、qtyが1から1000の範囲外、同じidが2回出てくる、のいずれかに当てはまる場合は、SQLSTATE22023で例外を投げます。通過した場合は、id昇順に並べた新しい配列を返し、各要素はid・revision・qtyの3つのキーだけを持ちます。

jsonb_typeofが、真偽値はboolean、引用符で囲んだ数字はstringとして区別してくれます。小数点はtypeofでは引っかからないので、文字列として取り出して、整数かどうかをもう一度確認してください。例外を投げるときにSQLSTATEを指定するには、raise exception using errcodeを使います。

承認した値がすべてそのままのときだけ変更する関数

/root/scope/04-apply.sqlで関数scope.apply_change(p_change_id text) returns table(changed integer, skipped integer)を作ってください。その変更IDの承認行が1つもなければ、SQLSTATE22023で例外を投げます。あればscope.ordersを更新しますが、tenant・id・revision・qtyが承認時の値と同じで、stateがpendingの行だけを変更します。変更された行は、stateがcancelledになり、revisionが1増えます。changedは実際に変更された行数、skippedは承認対象数からchangedを引いた値です。このステップでは関数だけを作り、chg-rain-01には適用しないでください。

UPDATEにFROM句で承認テーブルを結合すると、5つの条件を1つの文に入れられます。実際に何行が変わったかは、RETURNINGをCTEで包んで数えるのが正確です。先にSELECTで数えておいてからUPDATEすると、その間の変更を見逃します。採点ツールは自分のトランザクションにサンプルを用意してこの関数を呼び出したあとロールバックするので、関数はスキーマ名を固定して書けばかまいません。

承認のあとで、別の担当者が1件を変更する

昼の間に、別の担当者がblueの注文2の数量を5に直しました。正常な業務変更なので、revisionも1増えます。/root/scope/05-drift.sqlでその変更を作りますが、revisionがまだ1のときだけ適用されるように条件を付けて、何度実行しても結果が同じになるようにしてください。そのあと/root/scope/05-drift.txtに、tenant・id・new_qty・new_revision・approved_qty・approved_revisionの6行を残してください。前の3つは現在のscope.ordersから、あとの2つはscope.approvalから取り出します。

承認スナップショットは、このステップで1文字も変わってはいけません。変わったのは、現在の行だけです。2つの数字が食い違ったという事実そのものが、次のステップで0行しか変わらない理由です。

適用すると1件だけが変わる

chg-rain-01を実際に適用してください。select * from scope.apply_change('chg-rain-01')です。結果を/root/scope/06-apply.txtに、changed・skipped・applied_id・blocked_id・other_tenant_changedの5行で残してください。applied_idは実際にキャンセルされた注文番号、blocked_idは承認対象ですが変更されなかった注文番号です。other_tenant_changedはgreen顧客の行のうち、朝の観測と変わった行の数で、scope.baselineとscope.ordersを突き合わせて数える必要があります。

承認対象は2件ですが、1件は朝の観測と値が変わっています。条件5つすべてが一致しないと変更されないので、この行はそのまま通り過ぎます。エラーではなく、0行です。greenの注文1は、blueの注文1と、番号もバージョンも数量も同じです。条件に顧客が入っているかどうかが、ここで明らかになります。

件数ではなく対象で照合する

/root/scope/07-reconcile.sqlで関数scope.reconcile(p_change_id text) returns table(id integer, verdict text)を作ってください。承認行ごとに、現在のscope.ordersを見て判定を付けます。同じ顧客・番号の行がなければmissing、あって、stateがcancelledで、revisionが承認時の値+1で、qtyが承認時の値と同じならmatching、それ以外はdriftedです。結果はid昇順で、承認にない番号は含めません。読み取りだけを行う関数でなければなりません。作成後、chg-rain-01で呼び出し、/root/scope/07-reconcile.txtにmatching・drifted・missing・verdictsの4行を残してください。verdictsは、判定をid順にid:판정(プレースホルダーは判定です)の形で書き、カンマでつないだ文字列です。

承認行を左に置いて現在の行をLEFT JOINすると、なくなった対象がNULLで残り、missingを区別できます。結合条件に顧客と番号の両方を入れないと、他の顧客の同じ番号が結び付いてしまいます。数量が同じでもバージョンが違えば、それはその後の別の変更です。

承認範囲と実際の変更を並べて書く

最後に、顧客に送る照合表を/root/scope/08-report.txtに6行で作ってください。approved_targets・applied・drifted・missing・changed_rows_total・changed_outside_approvalです。前の4つはステップ7の関数から、changed_rows_totalはscope.baselineとscope.ordersを突き合わせて、qty・state・revisionのどれか1つでも違っている行の数として、changed_outside_approvalはその違っている行のうちchg-rain-01の承認対象でない行の数として取り出します。6つの値はすべて、手で書かずにクエリ結果をそのまま入れてください。

最後の行が、このラボの結論です。承認範囲の外では何も変わっていないことを、件数ではなく対象で示す数字です。朝の観測と現在を比較するときにNULLが混ざることがあるので、is distinct fromを使うと安全です。