Re: YAGT (yet another GUID thread) - Mailing list pgsql-general

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