Start from another min value when max value is reached on Postgres Sequence-CodePudding

I have this Sequence defined in sqlalchemy for postgres

import sqlalchemy as sa
user_id_sequence = sa.Sequence(name='user_id_seq',
                               start=100000,
                               increment=10,
                               maxvalue=100000000,
                               cache=100)

For some business logic I can't have a sequence of numbers with an increment of 1. What I want to do is after we reach maxvalue the sequence starts over again but now with start parameters as 100001. In this way the sequence will not collide with other already generated numbers and also I can re use the sequence without worrying about running out of numbers.

Is this possible?

CodePudding user response：

On Postgres Documentation there is a really nice function for generating random numbers without worrying about collisions. In this way I can use a normal sequence generator and always use the function for masquerade the original number.

https://wiki.postgresql.org/wiki/Pseudo_encrypt

CodePudding user response：

This is the final solution

CREATE OR REPLACE FUNCTION public.pseudo_encrypt(value bigint) returns bigint AS $$
DECLARE
l1 int;
l2 int;
r1 int;
r2 int;
i int:=0;
BEGIN
 l1:= (value >> 16) & 65535;
 r1:= value & 65535;
 WHILE i < 3 LOOP
   l2 := r1;
   r2 := l1 # ((((1366 * r1   150889) % 714025) / 714025.0) * 32767)::bigint;
   l1 := l2;
   r1 := r2;
   i := i   1;
 END LOOP;
 return ((r1 << 16)   l1);
END;
$$ LANGUAGE plpgsql strict immutable;

create sequence if not exists user_id_seq
as bigint
increment by 1
minvalue 1
maxvalue 1000000000
cache 100;

how to tested in SQL

-- generate 10 mill random id
select x, pseudo_encrypt(x) as encrypt
into temp table pseudo_encrypt_results
from generate_series(1,10000000) as x;

-- we should get 10 mill
select count(1) from pseudo_encrypt_results;

-- validate there are not repeated elements in temporary table
select encrypt as encrypt_1, encrypt as encrypt_2, count(*)
from pseudo_encrypt_results
group by encrypt_1, encrypt_2
HAVING count(*) > 1;

How to use in Python with SQLALchemy

from sqlalchemy import func, Sequence
user_id = session.query(func.public.pseudo_encrypt(
                Sequence('user_id_seq').next_value())).first()[0]