AFTER STATEMENTトリガーで上限付き再帰クエリを使い、許可された上限を超える階層のカテゴリを拒否します。書き込みトランザクションにはシリアライザブル分離レベルを必須とし、シリアライゼーション失敗時には再試行して、並行変更によって階層が共同で不正な状態にならないようにします。
このチュートリアルは、MacのTerminalでローカルのPostgreSQLデータベースを使う開発者向けです。具体的な作業は、既存のサブツリーを移動する場合も含め、カテゴリの階層を最大3段に制限することです。例ではすべて架空の記事カテゴリを使います。
階層数の上限の定義を明確にする
ここでは、ルートカテゴリの階層数を1とします。その子は階層数2、孫は階層数3です。第4階層は無効です。アプリケーションでルートを階層数0として数える場合は、上限を決める前にその数え方に合わせてください。
このテーブルでは隣接リストを使います。各カテゴリは、別のカテゴリを指す任意のparent_idを保持します。外部キーによって参照先の親が存在することは保証されますが、階層数のルールを適用するには複数の行を調べる必要があります。PostgreSQLでは、他の行に依存するCHECK制約を、継続的に維持される保証として使うことは明示的にサポートされていません。CHECKから呼び出す関数に探索処理を入れても、この制限は解消されません。制約。
以下の実装は、この記事用に取得した現行の安定版ドキュメントであるPostgreSQL 18を対象としています。インストール済みのPostgreSQL 18サーバーとクライアントに加えて、JavaScriptの例ではNode.js 24とnpmを使用します。それ以前のPostgreSQLバージョンについては、このチュートリアルの互換性を保証しません。node-postgresのドキュメントでは、互換バージョンの1つとしてNode.js 24が記載されています。PostgreSQL 18のドキュメント、node-postgresの互換性。
ステートメントの後に階層全体を検証する理由
新しい親の祖先だけを確認しても、サブツリーの移動には不十分です。たとえば、Articles → Guides → Editingがすでに3階層を占めているとします。Articlesを別のルートの下に移動すると、移動した行自体は第2階層にしか達しませんが、Editingは第4階層になります。
移動案に対する判定ルールは次のとおりです。サブツリーのルートの新しい階層数 + サブツリーの高さは、上限以下でなければなりません。高さは、サブツリーのルートから最深の子孫までの辺の数です。たとえば、新しいルートの階層数が2でサブツリーの高さが2なら、最深部の階層数は4になります。
祖先と子孫の計算を個別に維持する代わりに、この実装では変更を伴う各ステートメントの後にすべてのカテゴリを検証します。PostgreSQLのドキュメントによると、AFTERトリガーは操作による変更を確認でき、ステートメントトリガーはステートメントごとに1回実行されます。このため、行の処理順序に依存せず、複数行の挿入や更新にも対応できます。CREATE TRIGGER。
上限付き探索はすべてのカテゴリから始まり、親リンクをたどります。上方向への探索で4つのノードが見つかれば、3階層のルールに違反していることがわかります。ルートに到達しない循環でも、4ノードの探索になります。この上限により、無制限の再帰探索をせずに、過剰な階層数と循環の両方を拒否できます。この設計は、PostgreSQLの再帰CTE評価に関するドキュメントから導いたものです。WITHクエリ。
テーブルと階層数トリガーを作成する
Macのテキストエディタで、以下のセットアップ全体をcategory-depth.sqlとして保存します。新しいスキーマを作成するため、そのスキーマがまだ存在しないローカル開発データベースで1回実行してください。既存のテーブルを削除したり置き換えたりはしません。
トリガー関数は、PL/pgSQLのデータ変更トリガーに必要なtriggerを返します。また、明示的なエラーコードを指定したRAISE EXCEPTIONを使い、無効な階層と誤ったトランザクション設定を区別します。トリガー関数、エラーとメッセージ。
BEGIN ISOLATION LEVEL SERIALIZABLE;
CREATE SCHEMA category_depth_demo;
CREATE TABLE category_depth_demo.category (
id integer PRIMARY KEY,
name text NOT NULL,
parent_id integer REFERENCES category_depth_demo.category(id),
CONSTRAINT category_not_own_parent
CHECK (parent_id IS NULL OR parent_id <> id)
);
CREATE INDEX category_parent_idx
ON category_depth_demo.category(parent_id);
CREATE FUNCTION category_depth_demo.enforce_depth()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
max_levels CONSTANT integer := 3;
bad_id integer;
BEGIN
IF current_setting('transaction_isolation') <> 'serializable' THEN
RAISE EXCEPTION
'Category writes require a SERIALIZABLE transaction'
USING ERRCODE = '25000';
END IF;
WITH RECURSIVE walk(origin_id, parent_id, levels) AS (
SELECT id, parent_id, 1
FROM category_depth_demo.category
UNION ALL
SELECT w.origin_id, p.parent_id, w.levels + 1
FROM walk AS w
JOIN category_depth_demo.category AS p
ON p.id = w.parent_id
WHERE w.levels <= max_levels
)
SELECT origin_id INTO bad_id
FROM walk
WHERE levels = max_levels + 1
LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION
'Category % exceeds % levels or belongs to a cycle',
bad_id, max_levels
USING ERRCODE = '23514';
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER category_depth_guard
AFTER INSERT OR UPDATE OR DELETE
ON category_depth_demo.category
FOR EACH STATEMENT
EXECUTE FUNCTION category_depth_demo.enforce_depth();
INSERT INTO category_depth_demo.category(id, name, parent_id)
VALUES
(1, 'Articles', NULL),
(2, 'Guides', 1),
(3, 'Editing', 2),
(4, 'Reference', NULL),
(10, 'Notes', NULL),
(11, 'Examples', NULL);
COMMIT;
この関数は、PostgreSQLのドキュメントに記載されたcurrent_setting関数を通じて、トランザクションの分離レベルを読み取ります。1つのmax_levels定数で、再帰の上限と拒否のしきい値の両方を制御します。設定パラメータ関数。
Terminalを開き、ファイルのあるディレクトリに移動して、次のコマンドを実行します。既存のローカル接続設定で、localhost上のpostgresデータベースに接続できることを前提としています。-Xオプションは個人用のpsql起動ファイルをスキップし、ON_ERROR_STOPはエラー発生時にファイルの処理を停止します。psql。
psql -X -h localhost -d postgres -v ON_ERROR_STOP=1 -f category-depth.sql
外部キーの削除動作はデフォルトのままです。子を持つカテゴリを削除すると、サブツリーが暗黙に削除されるのではなく、削除が拒否されます。親インデックスはparent_idによる検索を支援しますが、トリガーによるテーブル全体の検証をなくすものではありません。PostgreSQLは、外部キーの参照側にインデックスを自動作成しません。外部キー。
挿入、サブツリーの移動、循環を確認する
アプリケーションを接続する前に、初期データを使って境界ケースを確認してください。以下の各操作は、最初のセットアップから変更がないことを前提としています。
| 操作 | 期待される結果 | 理由 |
|---|---|---|
| カテゴリ5をカテゴリ3の下に挿入する | 拒否 | 新しいカテゴリが第4階層になるため。 |
| カテゴリ1をカテゴリ4の下に移動する | 拒否 | カテゴリ3が第4階層に移動するため。 |
| カテゴリ1をカテゴリ3の下に移動する | 拒否 | 移動によって循環が生じるため。 |
| カテゴリ3をカテゴリ4の下に移動する | 許可 | カテゴリ3が第2階層になるため。 |
| デフォルトのRead Committedトランザクションで名前を更新する | 拒否 | この実装では、すべてのカテゴリ書き込みにシリアライザブルトランザクションが必要なため。 |
具体的なサブツリーのテストでは、psql -X -h localhost -d postgresで対話型セッションを開始し、次のブロックを実行します。この更新は階層数の例外を発生させるはずです。ROLLBACKは失敗したトランザクションを終了し、元の階層を維持します。
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 4
WHERE id = 1;
ROLLBACK;
SELECT id, name, parent_id
FROM category_depth_demo.category
ORDER BY id;
この探索では、過剰な階層数と循環を意図的にまとめて報告します。目的は不変条件を強制することであり、循環の正確な開始点を特定することではありません。訪問済みIDを別途追跡する診断クエリも作れますが、この上限付きの許可・拒否判定には必要ありません。
並行書き込みにも同じルールを適用する
再帰トリガーだけでは、並行性の問題は解決しません。2つのトランザクションが、それぞれ相手の未コミット変更を参照できないまま階層を検証することがあります。PostgreSQLの整合性に関するドキュメントでは、この問題を説明し、複数行に関するルールを強制する方法の1つとしてシリアライザブルトランザクションを推奨しています。アプリケーションレベルでのデータ整合性チェック。
そのため、この設計ではすべてのカテゴリ書き込みにシリアライザブル分離レベルを必須とします。成功したシリアライザブルトランザクションは、1つずつ順に実行されたかのように振る舞います。並行処理のためその順序を実現できない場合、PostgreSQLはトランザクションを拒否することがあります。そのため、更新が成功してもトランザクションがコミットするまでは確定ではありません。トランザクション分離。
架空の2つのルート、10と11を使って問題を実演できます。2つの対話型psqlセッションを開き、どちらもコミットする前に、それぞれのセッションで更新を実行します。
-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 11
WHERE id = 10;
-- Session B, in a separate psql session
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE category_depth_demo.category
SET parent_id = 10
WHERE id = 11;
その後、各セッションでCOMMITを実行します。2つの親変更が両方ともコミットされることはありません。両方がコミットされると循環が生じるためです。シリアライゼーション失敗は、ステートメント中またはコミット時に発生することがあります。一方の変更がコミットされた後にもう一方のセッションが更新を実行した場合、トリガーが可視になった循環を直接拒否することもあります。これらは、検証クエリとシリアライザブル実行を組み合わせた場合に予想される結果です。トランザクション分離。
node-postgresでカテゴリを移動する
プロジェクトディレクトリで、まだpackageファイルがなければnpm init -yを実行し、続いてnpm install pgでドライバーをインストールします。以下のプログラム全体をmove-category.mjsとして保存します。ドキュメントに記載されたインストール用パッケージはpgです。node-postgres。
このプログラムは、psqlコマンドと同じローカルデータベースに接続します。その他の接続項目には、ドライバーの環境変数ベースのデフォルト値が使われます。node-postgresの要件に従い、各トランザクション文は1つのチェックアウト済みクライアントを使います。トランザクション文を個別のpool.query呼び出しに分散させてはいけません。接続、トランザクション。
import pg from 'pg';
import { setTimeout as delay } from 'node:timers/promises';
const { Pool } = pg;
const pool = new Pool({
host: 'localhost',
database: 'postgres',
connectionTimeoutMillis: 5000
});
pool.on('error', (error) => {
console.error('Idle database client error:', error.message);
process.exitCode = 1;
});
async function moveCategory(id, parentId) {
const maxAttempts = 5;
for (let attempt = 1; attempt <= maxAttempts; attempt++) {
const client = await pool.connect();
let discard = false;
let retry = false;
try {
await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
const result = await client.query(
`UPDATE category_depth_demo.category
SET parent_id = $1
WHERE id = $2
RETURNING id, name, parent_id`,
[parentId, id]
);
if (result.rowCount !== 1) {
throw new Error(`Category ${id} does not exist`);
}
await client.query('COMMIT');
return result.rows[0];
} catch (error) {
try {
await client.query('ROLLBACK');
} catch {
discard = true;
}
retry = !discard && error.code === '40001'
&& attempt < maxAttempts;
if (!retry) throw error;
} finally {
client.release(discard);
}
if (retry) await delay(25 * attempt);
}
}
try {
const moved = await moveCategory(3, 4);
console.log(moved);
} catch (error) {
console.error(error.code ?? 'APP_ERROR', error.message);
process.exitCode = 1;
} finally {
await pool.end();
}
node move-category.mjsで実行します。初期データのままなら、カテゴリ3がカテゴリ4の下に移動し、返された行にはparent_id: 4が含まれます。このクエリでは、ドライバーのドキュメントにあるパラメーターインターフェースに従って、$1と$2を使って値を渡します。クエリ。
このプログラムはfinallyでクライアントを返し、ロールバックに失敗した場合はクライアントを破棄し、スクリプトの終了時にプールを閉じます。これらのライフサイクル操作は、ドキュメントに記載されたプールAPIに従っています。pg.Pool。
SQLSTATE 40001が発生した場合は、移動先を決める読み取りも含めて、トランザクション全体を再実行します。トリガーが返す階層数超過エラー23514は自動で再試行しないでください。変更案の修正が必要です。5回という試行回数の上限と、徐々に長くなる短い待ち時間は、アプリケーション上の例示的な選択であり、PostgreSQLの要件でも、最終的な完了の保証でもありません。シリアライゼーション失敗の処理。
例を応用する前に限界を理解する
この実装では、名前だけを更新する場合も含め、各ステートメントの後に階層を検証します。1つのステートメントで一時的に階層数の上限を超え、その後のステートメントで修正することはできません。各ステートメントの結果として階層が有効な状態になるように計画してください。PostgreSQLは遅延可能な制約トリガーをサポートしていますが、それらはAFTER ROWトリガーでなければなりません。このステートメントトリガーを単に遅延可能に設定することはできません。CREATE TRIGGER。
この方式では、検証にかかる処理量を予測しやすくなります。上限が3の場合、再帰関係にはカテゴリごとに最大4行の探索行が含まれますが、実際の実行コストはクエリプランとデータベースの内容に左右されます。大規模なテーブルや頻繁な小規模書き込みに採用する前に、測定することをお勧めします。影響を受けるサブツリーだけを検査する方式に置き換えるには、子孫の検証、循環の拒否、並行性に関する根拠を維持する必要があります。
既存のテーブルでは、トリガーを追加しただけでは保存済みの行は監査されません。通常の書き込みを一時停止し、関数内の再帰クエリを単独で実行して監査します。その際、max_levelsを3に置き換え、一致するorigin_idをすべて選択してください。報告されたパスを解決し、書き込みを有効にする前に、親参照も個別に検証します。
維持すべき不変条件は単純です。各カテゴリは3ノード以内でルートに到達しなければなりません。この上限付き探索で、サブツリーの移動と循環の両方をチェックできます。シリアライザブルトランザクションとトランザクション全体の再試行で、並行更新時にもこの条件を維持します。カテゴリテーブルに例を応用する際は、これらの要素をまとめて維持してください。
出典
- 制約 — PostgreSQL Global Development Group。
- WITHクエリ（共通テーブル式） — PostgreSQL Global Development Group。
- CREATE TRIGGER — PostgreSQL Global Development Group。
- トリガー関数 — PostgreSQL Global Development Group。
- エラーとメッセージ — PostgreSQL Global Development Group。
- システム管理関数 — PostgreSQL Global Development Group。
- psql — PostgreSQL Global Development Group。
- アプリケーションレベルでのデータ整合性チェック — PostgreSQL Global Development Group。
- トランザクション分離 — PostgreSQL Global Development Group。
- シリアライゼーション失敗の処理 — PostgreSQL Global Development Group。
- node-postgres — node-postgres。
- 接続 — node-postgres。
- トランザクション — node-postgres。
- クエリ — node-postgres。
- pg.Pool — node-postgres。