PostgreSQL如何实现invoiceitems表sortorder自动重排且避免触发器无限递归

方案1：使用内置pg_trigger_depth()函数限制递归（最简便） #

pg_trigger_depth()返回当前触发器的嵌套层数，首次触发时值为1，触发器内部的UPDATE操作再次触发本触发器时，层数会变为2，此时直接退出即可阻断递归，完全兼容PostgreSQL 11及以上版本。
触发器函数示例：

CREATE OR REPLACE FUNCTION reorder_invoice_items()
RETURNS TRIGGER AS $$
DECLARE
    target_invoice INTEGER;
BEGIN
    -- 嵌套层数大于1说明是重排逻辑内部触发的UPDATE，直接退出
    IF pg_trigger_depth() > 1 THEN
        RETURN NULL;
    END IF;

    -- 获取需要重排的发票ID
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        target_invoice := NEW.idinvoice;
    END IF;
    -- 如需支持删除条目后自动重排，可放开以下注释
    -- IF TG_OP = 'DELETE' THEN
    --     target_invoice := OLD.idinvoice;
    -- END IF;

    -- 你的重排逻辑
    WITH pre AS (
        SELECT idinvoiceitem,10*ROW_NUMBER() OVER (ORDER BY sortorder,idinvoiceitem) AS rownum
        FROM invoiceitems
        WHERE idinvoice = target_invoice
        ORDER BY sortorder,idinvoiceitem
    )
    UPDATE invoiceitems di
    SET sortorder=rownum
    FROM pre p
    WHERE p.idinvoiceitem=di.idinvoiceitem;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

创建触发器，使用AFTER行级触发器，仅在必要操作时触发减少性能损耗：

CREATE TRIGGER trigger_reorder_invoice_items
AFTER INSERT OR UPDATE OF sortorder, idinvoice
ON invoiceitems
FOR EACH ROW
EXECUTE FUNCTION reorder_invoice_items();

方案2：使用会话级变量作为信号量（适合多触发器嵌套场景） #

如果业务中存在其他触发器嵌套调用，用深度判断可能出现误判，可以用事务级临时变量作为重排标记，避免递归：

CREATE OR REPLACE FUNCTION reorder_invoice_items()
RETURNS TRIGGER AS $$
DECLARE
    target_invoice INTEGER;
BEGIN
    -- 检测重排标记已存在则直接退出
    IF current_setting('biz.invoice_reorder_running', true) = 'true' THEN
        RETURN NULL;
    END IF;
    -- 标记重排中，set_config第三个参数为true表示变量仅当前事务有效
    PERFORM set_config('biz.invoice_reorder_running', 'true', true);

    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        target_invoice := NEW.idinvoice;
    END IF;

    -- 重排逻辑
    WITH pre AS (
        SELECT idinvoiceitem,10*ROW_NUMBER() OVER (ORDER BY sortorder,idinvoiceitem) AS rownum
        FROM invoiceitems
        WHERE idinvoice = target_invoice
        ORDER BY sortorder,idinvoiceitem
    )
    UPDATE invoiceitems di
    SET sortorder=rownum
    FROM pre p
    WHERE p.idinvoiceitem=di.idinvoiceitem;

    -- 清除标记
    PERFORM set_config('biz.invoice_reorder_running', 'false', true);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

并发优化建议 #

如果存在多会话同时修改同一张发票条目的场景，可以在重排逻辑前增加行锁避免顺序冲突：

-- 锁定对应发票行，其他会话修改同发票条目时会等待当前重排完成
SELECT 1 FROM invoices WHERE idinvoice = target_invoice FOR UPDATE;

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