By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

YAGT (yet another GUID thread)

P: n/a
Has anyone tried [ab]using inet or cidr for storing GUID (or for storing
128bit numbers or hashes or similar stuffs)? It has a nice property in
that one can use hexadecimal notation (like
'FEDC:BA98:7654:3210:FEDC:BA98:7654:3210') when inserting it. Plus IPv6
is 128bit too.

--
dave
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Obvious... when you think about it. I didn't :)

I'm switching right away. The notation doesn't really do anything for
me, but that's fine. I've been using bit(128), but always suspected
that of being unoptimal (for no particular reason).

Anyone know of any caveats about indexing or such? I'm assuming not.

d.
On 15. jan 2004, at 15:17, David Garamond wrote:
Has anyone tried [ab]using inet or cidr for storing GUID (or for
storing 128bit numbers or hashes or similar stuffs)? It has a nice
property in that one can use hexadecimal notation (like
'FEDC:BA98:7654:3210:FEDC:BA98:7654:3210') when inserting it. Plus
IPv6 is 128bit too.

--
dave
---------------------------(end of
broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #2

P: n/a
David Helgason wrote:
I'm switching right away. The notation doesn't really do anything for
me, but that's fine. I've been using bit(128), but always suspected that
of being unoptimal (for no particular reason).


I think bit(128) is quite efficient (OCTET_LENGTH() function shows me
it's using 16 bytes).

Btw, here are the data types and format I've tried/considered to store
GUID in:

- BYTEA (storing the raw bytes; storage = 4+16 = 20 bytes; attlen = -1)

- CHAR/VARCHAR(18) (i'm using "base192" with character set containing
ASCII 64-255. storage = 4+18 = 22 bytes?; attlen = -1)

- CHAR/VARCHAR(22) (using base64, storage = 4+22 = 26 bytes?; attlen = -1)

- INET/CIDR (storage = 24 bytes?; attlen = -1)

- BIT(128) (storage = 16 bytes?; attlen = -1)

PostgreSQL hasn't included a datatype with attlen of exactly 16 bytes,
so all of the above are "variable-length field". My considerations in
choosing the appropriate type for storing GUID are as follow (sorted
from most important to least important):

1. The ease/naturalness of inserting. INET/CIDR is the slight winner
here. For VARCHAR(18)/VARCHAR(22) I have to create a
guidhex_to_base192()/guidhex_to_base64() function, which is not a big
deal. Of course, I can always create/represent GUID as base192/base64
from the start, in which case using VARCHAR(18)/VARCHAR(22) is very easy
too. For BYTEA you have to use "\\000" escape codes in psql. I'm still
having difficulty on how to insert BIT fields using DBD::Pg and
bind_param().

2. "Ease to the eye", that is, they way PostgreSQL displays the data.
For me, INET/CIDR wins here, though VARCHAR(22) looks equally nice too.
VARCHAR(18) and BYTEA makes the display looks weird due to high ASCII
characters and/or control characters. BIT(128) is just too long (and
silly me, I can't seem to find an easy way to display BIT(128) columns
as hex or normal strings).

Of course, we can use ENCODE(col, 'base64') to display BYTEA GUID
column, but it's kind of annoying to having to write that all the time.

3. The compactness/efficiency of storage. Well, none of the above are
the most efficient anyway. We'll have to wait until PostgreSQL
officially supports INT16/INT128/BIGBIGINT/GUID/fixed BYTEA. So either
one is ok to me. 16 vs 22-24 bytes are not that big a deal either. Also,
disk space is getting cheaper every day.

4. Ease of incremental searching. Suppose we're creating a GUI app to
let user type in an item by its ID. VARCHAR(22) is a winner here since
it allows users to type in normal characters in the keyboard and still
lets Pg uses index for searching using "WHERE col LIKE '...%'".

However, most "sane" database design would use another unique code for
most entities that need to be typed in. 128bit (22 characters as base64)
are just too long anyway.

5. The ease of migrating to future "real GUID" datatype. I think using
INET/CIDR will be easiest, as I can just use some simple combination of
builtin Pg string function. But this is a very minor issue since if
we're using a "real GUID" in the future, we most probably can't use our
old GUID anymore, due to different creation algorithm.

So in short, for GUID I now tend to use BYTEA or INET/CIDR. Storing as
base192/base64 feels a little wasteful for me, since I can use
ENCODE(...) to display binary data as base64 anyway. I find BIT(n)
awkward to work with/not properly supported in most languages.

Howver, using INET/CIDR prevents me to use LIKE or ~. So I guess it's
back to BYTEA for me.

--
dave
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #3

P: n/a
On 16. jan 2004, at 12:18, David Garamond wrote:
David Helgason wrote:
I'm switching right away. The notation doesn't really do anything for
me, but that's fine. I've been using bit(128), but always suspected
that of being unoptimal (for no particular reason).
I think bit(128) is quite efficient (OCTET_LENGTH() function shows me
it's using 16 bytes).


Since I'm storing several big piles of data for each GUID, it's not
ally an issue whether the storage is 16, 20, 22, 24, or 26 bytes, but
thanks for the extensive guide. I've not gone over to using a GUID as
PK+FK for the tables, and even if that should become interesting (for
uniqueness across several databases for example), I would prefer a dual
PK of (host-id, serial), where host-ids would be preassigned bit(8)
values or some such.

I was mostly wondering about index efficiency and such. A bit of
testing confirms that this seems to be just fine. Not that I'm
surprised.
4. Ease of incremental searching. Suppose we're creating a GUI app to
let user type in an item by its ID. VARCHAR(22) is a winner here since
it allows users to type in normal characters in the keyboard and still
lets Pg uses index for searching using "WHERE col LIKE '...%'".

However, most "sane" database design would use another unique code for
most entities that need to be typed in. 128bit (22 characters as
base64) are just too long anyway.
In my case, only applications ever specify the GUIDs, so this is a
non-issue.
5. The ease of migrating to future "real GUID" datatype. I think using
INET/CIDR will be easiest, as I can just use some simple combination
of builtin Pg string function. But this is a very minor issue since if
we're using a "real GUID" in the future, we most probably can't use
our old GUID anymore, due to different creation algorithm.
I'm already using 'real' GUIDs, which in my case means that the
database never generates them (since I don't have a
generate_real_guid() function in the database (and don't need to).

Neither GUID project on gborg (mentioned in another thread) seem to be
Mac OSX compatible, which is my current platform (but I want to stay
more-or-less free of platform dependance just yet).
Howver, using INET/CIDR prevents me to use LIKE or ~. So I guess it's
back to BYTEA for me.
Seems useless to me, since the GUIDS are practically random so LIKEness
has no relevance.
So in short, for GUID I now tend to use BYTEA or INET/CIDR. Storing as
base192/base64 feels a little wasteful for me, since I can use
ENCODE(...) to display binary data as base64 anyway. I find BIT(n)
awkward to work with/not properly supported in most languages.


I think I'll be staying with BIT(128) here, since none of the other
really make anything easier. The INET might have been a choice, but it
seems to have to much 'magic' in its output routines for me to feel
comfortable around it.

However the client app uses a hex representation internally (don't tell
me it's silly, it's already coded, due to intricacies of our project
management), and my PL/PgSQL hex2bit() and bit2hex() functions are damn
slow, so I'll be converting them to C any day not-so-soon (tried
PL/Perl too, but even its simpler implementation was 5x slower yet !?)
d.
David Helgason
Over the Edge Entertainments
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #4

P: n/a
David Helgason wrote:
I'm already using 'real' GUIDs, which in my case means that the database
never generates them (since I don't have a generate_real_guid() function
in the database (and don't need to).

Neither GUID project on gborg (mentioned in another thread) seem to be
Mac OSX compatible, which is my current platform (but I want to stay
more-or-less free of platform dependance just yet).
What do you use for generating GUID in OSX? Does OSX have a GUID
generation API/syscall? What is its algorithm, does it show the MAC
address verbatim on the resulting GUID?
However the client app uses a hex representation internally (don't tell
me it's silly, it's already coded, due to intricacies of our project
management), and my PL/PgSQL hex2bit() and bit2hex() functions are damn
slow, so I'll be converting them to C any day not-so-soon (tried PL/Perl
too, but even its simpler implementation was 5x slower yet !?)


Interesting. Care to share your plperl code? I would expect Perl to be
not so far behind C for this (at least if your using hex() &
pack()/unpack() and the "b" template; since the hard work will be done
in C routine anyway).

--
dave
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.