PostgreSQL - 错误与消息

PostgreSQL - 错误和消息

在 PostgreSQL 中，了解如何处理错误和生成消息对于构建健壮的应用程序和高效调试至关重要。错误是中止当前事务的条件，而消息则提供非致命信息、警告或调试输出。

常见 SQL 错误

在以编程方式处理错误之前，识别直接 SQL 查询中常见的错误非常重要。以下是一些示例：

1. 语法错误 (SQLSTATE: 42601)

当查询违反 SQL 语法规则时发生此错误。

postgres=# SELEC * FROM employees;
ERROR:  42601: syntax error at or near "SELEC"
LINE 1: SELEC * FROM employees;
        ^

2. 未定义表 (SQLSTATE: 42P01)

当您尝试查询不存在的表时发生此错误。

postgres=# SELECT * FROM products;
ERROR:  42P01: relation "products" does not exist
LINE 1: SELECT * FROM products;
                      ^

3. 唯一约束冲突 (SQLSTATE: 23505)

当您尝试向具有 UNIQUE 或 PRIMARY KEY 约束的列插入已存在的值时发生此错误。

-- 假设 'employees' 表存在且 'id' 列具有 PRIMARY KEY 约束
postgres=# INSERT INTO employees (id, first_name) VALUES (1, 'Peter');
ERROR:  23505: duplicate key value violates unique constraint "employees_pkey"
DETAIL:  Key (id)=(1) already exists.

4. 除零错误 (SQLSTATE: 22012)

尝试将数字除以零会导致此数学错误。

postgres=# SELECT 100 / 0;
ERROR:  22012: division by zero

使用 PL/pgSQL 处理错误

对于函数或存储过程中的更复杂逻辑，您可以使用 BEGIN...EXCEPTION...END 块优雅地处理错误。这可以防止您的整个函数在发生可预测的错误时失败。

语法

DO $$
BEGIN
    -- 可能导致错误的代码
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        -- 指定错误条件的处理代码
    WHEN OTHERS THEN
        -- 其他任何错误的处理
END;
$$;

The condition 可以是特定的 SQLSTATE 代码（例如，unique_violation 对应 ‘23505’）。

示例：处理唯一约束冲突

让我们创建一个函数来添加新员工，但如果电子邮件重复，它不会失败，而是会发出通知 (notice)。

-- 首先，为 email 添加唯一约束
ALTER TABLE employees ADD COLUMN email VARCHAR(100) UNIQUE;
INSERT INTO employees (id, first_name, email) VALUES (10, 'Test', 'test@example.com');

-- 现在，带有错误处理的 DO 块
DO $$
BEGIN
    INSERT INTO employees (id, first_name, email) VALUES (11, 'Another', 'test@example.com');
    RAISE NOTICE 'Employee added successfully!';
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'The email ''test@example.com'' already exists. No new employee was added.';
END;
$$;

运行此代码块不会产生错误。相反，它将输出我们的自定义通知：

NOTICE:  The email 'test@example.com' already exists. No new employee was added.
DO

使用 RAISE 生成消息

RAISE 语句用于在 PL/pgSQL 代码中报告消息和抛出错误。它是调试和通信状态的宝贵工具。

语法

RAISE level 'format' [, expression ...];

常见的 level（级别）包括：

• NOTICE：用于不停止执行的信息性消息。对调试很有用。
• WARNING：用于关于潜在问题的警告。
• EXCEPTION：这是最关键的级别。它会引发一个错误，该错误会中止当前事务（除非被 EXCEPTION 块捕获）。

示例：使用 RAISE 进行验证

让我们创建一个函数，它更新员工的薪水，但如果新薪水低于旧薪水，则抛出自定义异常 (exception)。

CREATE OR REPLACE FUNCTION update_salary(emp_id INT, new_salary NUMERIC)
RETURNS void AS $$
DECLARE
    current_salary NUMERIC;
BEGIN
    SELECT salary INTO current_salary FROM employees WHERE id = emp_id;

    IF new_salary < current_salary THEN
        RAISE EXCEPTION 'Salary decrease is not allowed. Current: %, New: %', current_salary, new_salary;
    END IF;

    UPDATE employees SET salary = new_salary WHERE id = emp_id;
    RAISE NOTICE 'Salary for employee % updated successfully.', emp_id;
END;
$$ LANGUAGE plpgsql;

-- 尝试执行一个禁止的更新
SELECT update_salary(1, 40000.00);

此调用将失败并产生我们的自定义错误消息：

ERROR:  Salary decrease is not allowed. Current: 55000.00, New: 40000.00
CONTEXT:  PL/pgSQL function update_salary(integer,numeric) line 8 at RAISE

调试策略

• 大量使用 RAISE NOTICE： 在复杂函数内部随意添加 RAISE NOTICE 'Reached point A with value: %', my_variable; 来跟踪执行流程和检查变量状态。
• 检查 client_min_messages： 如果您看不到 NOTICE 消息，您的客户端可能配置为忽略它们。运行 SHOW client_min_messages; 并在需要时将其设置为 notice (SET client_min_messages = 'notice';)。
• 隔离查询： 如果函数失败，从中提取查询并手动使用硬编码值运行它们，以查看是哪个查询导致了错误。
• 仔细阅读错误信息： PostgreSQL 错误消息非常有用。它们通常包含提示并指向发生错误的精确行和字符。