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.
At 01:49 PM 5/19/2002 -0700, Josh Berkus wrote:
>> 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
>is that a 16-byte string will take up 4x the sort memory of a 4-byte
>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.
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
In response to
pgsql-novice by date
|Next:||From: Joel Burton||Date: 2002-05-19 22:29:21|
|Subject: Re: Is a randomized default value primary key|
|Previous:||From: Ron Johnson||Date: 2002-05-19 20:57:06|
|Subject: New features in PlPgSQL|