由于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/，如需转载，请注明出处，否则将追究法律责任。