使用MERGE INTO插入数据时无法拦截已存在数据的问题排查

使用PostgreSQL的MERGE INTO方法向qm_log_details表插入数据时遇到偶发报错：调用接收数组参数的fill_logs存储过程批量插入数据时，明明表中log_id已设唯一约束，且逻辑上MERGE INTO应拦截已存在的log_id，但偶尔会出现Key (log_id)=(xxx) already exists的错误。想排查遗漏点，是否需要将每条数据放在单独事务中插入？

表结构 #

CREATE TABLE IF NOT EXISTS public.qm_log_details
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    log_id bigint,
    log_name character varying COLLATE pg_catalog."default",
    log_detail character varying COLLATE pg_catalog."default",
    CONSTRAINT log_id UNIQUE (log_id)
        INCLUDE(log_id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."qm_log_details "
    OWNER to postgres;

存储过程代码 #

CREATE OR REPLACE PROCEDURE public.fill_logs(IN log_ids bigint[], IN log_names character varying[], IN log_details character varying[])
    LANGUAGE 'plpgsql'
    
AS $BODY$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 1..cardinality(log_ids) LOOP
        MERGE INTO qm_log_details as target
        USING (VALUES(log_ids[i],log_names[i],log_details[i])) AS newSource(log_id,log_name,log_detail)
        ON target.log_id = newSource.log_id
        --WHEN MATCHED THEN
        WHEN NOT MATCHED THEN
            INSERT (log_id,log_name,log_detail)
            VALUES(log_ids[i],log_names[i],log_detail[i]);
    END LOOP;
END;
$BODY$;

调用代码（node-postgres） #

const ids=[1123,2322,2391];
const names=["loga","logb","logc"]
const details = ["d1","d2","d2"]
await this.client.query("CALL fill_logs($1,$2,$3);",[ids,names,details);

问题根源 #

1. 并发冲突：多请求同时调用存储过程且包含相同log_id时，MERGE INTO的检查与插入并非原子操作——两个请求可能同时通过ON条件检查（此时该log_id尚未被插入），随后同时执行插入，触发唯一约束报错。
2. 输入数组重复：传入的log_ids数组内部可能存在重复值，同一次调用中循环执行MERGE INTO时，第一次插入成功后，后续重复的log_id可能因事务隔离特性或逻辑疏漏触发报错。

解决办法 #

1. 去重输入并优化MERGE逻辑
   把循环逐条执行MERGE改成单次批量MERGE，同时对输入数据去重，既提升效率又避免同批次内的重复问题：

   CREATE OR REPLACE PROCEDURE public.fill_logs(IN log_ids bigint[], IN log_names character varying[], IN log_details character varying[])
       LANGUAGE 'plpgsql'
   AS $BODY$
   BEGIN
       MERGE INTO qm_log_details as target
       USING (
           SELECT DISTINCT ON (log_id) log_id, log_name, log_detail
           FROM unnest(log_ids, log_names, log_details) AS t(log_id, log_name, log_detail)
       ) AS newSource(log_id,log_name,log_detail)
       ON target.log_id = newSource.log_id
       WHEN NOT MATCHED THEN
           INSERT (log_id,log_name,log_detail)
           VALUES(newSource.log_id, newSource.log_name, newSource.log_detail);
   END;
   $BODY$;
   

2. 改用INSERT ... ON CONFLICT替代MERGE
INSERT ... ON CONFLICT DO NOTHING是原子操作，在高并发场景下比MERGE INTO更可靠，能彻底避免并发插入冲突：

   CREATE OR REPLACE PROCEDURE public.fill_logs(IN log_ids bigint[], IN log_names character varying[], IN log_details character varying[])
       LANGUAGE 'plpgsql'
   AS $BODY$
   BEGIN
       INSERT INTO qm_log_details (log_id, log_name, log_detail)
       SELECT DISTINCT ON (log_id) log_id, log_name, log_detail
       FROM unnest(log_ids, log_names, log_details) AS t(log_id, log_name, log_detail)
       ON CONFLICT (log_id) DO NOTHING;
   END;
   $BODY$;
   

3. 无需拆分单事务
   将每条数据放在单独事务中插入完全没必要，反而会大幅降低批量插入性能，且无法从根本上解决并发冲突问题——单事务拆分后，并发请求的插入操作仍可能出现冲突。

内容的提问来源于stack exchange，提问作者Kamuran Sönecek