PostgreSQL中SET CONSTRAINTS DEFERRED对FK约束无效问题求助

问题现象 #

• 执行自定义存储过程向lab表插入数据时，触发外键约束错误：

  SQL Error [23503]: ERROR: insert or update on table "lab" violates foreign key constraint "fk_lab_act"
  Key (Act_number)=(00001-ТР-230822-В-1) is not present in table "acts".

• 尝试SET CONSTRAINTS all DEFERRED无效果，单独执行SET CONSTRAINTS "fk_lab_act" DEFERRED提示约束不存在
• 尝试通过SET session_replication_role = replica或禁用触发器绕过约束，均因权限不足失败

相关代码 #

存储过程代码 #

create or replace procedure "DB_ESG".insert_lab()
language plpgsql
as $$
begin
SET CONSTRAINTS all DEFERRED;
insert into "DB_ESG".lab select * from public.data_lab on conflict do nothing;
commit;end;$$;

lab表创建语句 #

CREATE TABLE lab(
   ID int not null,
    Act_number text,
    constraint "fk_lab_act"
    foreign key("Act_number") references "DB_ESG".ACTS("ACT_NUM") ON DELETE cascade deferrable initially deferred);

解决办法 #

1. 确认约束名称的正确性 #

PostgreSQL对带双引号的标识符大小写敏感，你创建约束时用了"fk_lab_act"，必须严格匹配大小写查询：

SELECT conname 
FROM pg_constraint 
WHERE conrelid = '"DB_ESG".lab'::regclass 
  AND conname = 'fk_lab_act';

如果查询不到，就查该表所有外键约束确认实际名称：

SELECT conname, conrelid::regclass, confrelid::regclass 
FROM pg_constraint 
WHERE contype = 'f' 
  AND conrelid = '"DB_ESG".lab'::regclass;

找到正确名称后，再执行SET CONSTRAINTS "正确约束名" DEFERRED。

2. 调整存储过程的事务逻辑 #

SET CONSTRAINTS仅在事务内部生效，且你已经将外键设置为deferrable initially deferred，无需额外手动设置。另外，存储过程中显式commit会直接提交事务，延迟约束的检查会在此时触发。修改后的存储过程：

create or replace procedure "DB_ESG".insert_lab()
language plpgsql
as $$
begin
insert into "DB_ESG".lab 
select * from public.data_lab on conflict do nothing;
-- 若调用过程时已开启事务，无需显式commit；若需独立提交，保留commit即可
-- commit;
end;$$;

注意：延迟约束只是把检查时机从语句执行推迟到事务提交，不能绕过约束本身——只要数据不符合约束，提交时还是会报错。

3. 从数据源头解决问题 #

错误的核心是public.data_lab中存在Act_number不在DB_ESG.acts表的记录，有两种处理方式：

• 先把缺失的Act_number对应的记录插入到acts表，再执行lab表的插入
• 过滤掉不符合约束的数据后再插入：

  insert into "DB_ESG".lab 
  select dl.* 
  from public.data_lab dl
  join "DB_ESG".acts a on dl.Act_number = a."ACT_NUM"
  on conflict do nothing;
  

4. 权限问题的处理 #

如果确实需要临时绕过外键约束（仅限数据迁移等特殊场景），必须由超级用户执行：

-- 超级用户执行
SET session_replication_role = replica;
-- 执行插入操作
SET session_replication_role = default;

不要尝试禁用系统触发器，这会破坏数据一致性，且普通用户没有权限操作。

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