Editing pseudo_encrypt PostgreSQL function with Recurrsion to Avoid Certain IDs

Question

Because I can't test this easily with billions of table insertions, I want to get help on figuring out how to use the pseudo_encrypt (https://wiki.postgresql.org/wiki/Pseudo_encrypt) function for my table ids that already have sequential ids in them. For example, our users table has approx 10,000 users. Ids go from 1..10,000.

Now I want to use the pseudo_encrypt function to get the 10,001 ID which would look something like this: 1064621387932509969

The problem is that there is a chance that the "random" pseudo encrypt return value may collide at one point with my early 1-10,000 user IDs.

I do not want to change the first 10,000 user IDs as that would cause some pain for the current users (have to re-login again, urls broken, etc.).

My idea was to use some sort of recursive function to handle this... would something like this work, or am I mission something?

CREATE OR REPLACE FUNCTION "pseudo_encrypt"("VALUE" int) RETURNS int     IMMUTABLE STRICT AS $function_pseudo_encrypt$
DECLARE
l1 int;
l2 int;
r1 int;
r2 int;

return_value int;

i int:=0;
BEGIN
    l1:= ("VALUE" >> 16) & 65535;
    r1:= "VALUE" & 65535;
    WHILE i < 3 LOOP
        l2 := r1;
        r2 := l1 # ((((1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767)::int;
        r1 := l2;
        l1 := r2;
        i := i + 1;
END LOOP;

return_value = ((l1::int << 16) + r1); // NEW CODE

// NEW CODE - RECURSIVELY LOOP UNTIL VALUE OVER 10,000
WHILE return_value <= 10000
    return_value = pseudo_encrypt(nextval('SEQUENCE_NAME'))
END LOOP

RETURN return_value;
END;

$function_pseudo_encrypt$ LANGUAGE plpgsql;

Answer 1

Great comments! This seems to do the job:

CREATE OR REPLACE FUNCTION get_next_user_id() returns int AS $$
DECLARE
return_value int:=0;
BEGIN
    WHILE return_value  < 10000 LOOP
        return_value  := pseudo_encrypt(nextval('test_id_seq')::int);
    END LOOP;
    RETURN return_value ;
END;
$$ LANGUAGE plpgsql strict immutable;