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

Re: Is a randomized default value primary key possible?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: April L <april(at)i-netco(dot)com>,PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Is a randomized default value primary key possible?
Date: 2002-05-19 20:49:31
Message-ID: 200205191349.31353.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Ron JohnsonDate: 2002-05-19 20:57:06
Subject: New features in PlPgSQL
Previous:From: Ron JohnsonDate: 2002-05-19 20:10:30
Subject: Re: Is a randomized default value primary key possible?

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