PL/pgSQL调试和优化时常见错误解析

今天，为了给PL/pgSQL这段史诗级旅程画个句号，我们来聊聊调试和优化函数、过程时最容易踩的那些坑。知道这些坑，不仅能帮你以后少踩雷，还能让你遇到bug时更快定位问题。

调试和优化时的典型错误

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;

调用这个函数时，你会遇到用NULL参与计算的报错，因为discount_rate变量一开始就是空的。

怎么避免：

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表里根本没有这条记录怎么办？

怎么避免： 加上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. 忽略查询性能

这是所有数据库开发者的头号大敌。比如你写了个看起来没毛病的函数，结果跑得贼慢。慢的原因通常是没加索引，或者SQL执行计划很烂。

慢查询例子：

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看看SQL效率咋样：

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

2. 给常用字段建索引：

CREATE INDEX idx_orders_total ON orders(total);

4. 事务隔离级别用错了

执行复杂过程时，有时会因为对事务隔离级别理解不对而出错。比如两个事务同时更新同一条记录，就可能死锁（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：查询慢，因为没加索引

场景：你有个SQL按某列筛选，但这列没索引。

修复：给对应列加个索引。

错误2：函数逻辑太乱，难调试

场景：函数里逻辑太多，没拆成子函数。

修复：把复杂函数拆成小函数，代码更清晰，调试也简单。

错误3：RAISE EXCEPTION用错了

场景：所有错误都用RAISE EXCEPTION，连小问题也抛异常。

修复：普通信息用RAISE NOTICE，只有严重问题才用RAISE EXCEPTION。

RAISE NOTICE '一切正常——函数当前阶段已完成。';
RAISE EXCEPTION '出问题了！注意检查输入参数。';

防止出错的建议

1. 加日志：关键步骤用RAISE NOTICE追踪执行过程。
2. 多测试：经常用测试数据跑一下函数和过程。
3. 保持代码清晰：复杂函数拆成小函数和过程。
4. 分析性能：用EXPLAIN ANALYZE确保SQL效率高。
5. 做好异常处理：总是加异常处理块，防止出错崩溃。

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION '发生了意外错误: %', SQLERRM;

这样你就能更自信地搞定各种bug，也能提前预防问题啦。