PostgreSQL自定义函数溢出异常未被捕获，如何定位问题输入？

一、异常原因 #

这个错误是因为计算结果超出了real类型的取值范围。PostgreSQL的real是单精度浮点数，取值范围约为±1.18e-38到±3.40e38。当new与old的差值极大，或者old数值非常接近0时，计算100*(new::real - old::real)/old::real会得到超出real范围的数值，触发溢出错误。

而且该溢出发生在函数返回值的类型转换阶段：函数计算出的临时结果已经超出real范围，在将其转为函数声明的返回类型real时触发错误，这个阶段不在函数体begin...end块的异常捕获范围内。

二、为什么exception when others没捕获异常 #

PL/pgSQL的异常捕获仅能处理**begin到end代码块执行过程中**抛出的异常。本次溢出是在函数计算完成、准备返回结果时，对结果进行real类型转换时发生的，属于函数执行后的类型校验环节，不在begin...end的代码执行范围内，因此exception无法捕获该错误。

三、定位问题输入的方法 #

可以通过以下步骤找出触发溢出的行：

1. 创建一个返回numeric类型的调试函数（numeric支持更大取值范围，不会轻易溢出）：

CREATE OR REPLACE FUNCTION public.safe_percent_delta_debug(new text, old text)
 RETURNS numeric
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin 
    if (new :: numeric = 0 and old :: numeric <> 0) or (old :: numeric = 0 and new :: numeric <> 0) then 
        return null;
    else
        return 100 * (new :: numeric - old :: numeric) / old :: numeric;
    end if;
exception
    when others then 
        return null;
end;
$function$

2. 用调试函数查询临时表，筛选出结果超出real范围的行：

SELECT v_new, v_old, safe_percent_delta_debug(v_new, v_old)
FROM temp_table
WHERE safe_percent_delta_debug(v_new, v_old) > 3.40e38 
   OR safe_percent_delta_debug(v_new, v_old) < -3.40e38;

也可以直接排查极端值行：

SELECT v_new, v_old
FROM temp_table
WHERE abs(old::numeric) < 1e-30  -- old接近0
   OR abs(new::numeric - old::numeric) > 1e37;  -- 差值极大

四、修复方案 #

方案1：改用numeric作为返回类型（推荐） #

numeric支持更大的取值范围，从根源避免溢出问题，同时优化类型转换的异常处理：

CREATE OR REPLACE FUNCTION public.safe_percent_delta(new text, old text)
 RETURNS numeric
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
begin 
    -- 先校验是否为合法数字格式，避免转换异常
    if not (new ~ '^-?\d+(\.\d+)?$' and old ~ '^-?\d+(\.\d+)?$') then
        return null;
    end if;
    
    declare
        new_num numeric := new::numeric;
        old_num numeric := old::numeric;
    begin
        if (new_num = 0 and old_num <> 0) or (old_num = 0 and new_num <> 0) then 
            return null;
        else
            return 100 * (new_num - old_num) / old_num;
        end if;
    exception
        when division_by_zero then
            return null;
    end;
exception
    when others then 
        return null;
end;
$function$

方案2：保留real类型，添加范围检查 #

如果必须使用real，可以在计算后检查结果是否在real范围内，超出则返回null：

CREATE OR REPLACE FUNCTION public.safe_percent_delta(new text, old text)
 RETURNS real
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare
    new_num real;
    old_num real;
    result numeric;
begin 
    new_num := new::real;
    old_num := old::real;
    
    if (new_num = 0 and old_num <> 0) or (old_num = 0 and new_num <> 0) then 
        return null;
    else
        result := 100 * (new_num - old_num) / old_num;
        -- 检查结果是否在real的合法范围内
        if result > 3.40e38 or result < -3.40e38 then
            return null;
        else
            return result::real;
        end if;
    end if;
exception
    when others then 
        return null;
end;
$function$

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