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

Re: Create on insert a unique random number

From: Steve Midgley <public(at)misuse(dot)org>
To: lance(at)uiuc(dot)edu,pgsql-sql(at)postgresql(dot)org
Subject: Re: Create on insert a unique random number
Date: 2008-03-18 19:23:35
Message-ID: 20080318192351.4AD972E0041@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
At 11:58 AM 3/18/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Tue, 18 Mar 2008 13:40:42 -0500
>From: "Campbell, Lance" <lance(at)uiuc(dot)edu>
>To: "Vivek Khera" <vivek(at)khera(dot)org>,
>         <pgsql-sql(at)postgresql(dot)org>
>Subject: Re: Create on insert a unique random number
>Message-ID: 
><B10E6810AC2A2F4EA7550D072CDE8760CDDC34(at)SAB-FENWICK(dot)sab(dot)uiuc(dot)edu>
>
>Thanks for all of your input.  It appears that the best way to do this
>is to create a default random number in the primary id field in the
>table definition and then return that value after insert.  If an
>exception occurs because of duplicates I will simple perform the same
>insert statement again. I doubt there would be many duplicate hits if 
>I
>use a really large number.
>[snip]
>I built a web application called the Form Builder.  It allows
>individuals to create web forms.  After a user is done building their
>web form the tool provides a URL for the user to access the form.
>Obviously the URL has the random ID of the form in it.  Most of the
>forms created with this tool can be accessed and filled out by the
>general public.
[snip]

Hi Lance,

I think I "get you" as a fellow web systems (aka middleware) guy. My 
opinion is that the use of a "sparse index" is totally reasonable for 
the purpose you describe. But I would argue that you could take it a 
little further in implementation that might keep your db design sane 
while still giving you the sparse index function on the front-end.

1) Create a second field (as someone recommend on this list) that is an 
MD5 of your primary key. Use that as your "accessor" index from the web 
application. But keep the primary key as an integer serial, so that it 
works as expected, and you can build relations normally. I think in the 
end you'll be happier with this method than messing around with a 
custom primary key system.. You can build a trigger that generates the 
MD5 hash every time a record is created (or you can do it in your ORM 
layer in the web app).

2) Also, (but OT) put a monitor on your weblogs to look for "404" 
errors ("page not found" for the sql-only people here). This will 
supplement your sparse index by detecting people who are scanning your 
sparse index space and generating lots of "misses."

Hope that helps,

Steve


Responses

pgsql-sql by date

Next:From: D'Arcy J.M. CainDate: 2008-03-18 19:24:14
Subject: Re: Create on insert a unique random number
Previous:From: Vivek KheraDate: 2008-03-18 19:12:41
Subject: Re: Create on insert a unique random number

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