Re: Create on insert a unique random number

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