【ITニュース解説】Your Agent's ADD FOREIGN KEY Stalls Writes on Two Tables. NOT VALID, Then VALIDATE

データベースの設計や変更を行う際、特に稼働中のシステムでは細心の注意が必要となる。一見シンプルに見えるデータベースの変更、例えばテーブル間に「外部キー」という関連性（リレーション）を追加する操作が、システム全体に予期せぬ大きな影響を与えることがある。これは、AIを活用したプログラミングツール（エージェント）が生成したSQL文でも起こりうる、非常に重要な問題だ。

具体的には、ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id); というSQL文が問題を引き起こす。この文は「ordersテーブルのcustomer_idカラムが、customersテーブルのidカラムを参照する外部キーである」と定義している。しかし、これをそのまま実行すると、システムに接続している他のユーザーが行う書き込み処理（新しい注文の追加や顧客情報の更新など）が突然停止してしまう現象が発生する可能性がある。

この問題の根源は、データベース管理システムであるPostgreSQLの「ロック」の仕組みにある。データベースでは、複数のユーザーが同時にデータを操作しても整合性が保たれるように、特定の操作中にテーブルや行を一時的にロックする。ADD FOREIGN KEY 文を実行すると、PostgreSQLはordersテーブルとcustomersテーブルの両方にSHARE ROW EXCLUSIVEという強力なロックをかける。このロックは、テーブルへのデータの変更を伴う操作をブロックするために使われるものだ。

一方で、データベースに新しいデータを追加したり（INSERT）、既存のデータを更新したり（UPDATE）、削除したり（DELETE）する通常の書き込み処理は、ROW EXCLUSIVEというロックを使用する。このROW EXCLUSIVEロックと、ADD FOREIGN KEYが取得するSHARE ROW EXCLUSIVEロックは互いに競合する関係にある。つまり、ADD FOREIGN KEYがテーブルにロックをかけている間は、他の全ての書き込み処理がそのロックの解除を待たされ、結果として処理が停止してしまうのだ。

このロックは、外部キー制約を追加する際に、既存のデータが新しい制約に違反していないかを全行スキャンして確認する間ずっと保持される。もしordersテーブルが非常に大規模であれば、このスキャンには長時間かかるため、その間ずっと書き込みが停止してしまうことになる。さらに、多くのデータベースマイグレーションツールは、個々のSQL変更を一つの「トランザクション」として実行する。トランザクションとは、一連の処理をまとめて実行し、全て成功するか全て失敗するかのどちらかを保証する仕組みだが、一度取得したロックはトランザクションが完了（コミット）するまで解除されないため、スキャンが終わった後もロックが保持され続けることになる。

この問題を実際に体験するには、Dockerを使ってPostgreSQLの環境を構築し、大量の顧客データと注文データを持つテーブルを用意して試すことができる。ALTER TABLE ADD FOREIGN KEYをトランザクション内で実行し、同時に別のデータベースセッションから注文の追加や顧客情報の更新を行うと、それらの書き込み処理が実行されずに止まってしまう様子を観察できる。データベースの状態を監視するツール（pg_stat_activityやpg_locks）を使うと、どのプロセスがどのロックを保持し、どのプロセスがそのロックを待っているのかを具体的に確認できる。

このような事態を避けるための安全な解決策は、外部キー制約の追加を以下の三つのステップに分割することである。

第一のステップは、外部キーが参照するカラム（今回の例ではorders.customer_id）に「インデックス」を作成することだ。インデックスはデータベースの検索速度を向上させるための仕組みだが、外部キー制約がある場合、親テーブル（customers）のデータが削除されたり、主キーが更新されたりする際に、参照元のテーブル（orders）で関連するデータがないかを確認するためにインデックスが利用される。これにより、これらの操作時のパフォーマンスが大幅に向上する。CREATE INDEX CONCURRENTLYというコマンドを使うことで、このインデックス作成処理はデータベースへの書き込み操作をブロックすることなく実行できる。ただし、このコマンドはトランザクション内では実行できないため、マイグレーションツールの設定でこのステップだけトランザクションを無効化する必要がある。

第二のステップは、外部キー制約自体を追加することだが、ここではNOT VALIDオプションを使用する。ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID; のように記述する。NOT VALIDを指定することで、PostgreSQLは既存のデータが制約に違反していないかのスキャンをスキップする。これにより、制約追加時のロック保持時間が劇的に短縮され、書き込みの停止を防げる。この時点では、既存のデータについては制約が適用されていないが、このコマンドの実行以降に新しく追加されるデータや更新されるデータは、この外部キー制約に違反していないかチェックされるようになる。また、このステップではSET lock_timeout = '3s';のように「ロックタイムアウト」を設定することが推奨される。これは、もしこのマイグレーション自体が他の長時間実行中のトランザクションにブロックされた場合、無限に待つのではなく指定された時間（この例では3秒）で処理を中断させることで、システム全体への影響を最小限に抑えるための工夫である。

第三のステップは、先ほどNOT VALIDで追加した外部キー制約を「検証（VALIDATE）」することだ。ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;と記述する。このコマンドを実行すると、PostgreSQLは既存のデータが外部キー制約に違反していないかを全行スキャンして確認する。しかし、この検証時のロックはSHARE UPDATE EXCLUSIVEとROW SHAREという、書き込み操作と競合しない比較的弱いロックモードで行われる。そのため、このステップの実行中も、ordersテーブルやcustomersテーブルへの通常の書き込み操作はブロックされることなく継続できる。もし既存のデータの中に制約に違反しているもの（例えば、存在しない顧客IDを参照している注文データ）があれば、この検証は失敗し、制約は「検証されていない」状態のままとなるため、事前にそのような不正なデータを特定し、修正しておく必要がある。

非常に重要な点として、この第二のステップ（NOT VALIDでの追加）と第三のステップ（VALIDATEでの検証）は、必ず別々のトランザクションとして実行する必要がある。もしこれらを一つのトランザクション内で実行してしまうと、第二のステップで取得されたSHARE ROW EXCLUSIVEロックが、第三のステップの検証中も解除されずに保持され続け、結局書き込み処理がブロックされてしまうという元の問題に戻ってしまうからだ。

このような安全な手順を、AIエージェントが自動で生成するSQLに反映させるためには、エージェントに対して明確なルールを設定する必要がある。例えば、エージェントがコードを生成する際に参照する設定ファイルに、「外部キーを追加する際は、CREATE INDEX CONCURRENTLY、ADD CONSTRAINT ... NOT VALID、VALIDATE CONSTRAINTの3段階で行うこと」「各DDL（データ定義言語）のマイグレーションはSET lock_timeoutから始めること」といった具体的な指示を記述する。さらに、CI/CDパイプライン（コード変更が本番環境に適用されるまでの一連の自動化されたプロセス）に、このような外部キー制約の追加パターンをチェックする仕組みを組み込むことで、万が一エージェントがルールを忘れてしまった場合でも、システムへの悪影響を未然に防ぐことができる。

データベースの変更はシステムの安定性に直結するため、その影響を深く理解し、安全な手順を踏むことがシステムエンジニアにとって不可欠である。