更新主分区表movie时触发分区movie_comedy触发器的可行方案咨询

可以实现主表操作时触发对应分区的触发器逻辑，问题的核心原因是：PostgreSQL中，直接对分区表父表执行DML操作时，不会触发绑定在子分区上的触发器——父表的操作会将数据路由到对应分区，但触发器的触发规则是基于你直接操作的表对象，而非最终数据落地的分区。

下面提供两种可行的实现方式：

方式一：将触发器移至主表并添加分区判断 #

把原触发器从movie_comedy分区迁移到主表movie上，同时修改触发器函数，仅当操作涉及movie_comedy分区的行时执行刷新逻辑。

1. 修改触发器函数 #

假设你的分区是基于genre字段的LIST分区，值为'comedy'，调整后的函数如下：

CREATE OR REPLACE FUNCTION refresh() RETURNS TRIGGER AS $$
BEGIN
    CASE TG_OP
        WHEN 'INSERT', 'UPDATE' THEN
            -- 检查新增/更新的行是否属于comedy分区
            IF EXISTS (SELECT 1 FROM NEW TABLE WHERE genre = 'comedy') THEN
                REFRESH MATERIALIZED VIEW your_materialized_view_name;
            END IF;
        WHEN 'DELETE' THEN
            -- 检查删除的行是否属于comedy分区
            IF EXISTS (SELECT 1 FROM OLD TABLE WHERE genre = 'comedy') THEN
                REFRESH MATERIALIZED VIEW your_materialized_view_name;
            END IF;
    END CASE;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. 在主表创建触发器 #

CREATE TRIGGER refresh_after_dml
AFTER INSERT OR UPDATE OR DELETE ON movie
FOR EACH STATEMENT
EXECUTE FUNCTION refresh();

这样无论你直接操作movie_comedy分区，还是通过主表movie操作属于该分区的行，只要涉及comedy类型的数据变更，都会触发物化视图的刷新。

方式二：主表触发器统一调用分区逻辑 #

如果你希望保留分区上的触发器，也可以在主表创建一个触发器，当操作涉及movie_comedy分区时，手动调用对应的刷新逻辑。这种方式适合多分区各自有独立触发器逻辑的场景：

CREATE OR REPLACE FUNCTION main_table_refresh() RETURNS TRIGGER AS $$
BEGIN
    -- 判断操作是否涉及movie_comedy分区
    CASE TG_OP
        WHEN 'INSERT', 'UPDATE' THEN
            IF EXISTS (SELECT 1 FROM NEW TABLE WHERE genre = 'comedy') THEN
                PERFORM refresh(); -- 调用原分区触发器的函数
            END IF;
        WHEN 'DELETE' THEN
            IF EXISTS (SELECT 1 FROM OLD TABLE WHERE genre = 'comedy') THEN
                PERFORM refresh();
            END IF;
    END CASE;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 主表触发器
CREATE TRIGGER main_refresh_trigger
AFTER INSERT OR UPDATE OR DELETE ON movie
FOR EACH STATEMENT
EXECUTE FUNCTION main_table_refresh();

注意：如果使用这种方式，建议保留原分区上的触发器，确保直接操作分区时仍能触发刷新。

内容的提问来源于stack exchange，提问作者kylie.zoltan