PostgreSQL分区表创建行触发器报错及分区方案咨询

一、"分区表无法创建行触发器"问题的解决方法 #

报错根因：PostgreSQL 12及以下版本的声明式分区父表完全不支持行级触发器；13及以上版本虽然开放了行级触发器支持，但数据插入时的分区路由逻辑优先级高于触发器执行，未匹配到分区时会直接抛出路由错误，根本不会进入触发器逻辑，因此原本直接在父表挂触发器自动建分区的思路无法实现，可选择以下落地方案：

• 方案1：视图+INSTEAD OF触发器（兼容原有自定义逻辑）
  不直接对分区父表做插入，额外创建一张与父表结构完全一致的视图，所有业务写入都请求该视图，在视图上创建INSTEAD OF INSERT行级触发器执行自动建分区+写入逻辑，绕开分区父表的触发器限制。
  首先修正原有触发器函数的几个bug：
  1. 声明的host变量未赋值，需从NEW对象中取值
  2. 拼写错误：判断表存在时的partation_name为错写，应为partition_name
  3. 建分区的format语句括号位置错误，参数未传入
  4. 缺省schema判断，易因不同schema下的同名表出现误判
  5. 缺少并发创表的容错逻辑，高并发下易出现重复建表报错
     修正后的函数参考：

CREATE OR REPLACE FUNCTION service_function()
RETURNS TRIGGER AS $$
DECLARE
    host TEXT;
    partition_name TEXT;
BEGIN
    host := NEW.host;
    -- 处理host里的特殊字符，避免生成非法表名
    partition_name := 'services_' || replace(replace(host, '.', '_'), '-', '_');
    -- 判断分区是否存在，限定public schema
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_schema = 'public' AND table_name = partition_name
    ) THEN
        RAISE NOTICE 'Auto create partition: %', partition_name;
        -- 加IF NOT EXISTS避免并发插入同host时重复建表报错
        EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF public.services FOR VALUES IN (%L)', partition_name, host);
    END IF;
    EXECUTE format('INSERT INTO %I (id, service_name, host) VALUES ($1, $2, $3)', partition_name) 
    USING NEW.id, NEW.service_name, NEW.host;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

之后创建视图与触发器即可：

-- 业务写入全部走该视图，不要直接写分区父表
CREATE VIEW public.v_services AS SELECT * FROM public.services;

CREATE TRIGGER insert_service_trigger
    INSTEAD OF INSERT ON public.v_services
    FOR EACH ROW EXECUTE FUNCTION service_function();

• 方案2：使用pg_partman扩展（生产环境首选）
  不建议生产环境使用自定义触发器维护分区，成熟方案是直接使用pg_partman分区管理扩展，原生支持LIST分区的自动创建、索引继承、权限同步、过期分区清理等能力，鲁棒性远高于自定义触发器，且专门处理了高并发写入下的分区创建锁问题，适合100G以上规模的业务表使用，配置完成后无需额外写触发器逻辑，直接写入父表即可自动创建缺失分区。

二、按host字段做LIST分区的设计合理性 #

你的分区设计思路完全合理，完全匹配当前业务场景：

• 所有SELECT查询都携带host过滤条件，分区裁剪可以100%生效，查询时仅扫描对应host的单个分区，不会触发全分区扫描，性能收益明显
• 100G规模的单表在VACUUM、索引重建、备份等维护操作中锁粒度大、耗时久，按host拆分分区后，单分区数据量可控，维护操作可针对单分区执行，大幅降低运维成本
• 可根据不同host的访问热度灵活调整分区存储位置，比如高频访问的host分区放在高性能SSD，冷数据host分区放在大容量机械盘，平衡性能与成本

需要注意的优化点：

• 控制单张父表的分区总数在1000以内，过多分区会导致SQL执行计划生成耗时明显上升，如果host取值量级过大，可调整分区规则（比如按host哈希+列表组合分区）控制单分区数量
• 表名生成逻辑要处理host字段中的特殊字符（比如点、横杠、斜杠等），避免生成非法表名导致建分区失败
• 高并发写入场景下，自定义触发器方案存在一定的性能开销和锁冲突风险，优先使用pg_partman做分区管理

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