PL/pgSQLのデバッグと最適化でよくあるミスの解説

今日は、PL/pgSQLの壮大な旅の締めくくりとして、デバッグや最適化のときにみんながよくやっちゃうミスをまとめてみるよ。こういうミスを知っておくと、将来トラブルを避けられるし、もしバグが出ても効率よく原因を探せるようになるからね。

デバッグと最適化でよくあるミス

1. 変数の使い方を間違える

PL/pgSQLで関数を書くとき、変数の宣言や使い方を間違えるのは超ありがち。たとえば、変数の型を明示し忘れたり、パラメータで渡された値と混乱したり。実際にどうなるか見てみよう：

CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC;
BEGIN
    -- あっ！discount_rateの初期化を忘れた
    RETURN order_total * discount_rate;
END;
$$ LANGUAGE plpgsql;

この関数を呼び出すと、discount_rateが初期化されてないから、計算でNULLを使っちゃってエラーになるよ。

どうやって防ぐか：

1. 変数を宣言するときは、必ずデフォルト値をセットしよう：

   DECLARE
       discount_rate NUMERIC := 0.1; -- デフォルト値

2. RAISE NOTICEで変数の値をチェックして、ちゃんと期待通りになってるか確認しよう：

RAISE NOTICE 'discount_rateの値: %', discount_rate;

2. エラーログを出さない

もう一つよくあるのが、ログ出力の仕組みがないこと。何かうまくいかないとき、ログがなかったら真っ暗な部屋で黒猫を探すようなもんだよ。しかも猫がいるかどうかも分からないって感じ。

ログなしの関数の例：

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- 注文処理の複雑なロジック
    UPDATE orders SET status = 'processed' WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

order_idが変だったら？ordersテーブルにそのIDのレコードがなかったら？

どうやって防ぐか： RAISE NOTICEやRAISE EXCEPTIONで大事なステップをログに残そう：

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- 入力データをログ
    RAISE NOTICE 'ID % の注文を処理中', order_id;

    -- 複雑な処理
    UPDATE orders SET status = 'processed' WHERE id = order_id;

    -- 結果をログ
    RAISE NOTICE 'ID % の注文ステータスを更新した', order_id;
END;
$$ LANGUAGE plpgsql;

これで、どこでエラーが起きてるかすぐ分かるようになるよ。

3. クエリのパフォーマンスを無視する

これはDB開発者の最大の敵の一つ。関数自体は正しそうでも、やたら遅いことがある。遅いクエリの主な原因は、インデックスがないとか、実行プランがイケてないとかだよ。

遅いクエリの例：

CREATE OR REPLACE FUNCTION get_large_orders()
RETURNS TABLE(order_id INT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE total > 1000;
END;
$$ LANGUAGE plpgsql;

ordersテーブルのtotalカラムにインデックスがなかったら、全件スキャンになって超非効率。

どうやって防ぐか：

1. EXPLAIN ANALYZEでクエリの効率をチェックしよう：

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE total > 1000;

2. よく使うカラムにはインデックスを作ろう：

CREATE INDEX idx_orders_total ON orders(total);

4. トランザクション分離レベルの使い方を間違える

複雑なプロシージャを実行するとき、トランザクションの分離レベルをちゃんと理解してないとエラーになることがある。たとえば、2つのトランザクションが同じレコードを同時に更新しようとすると、deadlockになるかも。

deadlockの例：

BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;

-- 他のトランザクションのロック待ち
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

もう一つのトランザクションが逆の順番で操作したら、相互ロックになるよ。

どうやって防ぐか：

1. 操作の順番をちゃんと考えて、統一しよう。
2. 必要ならSERIALIZABLE分離レベルを使おう。

5. エラー処理がない

エラー処理は良い習慣ってだけじゃなくて、コードの安定性を保つための必須テク。たとえば、次のコードはエラー処理が全然ない：

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
END;
$$ LANGUAGE plpgsql;

order_idがすでに存在してたら、duplicate key value violates unique constraintエラーになるよ。

どうやって防ぐか： 例外処理ブロックを使おう：

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'ID % の注文はすでに存在してるよ！', order_id;
END;
$$ LANGUAGE plpgsql;

ミスの例とその直し方

ミス1: インデックスがないせいでクエリが遅い

状況: テーブルをカラムで絞り込むクエリがあるけど、そのカラムにインデックスがない。

直し方: 該当カラムにインデックスを作ろう。

ミス2: 関数のロジックがごちゃごちゃでデバッグしにくい

状況: 関数がロジック詰め込みすぎで、サブ関数に分けてない。

直し方: 複雑な関数は小さいサブ関数に分けよう。読みやすくなるし、デバッグも楽になるよ。

ミス3: RAISE EXCEPTIONの使い方が間違ってる

状況: どんなエラーでもRAISE EXCEPTIONを使ってる。

直し方: 情報だけならRAISE NOTICE、本当にやばいときだけRAISE EXCEPTIONを使おう。

RAISE NOTICE '大丈夫 — 関数のこの段階は完了したよ。';
RAISE EXCEPTION 'なんか壊れた！入力パラメータをチェックしてね。';

ミスを防ぐためのアドバイス

1. ログを追加しよう: 関数の大事なところでRAISE NOTICEを使って進捗を追跡しよう。
2. 関数をテストしよう: テストデータで関数やプロシージャを定期的にチェックしよう。
3. コードの読みやすさを保とう: 複雑な関数は小さいサブ関数やプロシージャに分けよう。
4. パフォーマンスを分析しよう: EXPLAIN ANALYZEでクエリが効率的か確認しよう。
5. 予想外に備えよう: エラー処理ブロックは必ず追加しておこう。

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION '予期しないエラーが発生した: %', SQLERRM;

こうしておけば、エラーにも自信を持って対応できるし、将来のバグも防げるよ。