How can wrap-on-overflow math be done in Postgres?

Question

Given the following example function:

CREATE OR REPLACE FUNCTION add_max_value(_x BIGINT)
    RETURNS BIGINT
    LANGUAGE sql
    AS $$
        SELECT 9223372036854775807 + _x;
    $$;

If this function is called with any positive value, the following error is returned:

SELECT add_max_value(1); -- Expecting -9223372036854775808 if math wrapped

-- SQL Error [22003]: ERROR: bigint out of range

How can I do wrap-on-overflow integer math in Postgres?
- Please note:
  1. I want to do this in the database, not in the application
  2. I don't want it to promote to an arbitrary precision integer (NUMERIC)
  3. Although the example only does addition, in practice I'm interested in other operations as well

Does the function really raise an error even for negative input? — jarlh, Sep 17 '20 at 12:37
@a_horse_with_no_name There is an expected result in the second code block. To reiterate, the same wrapping behavior that many mainstream languages offer (C, Java). — dminuoso, Sep 17 '20 at 12:45
@jarlh You are correct, good call; positive integers are the issue in the example. Question updated. — dminuoso, Sep 17 '20 at 12:46
I don't see any other way than to use an intermediate `numeric` variable in PL/pgSQL and to the "wrapping" manually — , Sep 17 '20 at 12:49

Belayer · Answer 1 · 2020-09-17T18:47:35.583

1

As a SQL function there isn't a way. SQL functions cannot process exceptions. But a plpgsql function can:

CREATE OR REPLACE FUNCTION add_max_value(_x BIGINT)
    RETURNS BIGINT
    LANGUAGE plpgsql
    AS $$
    declare 
        bigx bigint; 
    begin 
        bigx = 9223372036854775807 + _x; 
        return bigx;
    exception
        when sqlstate '22003' then
             return (9223372036854775807::numeric + _x - 2^64)::bigint;
    end;
    $$;

edited Sep 17 '20 at 18:47

answered Sep 17 '20 at 13:31

Belayer

13,578
2
11
22

You would know much better than I. But yes, you could also just "return 1". – Belayer Sep 17 '20 at 14:07
No problem. The specifics do not matter, it is the concept of handling the exception, and particularly specific exception at that. – Belayer Sep 17 '20 at 15:31
Would it be more efficient to first assign the result of the addition to a `numeric` variable, then check if it's bigger than `9223372036854775807`? Exception handling is quite expensive – Sep 17 '20 at 16:42
Yes, for the contrived example presented where the exception is **intentionally** caused, but I think not for the general case of arithmetic overflow. I do not think OP issue is with the specific example presented but to the general case. If the issue it is for specific example then more efficient yet just go straight to the reset value with a SQL function of a the statement "select (9223372036854775807::numeric + _x - 2^64)::bigint;" – Belayer Sep 17 '20 at 19:08

score 0 · Answer 2 · answered Sep 17 '20 at 13:56

This is easily in my top five of the most wasteful SQL I have ever written:

create or replace function add_max_value(_x bigint)
  returns bigint
  language sql
as $$
with recursive inputs as (
  select s.rn, r.a::int, s.b::int, (r.a::int + s.b::int) % 2 as sumbit, 
        (r.a::bit & s.b::bit)::int as carry  
    from regexp_split_to_table((9223372036854775807::bit(64))::text, '') with ordinality as r(a, rn)
         join regexp_split_to_table((_x::bit(64))::text, '') with ordinality as s(b, rn)
           on s.rn = r.rn
), addition as (
  select rn, sumbit, sumbit as s2, carry, carry as upcarry
    from inputs
   where rn = 64
  union 
  select i.rn, i.sumbit, (i.sumbit + a.upcarry) % 2, i.carry, 
         (i.carry::bit | a.upcarry::bit)::int
    from addition a
    join inputs i on i.rn = a.rn - 1
)
select (string_agg(s2::text, '' order by rn)::bit(64))::bigint
  from addition
$$;

How can wrap-on-overflow math be done in Postgres?

2 Answers2