Horton

Thread

  1. New data type: uniqueidentifier

    Dmitry G. Mastrukov <dmitry@taurussoft.org> — 2001-06-26T19:59:06Z

    Hi, All!

I've developed new data type for PostgreSQL - uniqueidentifier - 128-bit
value claims to be unique across Universe. It depends on libuuid from
e2fsprogs by Theodore Ts'o. Now I use it in my project. Everybody can grab
it from
    http://taurussoft.chat.ru/uniqueidentifier-0.1.9.tar.gz

Before announce this new type through pgsql-announce I want to clear for
myself some things.
I've marked "=" operator with HASH clause (and planner has started to use
hash jons). But as I understand the right way is to create special hash
function (may be wrapper for hash_any(), isn't it?) and register it for hash
as for btree method.
So is it desirable to mark "=" as HASH for this type (seems internal 16 byte
representation will be hash well) and if yes how can I create hash sort
method for uniqueidentifier?

regards,
Dmitry

PS. If you decide to install uniqueidentifier look at the date of
uuid/uuid.h somewhere in INCLUDE path. Sometimes it's necessary to manualy
enter "make install" in lib/uuid directory of e2fsprogs.

  2. Re: New data type: uniqueidentifier

    alex@pilosoft.com — 2001-06-26T22:02:46Z

    On Tue, 26 Jun 2001, Dmitry G. Mastrukov wrote:

> myself some things.
> I've marked "=" operator with HASH clause (and planner has started to use
> hash jons). But as I understand the right way is to create special hash
> function (may be wrapper for hash_any(), isn't it?) and register it for hash
> as for btree method.

No. Currently, there's no way to specify a hash function for a given
operator, it always uses a builtin function that operates on memory
representation of a value.

There's no need (or possibility) to register a hash with btree method.

> So is it desirable to mark "=" as HASH for this type (seems internal 16 byte
> representation will be hash well) and if yes how can I create hash sort
> method for uniqueidentifier?
You can mark it hashable, since two identical uuid values would have
identical memory representation and thus the same hash value. 

I'd look at your code, but that is URL too slow, in 5 minutes downloaded
1000 bytes...

> regards,
> Dmitry
> 
> PS. If you decide to install uniqueidentifier look at the date of
> uuid/uuid.h somewhere in INCLUDE path. Sometimes it's necessary to manualy
> enter "make install" in lib/uuid directory of e2fsprogs.

  3. Re: New data type: uniqueidentifier

    Dmitry G. Mastrukov <dmitry@taurussoft.org> — 2001-06-27T01:48:36Z

    Alex Pilosov <alex@pilosoft.com> wrote:
> 
> I'd look at your code, but that is URL too slow, in 5 minutes downloaded
> 1000 bytes...
> 
It's possible now to grab from another location

http://fitmark.net/taurussoft/uniqueidentifier-0.1.9.tar.gz

regards,
Dmitry

  4. Re: New data type: uniqueidentifier

    Peter Eisentraut <peter_e@gmx.net> — 2001-06-27T14:54:20Z

    Dmitry G. Mastrukov writes:

> I've developed new data type for PostgreSQL - uniqueidentifier - 128-bit
> value claims to be unique across Universe. It depends on libuuid from
> e2fsprogs by Theodore Ts'o.

ISTM that this should be a function, not a data type.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  5. Re: New data type: uniqueidentifier

    Dmitry G. Mastrukov <dmitry@taurussoft.org> — 2001-06-27T22:21:11Z

    Alex Pilosov <alex@pilosoft.com> wrote:
> On Tue, 26 Jun 2001, Dmitry G. Mastrukov wrote:
>
> > myself some things.
> > I've marked "=" operator with HASH clause (and planner has started to
use
> > hash jons). But as I understand the right way is to create special hash
> > function (may be wrapper for hash_any(), isn't it?) and register it for
hash
> > as for btree method.
>
> No. Currently, there's no way to specify a hash function for a given
> operator, it always uses a builtin function that operates on memory
> representation of a value.
>
> There's no need (or possibility) to register a hash with btree method.
>
Strange. When I execute following query (slightly modified query from User's
Guide chapter 7.6)

SELECT am.amname AS acc_name,
       opc.opcname AS ops_name,
       opr.oprname AS ops_comp
    FROM pg_am am, pg_amop amop,
         pg_opclass opc, pg_operator opr
    WHERE amop.amopid = am.oid AND
          amop.amopclaid = opc.oid AND
          amop.amopopr = opr.oid
    ORDER BY ops_name, ops_comp;

I see both hash and btree amname for builtin opclasses. For example

 acc_name | ops_name | ops_comp
----------+----------+----------
 btree    | int4_ops | <
 btree    | int4_ops | <=
 btree    | int4_ops | =
 hash     | int4_ops | =
 btree    | int4_ops | >
 btree    | int4_ops | >=

But new type has no hash for "=". Plus I saw hash functions for builtin
types in source code. So can I achieve for created type such intergration
with Postgres as for builtin types? Or am I understanding something wrong?

regards,
Dmitry

  6. Re: New data type: uniqueidentifier

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-28T15:12:48Z

    "Dmitry G. Mastrukov" <dmitry@taurussoft.org> writes:
> Alex Pilosov <alex@pilosoft.com> wrote:
>> On Tue, 26 Jun 2001, Dmitry G. Mastrukov wrote:
> I've marked "=" operator with HASH clause (and planner has started to
> use
> hash jons). But as I understand the right way is to create special hash
> function (may be wrapper for hash_any(), isn't it?) and register it for
> hash
> as for btree method.
>> 
>> No. Currently, there's no way to specify a hash function for a given
>> operator, it always uses a builtin function that operates on memory
>> representation of a value.

> Strange. When I execute following query (slightly modified query from User's
> Guide chapter 7.6)

You're looking at support for hash indexes, which have nothing to do
with hash joins.

*Why* they have nothing to do with hash joins, I dunno.  You'd think
that using the same hash functions for both would be a good idea.
But that's not how it's set up at the moment.

			regards, tom lane

  7. Re: New data type: uniqueidentifier

    Dmitry G. Mastrukov <dmitry@taurussoft.org> — 2001-06-29T01:48:19Z

     Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dmitry G. Mastrukov" <dmitry@taurussoft.org> writes:
> > Alex Pilosov <alex@pilosoft.com> wrote:
> >> On Tue, 26 Jun 2001, Dmitry G. Mastrukov wrote:
> > I've marked "=" operator with HASH clause (and planner has started to
> > use
> > hash jons). But as I understand the right way is to create special hash
> > function (may be wrapper for hash_any(), isn't it?) and register it for
> > hash
> > as for btree method.
> >>
> >> No. Currently, there's no way to specify a hash function for a given
> >> operator, it always uses a builtin function that operates on memory
> >> representation of a value.
>
> > Strange. When I execute following query (slightly modified query from
User's
> > Guide chapter 7.6)
>
> You're looking at support for hash indexes, which have nothing to do
> with hash joins.
>
> *Why* they have nothing to do with hash joins, I dunno.  You'd think
> that using the same hash functions for both would be a good idea.
> But that's not how it's set up at the moment.
>
OK. It's clear for me now. Thanks.
But should I create support for hash indexes? Since builtin types have such
support I want it too for uniqueidentifier :) How can I make it?

