Re: UUID column as pimrary key?

dennis.jenkins.75@gmail.com (dennis jenkins) writes:
> The UUID itself is 128 bits.  Some of those bits are pre-determined.
> I don't recall, but I think that a "normal" UUID has 121 bits of
> randomness.

That doesn't match RFC 4122 very well...

It indicates 5 forms of UUIDs:

1) Time-based, where about 1/2 the data comes from local timestamp data,
   and 48 bits come from MAC address (or similar)

2) "DCE Security" (about which it says little)

3) Name-based, using MD5 hashing

4) Randomly generated UUIDs (which are quite likely what you're thinking
   about) have 122 bits of random data

5) Name-based, using SHA-1 hashing

The reasonable choices for a would-be artificial primary key seem to be
1 and 3; in a distributed system, I'd expect to prefer 1, as the time +
host data are likely to eliminate the "oh, it might just randomly match"
problem.

Note the set of functions in the uuid-ossp contrib module fit this, down
to omitting Version 2 :-).

test@localhost->  \df+ public.uuid_generate*
                                                                     List of functions
 Schema |        Name        | Result data type |    Argument data types    |  Type  | Volatility |  Owner   | Language
|   Source code     | Description 

--------+--------------------+------------------+---------------------------+--------+------------+----------+----------+--------------------+-------------
 public | uuid_generate_v1   | uuid             |                           | normal | volatile   | postgres | c
|uuid_generate_v1   | 
 public | uuid_generate_v1mc | uuid             |                           | normal | volatile   | postgres | c
|uuid_generate_v1mc | 
 public | uuid_generate_v3   | uuid             | namespace uuid, name text | normal | immutable  | postgres | c
|uuid_generate_v3   | 
 public | uuid_generate_v4   | uuid             |                           | normal | volatile   | postgres | c
|uuid_generate_v4   | 
 public | uuid_generate_v5   | uuid             | namespace uuid, name text | normal | immutable  | postgres | c
|uuid_generate_v5   | 
(5 rows)
--
"I'm all for advancement.  However  rich text on an ephemeral media is
a totally brain-dead idea.  Nobody  in their right mind would take the
effort to prettyfy documents that are going to be gone in a few days."
-- Jay Denebeim <denebeim@deepthot.ml.org>