Re: Is a randomized default value primary key - Mailing list pgsql-novice

I made the primary key

"authkey"    bigint DEFAULT trunc(random()*10^15) NOT NULL

Does that seem reasonable? bigint is 8 bytes. I came up with this prior to
receiving your reply.

Since I do have to use the authkey to find records, it seems I would still
benefit by having an index for it even if I had a separate 4 byte primary
key - so I don't understand how it would save resources or increase
performance to avoid making this column the primary key? Admittedly, I
don't understand indexes in depth yet, I just assumed that every additional
index means additional housekeeping activities each time a record is
changed or added.

Thank you,

- April

At 01:49 PM 5/19/2002 -0700, Josh Berkus wrote:
>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>