原作者：彭冲

• 适用范围
• 问题概述
• 问题复现
• 问题原因
• 解决方案

适用范围

截止MogDB 3.0所有版本

问题概述

存储过程中使用EXCEPTION子句捕获异常，违反外键约束的错误无法被捕获到。

问题复现

1.创建主表tab

create table tab(id int primary key,info varchar);
insert into tab values(100,'test');

2.创建外键引用表tab_fk

create table tab_fk(id int primary key,num int);

alter table tab_fk add constraint fk1 foreign key(id) references tab(id) on delete cascade deferrable initially deferred;

3.创建测试存储过程

create or replace procedure public.prc_test_get_error()
as 
begin
   --insert into  tab(id,info) values(100,'abcdef') ;
	insert into tab_fk values(200,100);
	EXCEPTION
   WHEN others then
     raise notice E'Got exception:
       SQLSTATE: %
       SQLERRM: %', SQLSTATE, SQLERRM; 
end;
/

4.测试调用

call prc_test_get_error();

5.调用结果

15:45:13 (omm@local:3020)postgres=# call prc_test_get_error();
ERROR:  insert or update on table "tab_fk" violates foreign key constraint "fk1"
DETAIL:  Key (id)=(200) is not present in table "tab".

6.期望结果
上面存储过程插入语句如果换成

insert into  tab(id,info) values(100,'abcdef') ;

违反主键约束，可以正常捕获错误如下：

15:44:51 (omm@local:3020)postgres=# call prc_test_get_error();
NOTICE:  Got exception:

        SQLSTATE: 23505

        SQLERRM: duplicate key value violates unique constraint "tab_pkey"
 prc_test_get_error 
--------------------
 
(1 row)

问题原因

插入主表是在存储过程执行SQL的时候报错的，所以可以捕获异常。

但是插入外键引用表tab_fk，plpgsql执行的时候是没有问题的。执行后通过触发器来实现的外键约束。

所以plpgsql不能捕获改异常，因为报错的时候，整个存储过程已经执行结束了。

解决方案

暂无