多段階プロシージャの総合デバッグと最適化

多段階プロシージャって、DB界の「スイスアーミーナイフ」みたいなもんだよね。だいたい入力データのバリデーション、データの変更（たとえばレコードの更新やログの挿入）、時には分析まで入ってたりする。でもさ、複雑になればなるほどバグの温床にもなりやすい。ロジックミス、遅いクエリ、見落とし…どれか一つでもあると全部台無し。

総合デバッグで見るべきポイントはこんな感じ：

1. 入力データの分析：パラメータちゃんと設定されてる？データ正しい？
2. 主要ステップの実行チェック：全部のステップがちゃんと動いてる？
3. 中間結果のログ出力：どこで何が起きたか、何か「壊れる」前に把握しとく。
4. パフォーマンスのボトルネック最適化：「遅い」とこを見つけて改善！

課題設定：多段階プロシージャの例

実践例として、ネットショップのDBを使うとしよう。注文処理用のプロシージャを作るのがミッション。やることは：

1. 在庫があるかチェック
2. 商品を予約
3. 注文ステータスを更新
4. イベント（たとえば予約成功やエラー）をログテーブルに記録

DB構造のスクリプト：

-- 商品テーブル
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- 注文テーブル
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- ログテーブル
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

ステップ1：多段階プロシージャの作成

じゃあ、基本のprocess_orderプロシージャを作ってみよう。注文IDを受け取って、全部の処理をやるやつね。

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. 商品IDと注文ステータスを取得
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION '注文 % が存在しないか、product_id が未設定', p_order_id;
    END IF;

    -- 2. 在庫チェック
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION '商品 % の在庫切れ', v_product_id;
    END IF;

    -- 3. 在庫数を更新
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. 注文ステータスを更新
    UPDATE orders
    SET order_status = '処理済み'
    WHERE order_id = p_order_id;

    -- 5. 成功イベントをログに記録
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, '注文が正常に処理されました。');
END;
$$;

ステップ2：RAISE NOTICEとRAISE EXCEPTIONでエラーログ

ここからが本番。各ステップで何が起きてるかログを出して、バグや流れを追いやすくしよう。

ログ付きのコード例：

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE '注文 % を処理中...', p_order_id;

    -- 1. 商品ID取得
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION '注文 % が存在しないか、product_id が未設定', p_order_id;
    END IF;
    RAISE NOTICE '注文 % の商品ID: %', p_order_id, v_product_id;

    -- 2. 在庫チェック
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION '商品 % の在庫切れ', v_product_id;
    END IF;
    RAISE NOTICE '商品 % の在庫数: %', v_product_id, v_stock_quantity;

    -- 3. 在庫数を更新
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. 注文ステータスを更新
    UPDATE orders
    SET order_status = '処理済み'
    WHERE order_id = p_order_id;

    -- 5. 成功ログ
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, '注文が正常に処理されました。');
    RAISE NOTICE '注文 % は正常に処理されました。', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- エラーログ
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'エラー: ' || SQLERRM);
    RAISE;
END;
$$;

ステップ3：インデックスで最適化

商品や注文が多いと、該当レコード探すのがボトルネックになることも。インデックスを追加して処理を速くしよう：

-- 注文テーブルの検索高速化用インデックス
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- 商品テーブルの検索高速化用インデックス
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

ステップ4：EXPLAIN ANALYZEでパフォーマンス分析

今度は、関数がどれくらい速いかチェックしてみよう。パフォーマンス分析付きで呼び出す：

EXPLAIN ANALYZE
SELECT process_order(1);

これで各ステップの所要時間が分かる。どこが一番遅いか特定できるから、さらに最適化できるよ。

ステップ5：トランザクションで信頼性アップ

信頼性を上げるには、全部の処理をトランザクションで包むのがオススメ。何か失敗したら全部ロールバックできる！

BEGIN;

-- 関数呼び出し
SELECT process_order(1);

-- トランザクションコミット
COMMIT;

関数内でSAVEPOINTやROLLBACK TO SAVEPOINTを使えば、部分的なエラー処理もできるよ。

実践課題：大量注文の処理

最後に、複数注文を一気に処理する例で締めよう。Pendingステータスの注文を全部処理する関数を作るよ：

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = '未処理'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '注文 % の処理に失敗: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

この関数を呼び出すと、未処理ステータスの注文が全部処理されて、エラーはログだけに残るよ。

こんな感じで、複雑なプロシージャのデバッグや最適化、信頼性・パフォーマンス・可読性の向上方法を紹介したよ。こういう知識は、実際のプロジェクトでアプリの成功を左右するから、ぜひ活用してみて！