Re: uuid type for postgres - Mailing list pgsql-hackers

On Thu, Sep 08, 2005 at 12:02:54PM -0400, Jonah H. Harris wrote:
> I think what Greg suggested was sha1(number) as the key instead of requiring
> uuid as the key... it would perform the same function as far as you r use
> case is concerned.

I'm sure he meant something like this. But I am still failing to see
how the below is any better than UUID. UUID is 16 bytes instead of
4+32=36. UUID doesn't require synchronization of the SEQUENCE
configuration (INCREMENT 100 START <SITE-NUMBER> as suggested by
another person) when working with tables at multiple sites that will
be merged together.

So this would leave having the primary key for the row being SERIAL,
and naming the picture as the MD5/SHA1 using data only from constant
strings, and the row data, the only part of the row data remaining
constant being the primary key. I agreed this would work, and enhanced
this by copying a trick from the SASL people where the key would be
concatenated with a constant secret string to further prevent people
from guessing how to crack the numbering scheme, but it means I have
to jump through hoops, and it doesn't solve the multisite / joining
tables after the fact, issue. I can't take my picture databases from
two separate sites, and merge them together nightly without jumping
through hoops. I might have a site in New York, and a site in Toronto.
I don't want to obtain sequence numbers remotely between sites,
forcing a real-time dependency between the sites.

I'm sure we could debate about alternatives for a long, long time. :-)
In the case I describe above, I could have a "ORIGINAL SITE" field,
and include that in a multi-column primary key.

I stubbornly wish to use a type which doesn't require alternatives, or
messy complications to the many SQL queries I will be performing.  I
want it to work out of the box, without any magic. UUID does exactly
what I want, and perhaps more than I need. Most importantly, it does
exactly what I want. I don't care at all that it does more than I need,
as something less isn't available or standard at the moment. UUID is
standard.

You guys are amusing me - not in an insulting way, but by making me
think outside my own conclusions. In the end, though, although there
have been arguments that have made me doubt my decision (1.5X the
number of index pages required over a SERIAL primary key, 2X the
number of index pages required for a multi-column primary key used in
a join table), for the most part, I've become more confident with my
decision. There's nothing wrong with what I'm doing. Yes, there are
other ways to do it - but these would only increase the complexity of
my application, with an insufficient return for this sacrifice. My
application is quite simple as is, using UUID as my preferred type
for objects that will be accessed on their own through external
interfaces.

Cheers,
mark


> As a similar example (using MD5):
>
> CREATE SEQUENCE marks_seq START 1 INCREMENT 1;
> CREATE TABLE your_tbl (
> your_key VARCHAR(32) NOT NULL DEFAULT md5(nextval('marks_seq')),
> your_picture BYTEA NOT NULL,
> PRIMARY KEY (your_key));
>
> INSERT INTO your_tbl (your_picture) VALUES ('some bytea data');
>
> select * from your_tbl ;
> your_key | your_picture
> ----------------------------------+-----------------
> c4ca4238a0b923820dcc509a6f75849b | some bytea data
>
> same functionality from your standpoint.

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/