regards,
Dmitry

  8. Re: New data type: uniqueidentifier

    Thomas Swan <tswan@olemiss.edu> — 2001-07-02T16:13:41Z

    Peter Eisentraut wrote:

>Dmitry G. Mastrukov writes:
>
>>I've developed new data type for PostgreSQL - unique identifier - 128-bit
>>value claims to be unique across Universe. It depends on libuuid from
>>e2fsprogs by Theodore Ts'o.
>>
>
>ISTM that this should be a function, not a data type.
>
I'd second the function idea: function uuid( ) returns an int8 value; 
don't create a bazillion datatypes.  Besides, 128 bit numbers are 7 byte 
integers.   PostgreSQL has an int8 (8 byte integer) datatype.  While I 
like the UUID function idea, I'd recommend a better solution to creating 
an "unique" identifier.  Why not create a serial8 datatype: int8 with an 
int8 sequence = 256bit "unique" number.  {Yes, I know I'm violating my 
first sentence.}  Then, you'd have the same thing (or better) AND your 
not relying on randomness.

  9. Re: Re: New data type: uniqueidentifier

    alex@pilosoft.com — 2001-07-02T18:39:23Z

    On Mon, 2 Jul 2001, Thomas Swan wrote:

> Peter Eisentraut wrote:
> 
> >Dmitry G. Mastrukov writes:
> >
> >>I've developed new data type for PostgreSQL - unique identifier - 128-bit
> >>value claims to be unique across Universe. It depends on libuuid from
> >>e2fsprogs by Theodore Ts'o.
> >>
> >
> >ISTM that this should be a function, not a data type.
> >
> I'd second the function idea: function uuid( ) returns an int8 value; 
> don't create a bazillion datatypes.  Besides, 128 bit numbers are 7 byte 
> integers.   PostgreSQL has an int8 (8 byte integer) datatype.  While I 
> like the UUID function idea, I'd recommend a better solution to creating 
> an "unique" identifier.  Why not create a serial8 datatype: int8 with an 
> int8 sequence = 256bit "unique" number.  {Yes, I know I'm violating my 
> first sentence.}  Then, you'd have the same thing (or better) AND your 
> not relying on randomness.  

