Re: Question about integer out of range in function

On 5/14/21 1:38 AM, Condor wrote:
>
> Hello ppl,
>
> I have a question about ERROR:  integer out of range in one function.
> I modify the generate_ulid() function to accept also UNIX timestamp as
> input parameter.
>
> I drop old function and make new one:
>
> CREATE FUNCTION generate_ulid(fromtime bigint default 0)
>
> Then I declare two new variables:
>
>    rand_int   INTEGER;
>    new_time   BIGINT;
>
> and then begin:
>
>  BEGIN
>    -- 6 timestamp bytes
>    IF fromtime = 0 THEN
>      unix_time = (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;
>    ELSE
>      rand_int = (random() * 1000)::INT;
>      new_time = fromtime * 1000; -- here is line 19
>      unix_time = (new_time + rand_int)::BIGINT;
>    END IF;
>
> (Yes, I know it's can be one line, but this is for debug)
>
> When I start the function I receive error:
>
> test_db=# select generate_ulid(extract(epoch from now())::int);
> ERROR:  integer out of range
> CONTEXT:  PL/pgSQL function generate_ulid(integer) line 19 at assignment
>
> If I modify line 19 to : new_time = (fromtime * 1000)::BIGINT;
>
> Everything is work. Well, until I write the email I figured out, but I
> don't know is this normal behavior or just a problem.
>
> I think, this is happened because I send INT to function
> generate_ulid(extract(epoch from now())::int) but in function I expect
> this to be BIGINT and my variable is cast automatic to INT.
>
> My question is this normal and should input param not be treated as bigint
> automatic as defined ? Did I can change whit this way input type to other
> functions for example get_random_bytes(34423423423423423424234::BIGINT) ?

What is fromtime?  Since MAX_INT is 2147483648, and you're multiplying
fromtime by 1000, the largest that fromtime can be is 2147483 without some
INTEGER variable (possibly internal) overflowing.

> Version: PostgreSQL 13.2 on x86_64-slackware-linux-gnu, compiled by
> x86_64-slackware-linux-gcc (GCC) 10.3.0, 64-bit

--
Angular momentum makes the world go 'round.