From: D'Arcy J.M. Cain <darcy@druid.net>
To: Steve Midgley <public@misuse.org>
Cc: lance@uiuc.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: Create on insert a unique random number
Date: Wed, 19 Mar 2008 09:47:53 -0400
Message-ID: <20080319094753.0372e9de.darcy@druid.net> (raw)
In-Reply-To: <20080318205745.12EB82E002C@postgresql.org>
References: <20080318190314.94E2A24A9A4@maia-3.hub.org>
<20080318192351.4AD972E0041@postgresql.org>
<20080318153613.364cf08d.darcy@druid.net>
<20080318205745.12EB82E002C@postgresql.org>
On Tue, 18 Mar 2008 13:57:39 -0700
Steve Midgley <public@misuse.org> wrote:
> At 12:36 PM 3/18/2008, D'Arcy J.M. Cain wrote:
> >On Tue, 18 Mar 2008 12:23:35 -0700
> >Steve Midgley <public@misuse.org> wrote:
> > > 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
> >
> >I strongly disagree for three reasons. First, if you are going to
> >generate a key then don't store it. Just generate it every time.
> >Second, don't generate it based on a known field. You may think that
> >it is secure but what if you private key is compromised? Do you then
> >change everyone's security code? Third, what if one person's
> >code is compromised? If it is based on a calculation then you
> >can't change that one person's security code.
> I'm not clear on your concern here - an MD5 hash doesn't have a private
> key that can be compromised, afaik. It's a one way hash. I don't see
Right so it is even less useful than I implied. It can never be
changed so why store it when it can be re-generated at any time.
> much difference between making an MD5 of the primary key and generating
> a random number for the "public primary key", except that you shouldn't
> get index collisions with the MD5 method (whereas eventually you will
> with a random number, though of course using a GUID would eliminate
> that concern for practical purposes).
But your suggestion was to base this key on the serial primary key so
where is your index collision protection? You are going to get
collisions on both the serial key and, to a lesser extent, your
generated one. Besides, has anyone ever demonstrated a real issue with
lookups using serial primary keys? I think you are trying to second
guess the database engine with this and I don't think that that is a
great idea.
> The issue is about creating an index into a sparse hash so that each
> record is somewhat randomly located in a sparse hash "index space".
> (One valid reason to do this would be if you wanted to hide the total
> number of records in your table from competitors or customers). (Just
If that is your goal then start your serial at something other than 1.
Start at 1,000,000 for example and your first user will think that
you already have one million clients. Actually, he will think that
you started elsewhere than 1 but he won't know where.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, public@misuse.org, lance@uiuc.edu
Subject: Re: Create on insert a unique random number
In-Reply-To: <20080319094753.0372e9de.darcy@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox