PostgreSQL中partman.run_maintenance_proc()执行引发死锁求助

问题本质 #

从报错和日志可以明确死锁触发逻辑：

• 业务进程执行UPDATE my_table_partitioned ...时，会对主表持有RowExclusiveLock，并尝试获取目标行的行锁
• pg_partman的run_maintenance_proc()执行ALTER TABLE ... DETACH PARTITION时，需要对主表获取AccessExclusiveLock
• 两个进程互相持有对方需要的锁资源且循环等待，最终触发PostgreSQL的死锁检测机制

解决思路与方案 #

1. 错峰执行维护任务 #

这是最直接的规避方案：

• 将分区维护任务调度到业务低峰时段（比如凌晨2-4点），此时写操作极少，锁冲突概率大幅降低
• 用pg_cron定时任务实现自动化调度：

  -- 每天凌晨3点执行维护
  SELECT cron.schedule('0 3 * * *', 'CALL partman.run_maintenance_proc();');
  

2. 优化维护操作的锁等待策略 #

• 手动执行detach时，设置锁超时，避免长时间阻塞业务：

  SET lock_timeout = '30s'; -- 30秒内拿不到锁就放弃
  ALTER TABLE public.my_table_partitioned DETACH PARTITION public.my_table_partitioned_p2023w47;
  

• 调用pg_partman维护时，启用锁等待参数（适配pg_partman v4+版本）：

  -- 让维护操作等待锁，超时后自动放弃
  CALL partman.run_maintenance('public.my_table_partitioned', p_wait_for_lock := true, p_lock_wait_timeout := '60s');
  

3. 缩短业务写操作的锁持有时间 #

• 优化UPDATE语句的执行效率：确保id和create_time存在复合索引，让语句快速定位目标行，减少锁持有时长：

  CREATE INDEX IF NOT EXISTS idx_my_table_id_create_time ON public.my_table_partitioned(id, create_time);
  

• 拆分长事务：避免在同一个事务中执行多个写操作，缩短锁的持有周期，降低与维护任务冲突的概率

4. 应急处理方案（需评估业务影响） #

如果必须立即处理死锁并完成分区维护：

• 先查询当前持有主表锁的活跃进程：

  SELECT pid, query, state FROM pg_locks l 
  JOIN pg_stat_activity s ON l.pid = s.pid 
  WHERE relation = 'public.my_table_partitioned'::regclass;
  

• 临时终止阻塞维护操作的业务进程（注意：会中断正在执行的业务请求，需提前协调）：

  SELECT pg_terminate_backend(目标pid);
  

• 立即执行分区detach操作，完成后恢复业务

5. 长期优化方案 #

• 调整分区保留策略：适当延长旧分区的保留周期，减少维护操作的执行频率
• 归档旧分区数据：先将旧分区的数据同步到归档存储（比如通过逻辑复制或pg_dump），待旧分区无活跃事务后，再执行detach操作

内容的提问来源于stack exchange，提问作者Oleg Alekseiev