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

Re: Is a randomized default value primary key

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
Date: 2002-05-19 22:58:03
Message-ID: 200205191558.03677.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novice
April,

> 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.

Oh, yeah, you're right.  I should have added the caveat that having the two 
keys makes sense if you are doing a lot of JOINing on the primary key of this 
table.  If this table does not have a lot of key-related data in other 
tables, the dual keying does not make sense.  Since you seemed to be talking 
about a central data table, I just assumed that it would have child tables.

And Joel has a good point, which I made somewhat obliquely:  you need to be 
prepared for key violation messages, with your method.  If you expect many 
thousands of records, you should do the key-checking up front:

1. Create a PL/pgSQL trigger function called random_authkey(),
2. Have function generate a random number, scan the table for duplicates, and 
if found generate a replacement number and return it as NEW.authkey;
3. Create a BEFORE trigger which calls this function for each INSERT.

This should automate the process for you, although it will slow down your 
inserts significantly due to the extra index scan required for each insert.  
Depending on your level of activity, though, you will probably not even 
notice.


-- 
-Josh Berkus




In response to

pgsql-novice by date

Next:From: Ron JohnsonDate: 2002-05-19 23:33:48
Subject: Accessing table attributes from within PlPgSQL
Previous:From: ghaverlaDate: 2002-05-19 22:40:36
Subject: Re: Is a randomized default value primary key

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