由于GREENPLUM是分布式数据库集群,因此,如果存储过程异常处理不当会导致真正的SQL错误信息无法显示,对开发人员产生误导,比如下面的例子
CREATE TABLE public.generate_error (
id INTEGER NOT NULL PRIMARY KEY,
data TEXT NOT NULL
);
INSERT INTO public.generate_error (id, data) VALUES (1, 'test 1');
INSERT INTO public.generate_error (id, data) VALUES (2, 'test 2');
CREATE OR REPLACE FUNCTION public.f_generate_error()
RETURNS integer AS
$BODY$
DECLARE
BEGIN
INSERT INTO public.generate_error (id, data) VALUES (1, 'test 1');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
DECLARE
x INT;
BEGIN
RAISE NOTICE 'Raised an error ...';
RETURN -1;
END;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
去执行函数
SELECT public.f_generate_error();
得到如下错误
WARNING: The distributed transaction 'Abort [Prepared]' broadcast failed to one or more segments for gid = 1310028785-0000000049.
NOTICE: Releasing gangs for retry broadcast.
NOTICE: Retry of the distributed transaction 'Abort Prepared' broadcast succeeded to the segments for gid = 1310028785-0000000049.
ERROR: The distributed transaction 'Prepare' broadcast failed to one or more segments for gid = 1310028785-0000000049. (cdbtm.c:618)
本来很简单的主键重复的错误,变成了broadcast failed,看上去很严重的错误。使得开发人员无法对真正的问题进行纠正。
这要改变一下异常处理的定义
CREATE OR REPLACE FUNCTION public.f_generate_error()
RETURNS integer AS
$BODY$
BEGIN
INSERT INTO public.generate_error (id, data) VALUES (1, 'test 1');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Raised an error ...%', sqlerrm;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
再次执行
SELECT public.f_generate_error();
就可以得到真正导致错误原因的信息。
ERROR: Raised an error ...duplicate key violates unique constraint "generate_error_pkey" (seg0 roberj12-mbp:50000 pid=21389)
由于抛异常会中断执行,如果希望这个函数异常处理部分继续像原始定义那样返回-1,这样改写
CREATE OR REPLACE FUNCTION public.f_generate_error()
RETURNS integer AS
$BODY$
BEGIN
BEGIN
INSERT INTO public.generate_error (id, data) VALUES (1, 'test 1');
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING '%', sqlerrm;
RETURN -1;
END;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Raised an error ...%', sqlerrm;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
但是如果直接执行
poc=# SELECT public.f_generate_error();
WARNING: Warning:duplicate key violates unique constraint "generate_error_pkey" (seg0 roberj12-mbp:50000 pid=22127)
WARNING: The distributed transaction 'Abort [Prepared]' broadcast failed to one or more segments for gid = 1310068719-0000000197.
NOTICE: Releasing gangs for retry broadcast.
NOTICE: Retry of the distributed transaction 'Abort Prepared' broadcast succeeded to the segments for gid = 1310068719-0000000197.
ERROR: The distributed transaction 'Prepare' broadcast failed to one or more segments for gid = 1310068719-0000000197. (cdbtm.c:605)
错误依旧,这是因为函数自动提交。你可以在调用代码中引入事务控制异常处理。
poc=# begin work;
BEGIN
poc=# SELECT public.f_generate_error();
WARNING: Warning:duplicate key violates unique constraint "generate_error_pkey" (seg0 roberj12-mbp:50000 pid=22132)
f_generate_error
------------------
-1
(1 row)
poc=# commit;
WARNING: The distributed transaction 'Abort [Prepared]' broadcast failed to one or more segments for gid = 1310068719-0000000198.
NOTICE: Releasing gangs for retry broadcast.
NOTICE: Retry of the distributed transaction 'Abort Prepared' broadcast succeeded to the segments for gid = 1310068719-0000000198.
ERROR: The distributed transaction 'Prepare' broadcast failed to one or more segments for gid = 1310068719-0000000198. (cdbtm.c:605)
也可以选择回滚,这时不再会产生错误信息
poc=# begin work;
BEGIN
poc=# SELECT public.f_generate_error();
WARNING: Warning:duplicate key violates unique constraint "generate_error_pkey" (seg0 roberj12-mbp:50000 pid=22140)
f_generate_error
------------------
-1
(1 row)
poc=# rollback;
ROLLBACK
poc=#
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25548387/viewspace-703160/,如需转载,请注明出处,否则将追究法律责任。