I don't think you know what UUID is. It is NOT just a unique randon
number. There are specific rules for construction of such number, specific
rules for comparison of numbers (no, its not bit-by-bit), thus a datatype
is most appropriate answer. 

-alex

  10. Re: Re: New data type: uniqueidentifier

    Peter Eisentraut <peter_e@gmx.net> — 2001-07-02T19:20:57Z

    Alex Pilosov writes:

> I don't think you know what UUID is. It is NOT just a unique randon
> number. There are specific rules for construction of such number, specific
> rules for comparison of numbers (no, its not bit-by-bit), thus a datatype
> is most appropriate answer.

A data type may be appropriate for storing these values, but not for
generating them.  Functions generate stuff, data types store stuff.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  11. Re: Re: New data type: uniqueidentifier

    alex@pilosoft.com — 2001-07-02T21:27:37Z

    On Mon, 2 Jul 2001, Peter Eisentraut wrote:

> Alex Pilosov writes:
> 
> > I don't think you know what UUID is. It is NOT just a unique randon
> > number. There are specific rules for construction of such number, specific
> > rules for comparison of numbers (no, its not bit-by-bit), thus a datatype
> > is most appropriate answer.
> 
> A data type may be appropriate for storing these values, but not for
> generating them.  Functions generate stuff, data types store stuff.

Sorry, apparently we misunderstood each other but are really in full
agreement.

Dmitry's stuff contains both datatype (uniqueidentifier), a function to
generate a new object of that type (newid), and a set of functions to
implement comparison operators for that type.

I don't see anything wrong with that setup, but maybe I'm still missing
something?

-alex

  12. RE: Re: New data type: uniqueidentifier

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-07-03T01:37:33Z

    > don't create a bazillion datatypes.  Besides, 128 bit numbers are 7
> byte integers.

Hang on:  128 div 8 = 16 byte integer

> PostgreSQL has an int8 (8 byte integer) datatype.

And therefore it is a _64_ bit integer and you can't have a 256bit unique
number in it...

> While I like the UUID function idea, I'd recommend a better solution to
> creating an "unique" identifier.  Why not create a serial8 datatype:
> int8 with an int8 sequence = 256bit "unique" number.  {Yes, I know
> violating my first sentence.}  Then, you'd have the same thing (or
> better) AND your not relying on randomness.

Chris

  13. Re: Re: New data type: uniqueidentifier

    Thomas Swan <tswan@olemiss.edu> — 2001-07-03T01:54:01Z

    I sit corrected.  

*slightly humbled*

Why not do an unsigned int16 to hold your UUID generated numbers.   
Ultimately, this would seem to be a more general solution and accomplish 
your goals at the sametime.  Or, am I completely missing something.

Christopher Kings-Lynne wrote:

>>don't create a bazillion datatypes.  Besides, 128 bit numbers are 7
>>byte integers.
>>
>
>Hang on:  128 div 8 = 16 byte integer
>
>>PostgreSQL has an int8 (8 byte integer) datatype.
>>
>
>And therefore it is a _64_ bit integer and you can't have a 256bit unique
>number in it...
>
>>While I like the UUID function idea, I'd recommend a better solution to
>>creating an "unique" identifier.  Why not create a serial8 datatype:
>>int8 with an int8 sequence = 256bit "unique" number.  {Yes, I know
>>violating my first sentence.}  Then, you'd have the same thing (or
>>better) AND your not relying on randomness.
>>
>
>Chris
>

  14. Re: Re: New data type: uniqueidentifier

    alex@pilosoft.com — 2001-07-03T02:30:50Z

    On Mon, 2 Jul 2001, Thomas Swan wrote:

