INSERT...SELECT因源表数据变更触发外键约束错误的解决办法

我要运行以下SQL语句：

INSERT INTO contacts_fields (
  contact_id, field_name, numeric_value
)
select distinct      
  c.id, 
  'storeId', 
  1
from 
  contacts c 
  inner join contacts_devices cd on cd.contact_id = c.id 
  and c.id NOT IN (
    SELECT 
      contact_id 
    FROM 
      contacts_fields
  )
group by 
  c.id

执行时出现错误：

SQL Error [23503]: ERROR: insert or update on table "contact_fields" violates foreign key constraint "contacts_fields_fkey"
Detail: Key (contact_id)=(2425542) is not present in table "contacts".

推测原因是contacts表数据实时变动，SELECT查询与INSERT插入的间隙中，ID为2425542的contact记录已被删除，导致无法插入。请问该如何解决？

解决方法 #

1. 加共享锁锁定查询记录 #

在查询时对contacts表加共享锁，防止查询过程中记录被删除。PostgreSQL支持FOR SHARE语法，事务期间被选中的contacts记录仅允许读取，无法被删除或修改：

BEGIN;
INSERT INTO contacts_fields (
  contact_id, field_name, numeric_value
)
select distinct      
  c.id, 
  'storeId', 
  1
from 
  contacts c 
  inner join contacts_devices cd on cd.contact_id = c.id 
where c.id NOT IN (
    SELECT contact_id FROM contacts_fields
)
FOR SHARE; -- 锁定查询到的contacts记录
COMMIT;

注意：高并发场景下，该方法可能影响删除操作的性能，需根据业务场景评估。

2. 插入前二次校验contact_id存在性 #

将存在性校验合并到查询逻辑中，确保待插入的contact_id在contacts表中真实存在：

INSERT INTO contacts_fields (
  contact_id, field_name, numeric_value
)
select distinct      
  c.id, 
  'storeId', 
  1
from 
  contacts c 
  inner join contacts_devices cd on cd.contact_id = c.id 
where 
  c.id NOT IN (SELECT contact_id FROM contacts_fields)
  AND EXISTS (SELECT 1 FROM contacts WHERE id = c.id); -- 二次校验存在性

该方法无需加锁，能大幅降低间隙错误概率，但极端场景下仍可能出现问题。

3. 结合事务隔离级别+重试 #

PostgreSQL默认隔离级别为REPEATABLE READ，事务内读取的数据快照保持一致，但插入时仍会触发外键校验。可结合异常重试机制，在应用代码或存储过程中捕获外键错误后重新执行：

PL/pgSQL存储过程示例

CREATE OR REPLACE FUNCTION insert_contact_storeid() RETURNS void AS $$
DECLARE
  retry_count INT := 3; -- 设置重试次数
BEGIN
  WHILE retry_count > 0 LOOP
    BEGIN
      INSERT INTO contacts_fields (
        contact_id, field_name, numeric_value
      )
      select distinct      
        c.id, 
        'storeId', 
        1
      from 
        contacts c 
        inner join contacts_devices cd on cd.contact_id = c.id 
      where c.id NOT IN (SELECT contact_id FROM contacts_fields);
      RETURN;
    EXCEPTION
      WHEN foreign_key_violation THEN
        retry_count := retry_count - 1;
        IF retry_count = 0 THEN
          RAISE; -- 重试耗尽后抛出原错误
        END IF;
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

4. 延迟外键校验（不推荐） #

将外键约束设置为DEFERRABLE INITIALLY DEFERRED，使校验延迟到事务提交时执行。但这仅能延迟错误触发，无法解决数据不一致问题，非特殊场景不建议使用。

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