Re: GUID for postgreSQL

From: Chris Travers <chris(at)travelamericas(dot)com>
To: Ben <bench(at)silentmedia(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: GUID for postgreSQL
Date: 2005-07-27 21:20:28
Message-ID: 42E7FA9C.6030804@travelamericas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

You could guarantee it, for example...

Something like (pseudocode here):
create sequence local_id;
create domain guid AS text default ('54-' || (nextval(local_id))::text);
where 54 is the database id. In this way, every inserted GUID will be
guaranteed to contain a GUID in two parts: A database identifier and a
locally unique local identifier. These could then be parsed in a
reasonable way.

The only way I think one can come up with *guaranteed* globally unique
identifiers is to place such information such as we use with other
things that must be globally unique: have a locally unique identifier
along with a globally unique location identifieer. Sort of like we have
with IP addresses, MAC addresses, telephone numbers, etc...

Best Wishes,
Chris Travers
Metatron Technology Consulting

Ben wrote:

>Yes, this is the problem with GUIDs... you can calculate them by mashing
>toghether things like the time, a network address, and some random
>numbers, which makes it very unlikely for a collision.... but at the end
>of the day that G stand for global, *not* guaranteed.
>
>On Wed, 27 Jul 2005, Scott Marlowe wrote:
>
>
>
>>On Wed, 2005-07-27 at 15:32, John DeSoi wrote:
>>
>>
>>>On Jul 27, 2005, at 4:49 AM, Tino Wildenhain wrote:
>>>
>>>
>>>
>>>>I'd create a sequence:
>>>>
>>>>CREATE SEQUENCE global_unique_id_seq;
>>>>
>>>>and a function:
>>>>
>>>>CREATE OR REPLACE FUNCTION newid()
>>>> RETURNS text AS
>>>>$BODY$ SELECT nextval('global_unique_id_seq')::text; $BODY$
>>>> LANGUAGE 'sql' VOLATILE;
>>>>
>>>>
>>>>now every call to newid() returns a garantied unique id for
>>>>say the next 18446744073709551616 calls.
>>>>Of course you can obfuscate the ID even more using
>>>>md5, include servername and so on, but this will not improve
>>>>security in any way (unless you mix data with 2nd database)
>>>>
>>>>
>>>This is not really a viable replacement for a GUID == globally unique
>>>identifier. Here global means that if I use the application in
>>>multiple databases, I'm guaranteed that no two identifiers will be
>>>the same. Using a sequence will only support uniqueness for a single
>>>database.
>>>
>>>
>>So, how can two databases, not currently talking to one another,
>>guarantee that their GUIDs don't collide? using a large randomly
>>generated name space only reduces the chances of collision, it doesn't
>>actually guarantee it.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2005-07-27 21:26:42 Re: GUID for postgreSQL
Previous Message Magnus Hagander 2005-07-27 21:03:00 Re: GUID for postgreSQL