PostgreSQL 11特定表触发器失效：创建时出现锁等待日志

核心原因 #

创建/删除触发器需要对目标表加AccessExclusiveLock——这是PostgreSQL中最严格的表级锁，会与所有其他表级锁（如查询的AccessShareLock、写入的RowExclusiveLock等）冲突。日志显示创建触发器时，多个进程正持有该表的锁，导致锁等待超时。虽然触发器最终被创建，但锁冲突可能导致触发器未正确注册，或后续触发逻辑因锁遗留问题无法执行。

排查与修复步骤 #

1. 确认触发器与函数状态 #

先检查触发器是否正常存在且启用：

-- 查看触发器详情
SELECT tgname, tgenabled, tgtype, tgfoid
FROM pg_trigger
WHERE tgname = 'my_trigger' AND tgrelid = 'public.payments_log'::regclass;

-- 查看触发器函数是否有效
SELECT proname, prosrc, pronamespace
FROM pg_proc
WHERE proname = 'my_trigger_function';

• 如果tgenabled字段值为D，说明触发器被禁用，执行以下命令启用：

ALTER TABLE public.payments_log ENABLE TRIGGER my_trigger;

2. 清理持有锁的进程 #

日志中列出的进程（83081、83080等）正占用表锁，需先终止这些进程（注意：需确认这些进程无重要业务操作，建议在低峰期执行）：

-- 终止指定进程
SELECT pg_terminate_backend(83081);
SELECT pg_terminate_backend(83080);
-- 依次终止其他持有锁的进程

3. 重新创建触发器 #

在表无活跃读写操作的时段（如业务低峰），重新执行触发器创建语句，确保锁申请顺利：

DROP TRIGGER IF EXISTS my_trigger ON public.payments_log;

CREATE CONSTRAINT TRIGGER my_trigger
AFTER INSERT OR UPDATE ON public.payments_log
FOR EACH ROW
EXECUTE FUNCTION my_trigger_function();

4. 验证触发器逻辑 #

手动构造数据测试触发器是否生效：

-- 插入测试数据
INSERT INTO public.payments_log (/* 填写表字段 */) VALUES (/* 对应值 */);
-- 或更新数据
UPDATE public.payments_log SET /* 字段更新 */ WHERE /* 条件 */;

然后检查触发器函数的预期效果是否实现（如数据校验、关联表更新等），若未生效，需排查函数内部逻辑是否存在错误（如条件判断错误、返回值异常等）。

5. 长期优化建议 #

• 避免在业务高峰操作触发器变更，提前规划维护窗口。
• 对高频访问的表，尽量减少需要加AccessExclusiveLock的操作（如触发器变更、表结构修改）。
• 监控表锁情况，提前识别锁冲突风险：

-- 查看当前表锁状态
SELECT locktype, relation::regclass, mode, pid, usename
FROM pg_locks
WHERE relation = 'public.payments_log'::regclass;

内容的提问来源于stack exchange，提问作者Damir Lumaza