如何在PostgreSQL中通过触发器实现分表逻辑的自动路由

触发器函数必须用 BEFORE INSERT，因为分表路由需在数据写入前决定目标子表；AFTER INSERT 无法改变插入目标，会导致插入失败或无效操作。

触发器函数必须用 BEFORE INSERT，不能用 AFTER

因为分表路由发生在数据写入前——你要决定把这一行插到哪个子表里。如果用了 AFTER INSERT，主表（或父表）已经尝试插入失败或成功了，再跳转就晚了。特别是对声明式分区表，AFTER 触发器无法改变插入目标；对继承表结构，AFTER 会多一次无效的主表插入，还可能违反约束。

常见错误是直接复制“日志表插入后创建子表”的示例，结果发现 INSERT INTO parent_table 总报 no partition of relation "xxx" found for row。根本原因就是触发器没在写入前拦截并重定向。

• BEFORE INSERT 是唯一能用 RETURN NULL 或 RETURN NEW 控制流向的时机
• 若用继承模型（非声明式分区），RETURN NULL 表示取消主表插入，由触发器内 EXECUTE 手动写入子表
• 若用声明式分区，RETURN NEW 仍需确保目标分区存在，否则仍会报错

EXECUTE 动态建表时，INHERITS 必须带空括号

这是 PostgreSQL 继承表分表中最隐蔽的坑。很多示例漏掉括号，导致建表语法错误或继承关系未生效：

CREATE TABLE log_2026 () INHERITS (log_parent); -- ✅ 正确

CREATE TABLE log_2026 INHERITS (log_parent);    -- ❌ 报错：syntax error at or near "INHERITS"

括号表示“继承结构但不继承数据”，是强制语法要求。漏掉后不仅建表失败，后续 INSERT 也会因子表缺失而中断。

• 建表语句中 () 不可省略，哪怕你不需要任何额外列
• 若要继承索引、约束等，得显式加 INCLUDING INDEXES 或 INCLUDING CONSTRAINTS
• 用 format() 拼接时，注意单引号和双引号嵌套，避免 SQL 注入风险

分区键值解析必须严格校验，否则路由错乱

触发器里从 NEW 取分区字段（比如 NEW.log_time）后，不能直接拼进 TO 边界。例如按月分区时，date_trunc('month', NEW.log_time) 得到的是当月第一天，但若原始值是 '2026-06-31'（非法日期），PostgreSQL 会静默转成 '2026-07-01'，导致数据被写进下个月分区。

• 务必先用 IS VALID 或 to_date() 校验输入合法性，尤其来自应用层未清洗的数据
• 时间范围边界建议统一用 date_trunc('month', ...) + interval '1 month' 计算，避免手写字符串出错
• 哈希分表场景下，hashint4(NEW.user_id) % 10 要确认模数与实际子表数量一致，否则路由溢出

并发插入时，CREATE TABLE IF NOT EXISTS 不足以防冲突

多个连接同时插入同一年份/月份数据，都走到触发器里判断子表不存在，然后几乎同时执行 CREATE TABLE，其中一个必报 relation "xxx" already exists 错误。这不是语法问题，而是竞态条件。

解决方法不是加锁（PG 中无法在触发器里显式锁表），而是靠异常捕获 + 重试逻辑：

BEGIN  EXECUTE format('CREATE TABLE %I (...) INHERITS (%I)', tbl_name, parent_name);EXCEPTION WHEN duplicate_table THEN  -- 表已存在，继续插入END;

• 必须捕获 duplicate_table 异常，而不是泛泛的 OTHERS
• 不要在异常块里再执行 INSERT，应让流程自然落到后续的 EXECUTE INSERT 语句
• 高并发场景下，建议搭配 pg_advisory_lock 对年份/月份做轻量级锁，但要注意锁粒度和释放时机

触发器自动分表真正难的不是写几行 PL/pgSQL，而是处理边界：非法输入、并发冲突、分区键变更、以及——最常被忽略的——子表 DDL 变更后如何同步到所有已有子表。比如给父表加个新字段，你得手动跑一遍 ALTER TABLE ... ADD COLUMN 到每个子表，触发器本身不会帮你做这个。