Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

On 2009-04-30, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
> Hi
>
> This must be a fairly common requirement, but either I don't know how to
> ask Google about it or there's not as much out there as I would've expected.
>
> I'm looking for a way to map the output from a monotonically increasing
> sequence (not necessarily gapless - ie a normal Pg SEQUENCE) into a
> fairly random different value in the availible space with a 1:1
> input->output relationship. In other words, for the input "27" the
> output will always be the same (say 32 bit) number, and no other input
> will produce that output.

so you want

DEFAULT magic_func( nextval('foo_id_seq'::regclass) )

where magic_func is the 1:1 mapping and foo_id_seq is the sequence
that feeds it.

> Note that I'm *NOT* looking for a PRNG that takes the previous output as
> its input. That'd force me to use the same techniques as for a gapless
> sequence in Pg, with all the associated horror with locking and
> deadlocks, the performance issues, etc.

any good PRNG will have the 1:1 mapping you want, but fed sequential
values they tend to produce predictable output.

I suggest for magic_func you use a collection of bit-shifts, adds, and
XORs then mask out the bits abouve 31 and use what's left.

test and adjust if needed,

> If I find something good and there aren't any existing Pl/PgSQL
> implementations I'll post one for others' use, since I'm pretty sure it
> must come up a lot. You don't want your database to send out "invoice
> #1" or "customer #1" after all.

to this end was pg_catalog.setvalue( sequence, value,TRUE)
invented.

> (I'm also going to be looking for efficient ways to calculate effective
> check digits for arbitrary numbers within a certain range, too, and will
> post something for that, but that comes later).