Why is this PostgreSQL function throwing an exception, and how can I tell which input is?

I have a table which stores attribute value data, the majority of which are numbers but occasionally strings. When the values are numbers, we are interested in a delta. The following PostgreSQL function attempts to capture that

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

In the table, we store that as a generated column. However, on a recent insert of new data, I'm getting an exception.

SQL Error [22003]: ERROR: value out of range: overflow
  Where: PL/pgSQL function safe_percent_delta(text,text) while casting return value to function's return type

I'm trying to insert ~4 million rows, we currently have over 4 billion stored, and this is the first time I've seen this happen. The way we typically insert the data is with a copy into a temporary table with just the data we're inserting because it's much faster, then we do the actual insert which relies on some subquerying and also implicitly calls this function for the generated column. When debugging this, I made the temporary table persistent and tried just calling the function after the fact with select safe_percent_delta(v_new, v_old) from temp_table and got the same result.

What's going on here? Is there a way to find out which input is causing the issue? Why is my exception when others not catching this?

Solution

Your expected output is a real. Casting the outcome of your calculation to this real is done after the code block has finished, which is why your error handler doesn't catch the error.

You might be better off using a double precision that can handle a broader range of values. You could/should also validate the content before casting the text to a numeric value. Something like this:

CREATE OR REPLACE FUNCTION public.safe_percent_delta(new_text text, old_text text)
RETURNS double precision
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    new_val double precision;
    old_val double precision;
BEGIN
    -- Validate content first:
    IF NOT pg_input_is_valid(new_text, 'double precision') 
       OR NOT pg_input_is_valid(old_text, 'double precision') THEN
        RETURN NULL;
    END IF;

    new_val := CAST(new_text AS double precision);
    old_val := CAST(old_text AS double precision);

    -- Return NULL if exactly one is zero
    IF (new_val = 0) <> (old_val = 0) THEN
        RETURN NULL;
    END IF;

    RETURN 100 * (new_val - old_val) / NULLIF(old_val, 0);
END;
$$;

Edit: If you want to keep the output as real, you can validate the result from the calculation and return NULL when it's not valid:

CREATE OR REPLACE FUNCTION public.safe_percent_delta(new_text text, old_text text)
RETURNS real
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    new_val double precision;
    old_val double precision;
    output double precision;
BEGIN
    -- Validate content first:
    IF NOT pg_input_is_valid(new_text, 'double precision')
       OR NOT pg_input_is_valid(old_text, 'double precision') THEN
        RETURN NULL;
    END IF;

    new_val := CAST(new_text AS double precision);
    old_val := CAST(old_text AS double precision);

    -- Return NULL if exactly one is zero
    IF (new_val = 0) <> (old_val = 0) THEN
        RETURN NULL;
    END IF;

    output := 100 * (new_val - old_val) / NULLIF(old_val, 0);

    IF pg_input_is_valid(CAST(output AS text), 'real') THEN
        RETURN output;
    ELSE
        RETURN NULL;
    END IF;
END;
$$;

This avoids the need for a costly exception handler.