> I sit corrected.  
> 
> *slightly humbled*
> 
> Why not do an unsigned int16 to hold your UUID generated numbers.   
Not a good idea, since rules for comparison of UUID are wierd and are
_definitely_ not same as for comparison of int16.

> Ultimately, this would seem to be a more general solution and accomplish 
> your goals at the sametime.  Or, am I completely missing something.

  15. Re: New data type: uniqueidentifier

    Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-07-03T15:05:18Z

    Where can I find some more information on it?  I'm curious now.

Alex Pilosov wrote:

>On Mon, 2 Jul 2001, Thomas Swan wrote:
>
>>I sit corrected.  
>>
>>*slightly humbled*
>>
>>Why not do an unsigned int16 to hold your UUID generated numbers.   
>>
>Not a good idea, since rules for comparison of UUID are wierd and are
>_definitely_ not same as for comparison of int16.
>
>>Ultimately, this would seem to be a more general solution and accomplish 
>>your goals at the sametime.  Or, am I completely missing something.
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

  16. Re: Re: New data type: uniqueidentifier

    Peter Eisentraut <peter_e@gmx.net> — 2001-07-03T15:33:09Z

    Alex Pilosov writes:

> Dmitry's stuff contains both datatype (uniqueidentifier), a function to
> generate a new object of that type (newid), and a set of functions to
> implement comparison operators for that type.
>
> I don't see anything wrong with that setup, but maybe I'm still missing
> something?

It would be much simpler if you stored the unique id in varchar or text.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

  17. Re: New data type: uniqueidentifier

    alex@pilosoft.com — 2001-07-03T15:37:20Z

    If you mean the [proposed?] standard itself, here is a good description of
it:
http://www.ics.uci.edu/pub/ietf/webdav/uuid-guid/draft-leach-uuids-guids-01.txt

It was a proposed IETF standard, however, IETF standardization failed
because ISO already ratified it as a DCE/RPC standard ISO 11578, however,
the above URL provides far better description of UUIDs than ISO standard
itself

 On Tue, 3 Jul 2001, Thomas Swan wrote:

> Where can I find some more information on it?  I'm curious now.

  18. Re: Re: New data type: uniqueidentifier

    alex@pilosoft.com — 2001-07-03T15:52:31Z

    On Tue, 3 Jul 2001, Peter Eisentraut wrote:


>> Dmitry's stuff contains both datatype (uniqueidentifier), a function to
>> generate a new object of that type (newid), and a set of functions to
>> implement comparison operators for that type.

> It would be much simpler if you stored the unique id in varchar or text.
Peter,

UUIDs have specific rules for comparison of them. Its so much easier to
compare them via a<b than uuid_lt(a,b). If one wanted to make a meaningful
index on uuid value, normal ordering of varchar would not suffice...

-alex

  19. Re: Re: New data type: uniqueidentifier

    Dmitry G. Mastrukov <dmitry@taurussoft.org> — 2001-07-03T20:19:23Z

    Peter Eisentraut wrote:

>Alex Pilosov writes:
>
>>Dmitry's stuff contains both datatype (uniqueidentifier), a function to
>>generate a new object of that type (newid), and a set of functions to
>>implement comparison operators for that type.
>>
>>I don't see anything wrong with that setup, but maybe I'm still missing
>>something?
>>
>
>It would be much simpler if you stored the unique id in varchar or text.
>
Are you sure varchar comparision will be quickly than current 
implementation? Next, varchar will need 36 byte, uniqueidentifier takes 
16. Next, indexing - IMHO current stuff more suitable for indexes. Some 
time ago I saw some stuff which deals with uniqueidentifiers for 
Interbase. It uses your scheme with chars. But it strip "-" from string 
and reverts it to efficiently use indexes (uid sometimes uses 
MAC-address as part of itself, so MAC should go first in string). Weird 
scheme for me!

regards,
Dmitry