Re: uuid type for postgres
От | mark@mark.mielke.cc |
---|---|
Тема | Re: uuid type for postgres |
Дата | |
Msg-id | 20050908162845.GA15460@mark.mielke.cc обсуждение исходный текст |
Ответ на | Re: uuid type for postgres ("Jonah H. Harris" <jonah.harris@gmail.com>) |
Ответы |
Re: uuid type for postgres
("Jonah H. Harris" <jonah.harris@gmail.com>)
|
Список | 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 bindthem... http://mark.mielke.cc/
В списке pgsql-hackers по дате отправления: