Skip site navigation (1) Skip section navigation (2)

Re: Is a randomized default value primary key

From: April L <april(at)i-netco(dot)com>
To: PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Is a randomized default value primary key
Date: 2002-05-19 22:19:36
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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:
>> 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.
>-Josh Berkus
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?

In response to


pgsql-novice by date

Next:From: Joel BurtonDate: 2002-05-19 22:29:21
Subject: Re: Is a randomized default value primary key
Previous:From: Ron JohnsonDate: 2002-05-19 20:57:06
Subject: New features in PlPgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group