PostgreSQL如何实现类似Oracle的SCHEMA级别错误捕获触发器功能

PostgreSQL 没有直接对应 Oracle AFTER SERVERERROR ON SCHEMA 的原生触发器语法，可通过事件触发器+错误上下文捕获的组合实现同等能力，具体实现步骤如下：

1. 前置准备：创建错误日志存储表 #

用于持久化存储捕获到的错误信息，表结构可按需扩展：

CREATE TABLE IF NOT EXISTS schema_error_log (
    log_id BIGSERIAL PRIMARY KEY,
    error_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    user_name TEXT DEFAULT current_user,
    schema_name TEXT,
    error_code TEXT,
    error_message TEXT,
    error_detail TEXT,
    executed_sql TEXT,
    client_addr INET DEFAULT inet_client_addr()
);

2. 捕获 Schema 下的 DDL 执行错误 #

通过 PostgreSQL 事件触发器即可实现指定 Schema 下的 DDL 错误捕获：

2.1 创建事件触发器处理函数 #

CREATE OR REPLACE FUNCTION log_schema_ddl_error()
RETURNS event_trigger AS $$
DECLARE
    v_schema_name TEXT := current_schema();
    v_error_code TEXT;
    v_error_msg TEXT;
    v_error_detail TEXT;
    v_executed_sql TEXT := current_query();
BEGIN
    -- 替换为你需要监听的 Schema 名称
    IF v_schema_name != 'target_schema' THEN
        RETURN;
    END IF;

    -- 获取错误上下文信息
    GET STACKED DIAGNOSTICS
        v_error_code = RETURNED_SQLSTATE,
        v_error_msg = MESSAGE_TEXT,
        v_error_detail = PG_EXCEPTION_DETAIL;

    -- 写入错误日志表
    INSERT INTO schema_error_log (schema_name, error_code, error_message, error_detail, executed_sql)
    VALUES (v_schema_name, v_error_code, v_error_msg, v_error_detail, v_executed_sql);
EXCEPTION
    WHEN OTHERS THEN
        -- 避免日志触发器本身报错影响业务执行
        RAISE WARNING '错误日志记录失败: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

2.2 创建事件触发器 #

CREATE EVENT TRIGGER trigger_log_schema_ddl_error
ON ddl_command_end
-- 可按需添加要监听的DDL类型，移除WHEN TAG子句则监听所有DDL操作
WHEN TAG IN (
    'CREATE TABLE', 'ALTER TABLE', 'DROP TABLE',
    'CREATE INDEX', 'ALTER INDEX', 'DROP INDEX',
    'CREATE FUNCTION', 'ALTER FUNCTION', 'DROP FUNCTION'
)
EXECUTE FUNCTION log_schema_ddl_error();

3. 捕获 Schema 下的 DML/查询运行时错误 #

PostgreSQL 目前没有原生的全量 Schema 级 DML 错误触发器，可根据业务场景选择以下两种方案实现：

• 方案一：PL/pgSQL 函数统一封装
  把指定 Schema 下的业务逻辑都封装为 PL/pgSQL 函数，在函数中统一添加异常捕获逻辑，示例如下：

  CREATE OR REPLACE FUNCTION target_schema.biz_update(p_id INT, p_val TEXT)
  RETURNS VOID AS $$
  BEGIN
      -- 业务SQL逻辑
      UPDATE target_schema.my_table SET col1 = p_val WHERE id = p_id;
  EXCEPTION
      WHEN OTHERS THEN
          -- 写入错误日志
          INSERT INTO schema_error_log (schema_name, error_code, error_message, executed_sql)
          VALUES ('target_schema', SQLSTATE, SQLERRM, current_query());
          -- 按需抛出原始错误，不影响上游业务感知异常
          RAISE;
  END;
  $$ LANGUAGE plpgsql;
  

• 方案二：日志同步
  修改 PostgreSQL 配置文件postgresql.conf，开启错误日志记录：

  log_statement = 'all'
  log_min_error_statement = 'error'
  log_line_prefix = '%u %s %e ' # 增加用户名、Schema、错误码等前缀字段
  

  再通过定时任务将符合指定 Schema 条件的错误日志同步到schema_error_log表中，适合对日志实时性要求不高的场景。

内容的提问来源于stack exchange，提问作者Christophe Briolant