PostgreSQL 12/13 如何基于外关联表日期实现跨表分区一致性

文档描述的逻辑有效性确认 #

你查到的两类官方文档说明，在PG12、13版本完全生效，没有版本变更：

• 挂载在分区根表上的行级BEFORE触发器，在修改分区键时确实存在路由限制：触发器执行阶段系统还没完成初始分区匹配，这阶段修改分区键很容易出现目标分区错位、路由报错，这也是你实测INSERT前触发器跑不通的核心原因。
• 常规DML操作中如果更新分区键值，让行匹配到其他分区范围时，系统自动把旧行从原分区移除、写入目标分区的逻辑，从PG11引入声明式分区开始就一直保留，12、13版本没有改动。

数据库侧透明赋值的实现路径 #

不需要应用侧手动维护question_date字段，完全可以在数据库侧实现自动赋值路由，绕开BEFORE触发器的限制：

• 把原来的BEFORE触发器换成INSTEAD OF行级触发器，挂载在Answers、Comments的分区根表上：触发器收到INSERT请求时，先根据NEW行里的question_id，从Questions分区根表查出对应的问题创建时间，把这个值直接赋值给NEW.question_date，再手动把整行写入匹配的目标分区。
  这个逻辑完全不需要应用传额外字段，也不会和系统默认的分区路由逻辑冲突，在PG12、13上可以稳定运行。
• 如果不想用触发器，也可以在Answers和Questions的关联外键上定义校验触发器，写入Answers行时自动提取关联问题的时间戳作为分区键值，这个执行路径不会和分区路由的顺序产生冲突。

多表对应分区的原子Detach方案 #

你担心的detach过程中出现悬空外指针的问题，靠单事务的原子特性就可以完全避免，不需要额外加异步锁或者后台清理任务：

• 把所有分区detach操作全部放到同一个数据库事务里执行，以2021年3月的关联分区为例，执行顺序如下：
  • 开启事务
  • 执行ALTER TABLE comments DETACH PARTITION comments_2021_03;
  • 执行ALTER TABLE answers DETACH PARTITION answers_2021_03;
  • 执行ALTER TABLE questions DETACH PARTITION questions_2021_03;
  • 事务提交
• 这个执行过程中，受事务隔离级别保护，其他会话永远只会看到两种一致状态：要么三个时间维度的分区都正常挂载在对应根表下，要么三个分区都完成detach，不会出现「Questions分区已经detach，但Answers、Comments分区还挂载着被悬空外键关联」的中间态。
• 如果detach过程中检测到有其他事务持有这几个分区的行锁，整个事务会一起回滚，不会出现部分分区detach成功的不一致状态。
• detach完成后，所有关联分区都是独立的普通表，可以直接在同一个事务里做整表删除或者归档操作，因为已经脱离了根表的外键约束链，操作速度非常快。

实际踩坑提醒 #

• 不要继续尝试用BEFORE行级触发器修改分区键做自动路由：这个路径在PG12、13上有固定的执行顺序冲突——触发器执行完成后，系统会先按照触发器执行前的原始分区键值做第一次路由匹配，再执行触发器修改分区键的逻辑，最后才完成跨分区迁移，整个过程会产生不必要的行搬迁IO，极端情况下还会触发长时间锁等待。
• 不需要为了路由逻辑舍弃自增主键、改成时间戳当主键：你当前设计的「用关联父表的时间戳作为子表分区键」的结构，只要把触发器逻辑换成INSTEAD OF的实现，完全可以保留原有自增主键，不会破坏现有表结构的设计合理性。

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