PostgreSQL使用TRIGGER/RULE实现购物车表插入去重并更新数量咨询

前置依赖 #

首先确保ecommerce.store_cart_item表已创建item_id和user_id的联合唯一约束，这是冲突检测的基础：

ALTER TABLE ecommerce.store_cart_item 
ADD CONSTRAINT unique_item_user UNIQUE (item_id, user_id);

原有方案问题说明 #

• 触发器递归原因：你在触发函数内部对同一张表执行INSERT操作，这个INSERT会再次触发绑定的BEFORE INSERT触发器，形成无限递归
• RULE方案失效原因：PostgreSQL的RULE属于查询重写机制，不是逐行触发，对批量插入、带RETURNING子句的插入等场景兼容极差，且存在隐性竞态问题，官方不推荐用RULE实现行级操作逻辑

最优实现方案 #

方案1：无递归触发器（低并发场景推荐） #

调整触发函数逻辑，完全不在触发器内执行INSERT操作，从根源避免递归：

CREATE OR REPLACE FUNCTION ecommerce.add_store_cart_item()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE COST 100
AS $BODY$
BEGIN
    -- 先尝试更新已存在的同用户同商品记录
    UPDATE ecommerce.store_cart_item
    SET qty = qty + NEW.qty
    WHERE item_id = NEW.item_id AND user_id = NEW.user_id;
    
    -- 若更新命中记录，说明已存在重复，返回NULL阻止原插入
    IF FOUND THEN
        RETURN NULL;
    END IF;
    
    -- 无匹配记录，返回NEW让原插入正常执行
    RETURN NEW;
END;
$BODY$;

-- 绑定BEFORE INSERT触发器
CREATE TRIGGER trg_before_insert_store_cart_item
BEFORE INSERT ON ecommerce.store_cart_item
FOR EACH ROW
EXECUTE FUNCTION ecommerce.add_store_cart_item();

该方案优势：

• 无递归风险，逻辑简洁易维护
• 完全在数据库层实现，应用层只需正常写INSERT语句即可，无需修改业务代码
• 性能优异，UPDATE和INSERT都走联合唯一索引，开销极低

方案2：并发安全版触发器（高并发场景推荐） #

如果你的业务存在高并发插入同一条购物车记录的场景，方案1可能出现两个请求同时更新未命中，同时插入触发唯一约束报错的情况，可使用该方案规避：

CREATE OR REPLACE FUNCTION ecommerce.add_store_cart_item()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE COST 100
AS $BODY$
BEGIN
    -- 仅最外层触发器调用执行逻辑，避免递归
    IF pg_trigger_depth() <> 1 THEN
        RETURN NEW;
    END IF;

    INSERT INTO ecommerce.store_cart_item (item_id, user_id, qty)
    VALUES (NEW.item_id, NEW.user_id, NEW.qty)
    ON CONFLICT (item_id, user_id) DO UPDATE
    SET qty = store_cart_item.qty + EXCLUDED.qty;
    
    RETURN NULL;
END;
$BODY$;

该方案通过pg_trigger_depth()限制仅最外层的触发器调用会执行INSERT逻辑，完全避免递归，同时依靠ON CONFLICT处理并发冲突，无需应用层做额外适配。

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