Re: Is a randomized default value primary key possible?

April,

> Instead of a sequentially auto-incrementing primary key, I would like a
> random 16 byte character value (assumedly hex) to be automatically created
> as the default value for each new record.
>
> I guess I could just use a large random number, created with the postgres
> random() function - however I don't see any way of seeding it, or know if
> that is necessary.

First, let me point out that there is no reason for the "user key" you are
trying to implement to be the same value as the primary key of the table.
There are, in fact, a number of good arguments against it, the least of which
is that a 16-byte string will take up 4x the sort memory of a 4-byte integer.
I would suggest that you give the table an actual, hidden primary key based
on a simple sequence, and a seperate unique "user key" for lookups.

This is actually easy to implement through custom functions, triggers, or
rules. However, there are some performance implications if your table gets
very large, as you would have to prescan for accidental duplicates (in a
truly random distribution, this is nearly certain given enough records, even
with a 16-byte value).

> The purpose of using a random rather than sequential number is to prevent
> people being able to access other's records by guessing.
>
> Has anyone else encountered this challenge, and do you know of a way to
> generate a random default value for the primary key?

Genrally, a pseudo-random number is more than adequate. For example, one of
my applications generates a pseudo-random session key based on a calculation
involving the user_id of the modifying user and the epoch timestamp on which
the record was locked. This appears random to the casual eye, and is
near-impossible to guess.

--
-Josh Berkus