Re: uuid type for postgres

From what you said:

> 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

under definition of security, "Something that gives or assures safety"

Firstly, the use case is a security-related thing, i.e. it RESTRICTS someone
from doing something (mass dump of data) by introducing a SECURITY MEASURE
(obfuscating a key). Enough about that discussion.

Second, while you did mention a distributed environment as an advantage of
using UUID, there was no mention of distributed sites (New York, Toronto) in
your actual use case; so don't expect us to be psychic.

Third, if you feel that you need UUID, by all means, USE IT! This topic,
IMHO, is now *WAY* out of scope. You have the information from Tom et al and
if you want to go ahead with a new PostgreSQL uuid type then develop it,
test it, and submit it for 8.2.

Lastly, people are here to help you, not to argue with you (most of the
time). No one told you not to use it, they just offered alternatives and
discussion on whether it should be added to core.

You've made the case that it would be useful to have, so go forth and
develop it. I don't see how there can be much more discussion here.

On 9/8/05, mark(at)mark(dot)mielke(dot)cc <mark(at)mark(dot)mielke(dot)cc> wrote:
>
> 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(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)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/
>
>

--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/