pg_dbms_errlog

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

shared_preload_libraries = 'pg_dbms_errlog';

CREATE EXTENSION pg_dbms_errlog;

shared_preload_libraries = 'pg_dbms_errlog'

CREATE EXTENSION pg_dbms_errlog;
LOAD 'pg_dbms_errlog';

BEGIN;
CALL dbms_errlog.create_error_log('employees');
END;
-- 创建表 "ERR$_employees"，包含错误日志列

-- 使用自定义名称和模式：
BEGIN;
CALL dbms_errlog.create_error_log('hr.employees', '"ERRORS"."ERR$_EMPTABLE"');
END;

SET pg_dbms_errlog.enabled TO true;       -- 启用错误日志
SET pg_dbms_errlog.query_tag TO 'daily_load';  -- 用于标识语句的标签
SET pg_dbms_errlog.reject_limit TO 10;    -- 回滚前的最大错误数（-1=无限）
SET pg_dbms_errlog.synchronous TO 'transaction'; -- 'transaction'、'query' 或 'off'
SET pg_dbms_errlog.no_client_error TO true;      -- 抑制客户端错误消息

LOAD 'pg_dbms_errlog';
LOAD 'pg_statement_rollback';

CREATE TABLE hr.raises (emp_id integer, sal integer CHECK(sal > 8000));

BEGIN;
CALL dbms_errlog.create_error_log('hr.raises');
END;

SET pg_dbms_errlog.query_tag TO 'daily_load';
SET pg_dbms_errlog.reject_limit TO 10;
SET pg_dbms_errlog.enabled TO true;

BEGIN;
SET pg_statement_rollback.enabled TO on;
INSERT INTO hr.raises VALUES (145, 15400);  -- 成功
INSERT INTO hr.raises VALUES (161, 7700);   -- 失败（已记录）
ROLLBACK TO SAVEPOINT "PgSLRAutoSvpt";
INSERT INTO hr.raises VALUES (175, 9680);   -- 成功
COMMIT;

SELECT * FROM "ERR$_raises";
-- pg_err_number$  | 23514
-- pg_err_mesg$    | new row for relation "raises" violates check constraint
-- pg_err_optyp$   | I
-- pg_err_tag$     | daily_load
-- pg_err_query$   | INSERT INTO hr.raises VALUES (161, 7700);

SELECT dbms_errlog.publish_queue();