Thread: GUID/UUID Support
Greetings all. I've been poking around the web and trying to track
the status of the GUID/UUID patches. In the todo list, that item is
not marked as going into 8.3, and I feel this is unfortunate.
Searching google for "postgresql guid -guide" comes up with around
200,000 hits. It's obviously been a popular topic over time.
There was some discussion about including a generator in the core or
not. Even a random generator (version 4 UUID) would be great in my
opinion. The Wikipedia article about UUIDs produces some statistics
about a random source ID that are interesting.
http://en.wikipedia.org/wiki/UUID
If you generate 2^41 (2'199'023'255'552) true random ids, the
probability of getting a duplicate is 0.000'000'000'000'4. For
comparison, the page indicates that the probability of getting hit by
a meteorite is 0.000'000'000'06.
If there are qualms about a generator that only produces one type of
IDs, could the database not just provide a way to generate a "version
X" uuid? Some versions would be implemented later.
One thing that is quite important, to me at least, is to make the GUID
generation functionality cross-platform. Using a random UUID
technique for an initial implementation would simplify this issue.
Even if the generator is punted to contrib, I would like to see at
least native UUID field support in the next version.
Patrick
On Tuesday 16 January 2007 05:47 pm, Patrick Earl wrote: > Greetings all. I've been poking around the web and trying to track > the status of the GUID/UUID patches. In the todo list, that item is > not marked as going into 8.3, and I feel this is unfortunate. > > [...] > > One thing that is quite important, to me at least, is to make the GUID > generation functionality cross-platform. Using a random UUID > technique for an initial implementation would simplify this issue. The problem with a random UUID generator is: where do you get the random numbers? However, there are really only two platforms to worry about: POSIX (use libuuid) and Win32 (there is probably a Win32 function to generate it, since MS SQL Server and the .NET API both provide GUID generation; but I can't seem to find it by searching the Web). Anyway, the uniqueidentifier project on GBorg seems like it has what you want: a user-defined type, and a generator. I think the main thing it needs is some code cleanup/robustness and a lot of production testing. -- David Lee Lambert (recent WSU grad) <as4109@wayne.edu> Cell phone: +1 586-873-8813 Work email: dlambert@bmtcarhaul.com
Attachment
On 1/16/07, David Lee Lambert <as4109@wayne.edu> wrote:
The problem with a random UUID generator is: where do you get the random
numbers? However, there are really only two platforms to worry about:
POSIX (use libuuid) and Win32 (there is probably a Win32 function to generate
it, since MS SQL Server and the .NET API both provide GUID generation; but
I can't seem to find it by searching the Web).
Anyway, the uniqueidentifier project on GBorg seems like it has what you
want: a user-defined type, and a generator. I think the main thing it
needs is some code cleanup/robustness and a lot of production testing.
FYI...
Apache Portable Runtime has a uuid generator, see apr-util package, function is apr_uuid_get(). Underlying it uses one of the following:
a) uuid_create (FreeBSD via libc)
b) uuid_generate (from libuuid)
c) UuidCreate (for Win32)
d) Apache version of a UUID generator
--
Chad
http://www.postgresqlforums.com/
While using third party modules is not really a barrier for people
deep in the PostgreSQL world, here is what I would need to do to use
GUIDs today.
1. Get a set of GUID/UUID patches (there seem to be many) and
recompile the necessary code.
2. Do the same thing for Windows, assuming that the patches were
created in a cross-platform manner. This is a much larger barrier on
Windows, since the tools to recompile things aren't immediately
available and require considerable effort to install and configure.
3. Perform a manual install of the patched server/modules.
4. Get the source code for the higher level libraries (such as
Npgsql). Modify those libraries to support GUIDs. Every time those
libraries are upgraded, re-modify your local copies to support them.
5. If installing to multiple client sites, I also need to ensure that
the patched servers reach them and get around any objections they
might have to running the custom changes.
It's not hard to see why a busy developer wouldn't have time to go
through those steps.
Certainly support for the GUID field type itself is most important.
As for the generators, though they are non-essential, they are very
useful. Other platforms and libraries have standardized on uuid
generators, so I don't see why PostgreSQL can't.
I hope I don't come across as complaining, I'm just trying to give
this feature a bit of a boost. It's undoubtedly a practical feature
(as indicated by the number of implementations and messages about it)
that is supported by other databases (such as oracle and mssql). I
hope that we can dot our Is and cross our Ts and get this feature into
the next version.
Patrick
On 1/17/07, Patrick Earl <patearl@patearl.net> wrote:
Maybe I am oblivious to the reason, but why is there a need for a special data type for GUID/UUIDs? Wouldn't you always be doing an "equality" anyways? Wouldn't a varchar suffice?
--
Chad
http://www.postgresqlforums.com/
Certainly support for the GUID field type itself is most important.
As for the generators, though they are non-essential, they are very
useful. Other platforms and libraries have standardized on uuid
generators, so I don't see why PostgreSQL can't.
Maybe I am oblivious to the reason, but why is there a need for a special data type for GUID/UUIDs? Wouldn't you always be doing an "equality" anyways? Wouldn't a varchar suffice?
Chad
http://www.postgresqlforums.com/
One issue is that UUIDs are only 16 bytes of data. To store the as
text in canonical form requires 36 bytes. As there are alternate
frequently used representations, you also run into potential issues
with input. The GUID type (proposed by Gevik) handles those standard
input variations.
Though I haven't tried it, I would imagine there would be performance
implications when using 36 character keys everywhere to do indexing,
joins, etc.
Another issue is that higher level languages (such as Delphi and .NET)
have GUID field types built in. If the field is just a string field,
it won't map nicely to those higher level types.
Patrick
On 1/17/07, Chad Wagner <chad.wagner@gmail.com> wrote:
> On 1/17/07, Patrick Earl <patearl@patearl.net> wrote:
> > Certainly support for the GUID field type itself is most important.
> > As for the generators, though they are non-essential, they are very
> > useful. Other platforms and libraries have standardized on uuid
> > generators, so I don't see why PostgreSQL can't.
>
> Maybe I am oblivious to the reason, but why is there a need for a special
> data type for GUID/UUIDs? Wouldn't you always be doing an "equality"
> anyways? Wouldn't a varchar suffice?
>
> --
> Chad
> http://www.postgresqlforums.com/
I second the desire for a UUID type in PostgreSQL! I'm aware of the pguuid project, but it's not the same as having it in core and isn't very well maintained. This is such a common database paradigm that it seems reasonable to promote it to first-class citizen status in PostgreSQL. I currently use varchars for UUIDs, but the table size, index size, integrity (validation), and performance would be better if PostgreSQL supported UUIDs directly. On Thu, 18 Jan 2007 10:31:26 -0700, "Patrick Earl" <patearl@patearl.net> said: > One issue is that UUIDs are only 16 bytes of data. To store the as > text in canonical form requires 36 bytes. As there are alternate > frequently used representations, you also run into potential issues > with input. The GUID type (proposed by Gevik) handles those standard > input variations. > > Though I haven't tried it, I would imagine there would be performance > implications when using 36 character keys everywhere to do indexing, > joins, etc. > > Another issue is that higher level languages (such as Delphi and .NET) > have GUID field types built in. If the field is just a string field, > it won't map nicely to those higher level types. > > Patrick > > On 1/17/07, Chad Wagner <chad.wagner@gmail.com> wrote: > > On 1/17/07, Patrick Earl <patearl@patearl.net> wrote: > > > Certainly support for the GUID field type itself is most important. > > > As for the generators, though they are non-essential, they are very > > > useful. Other platforms and libraries have standardized on uuid > > > generators, so I don't see why PostgreSQL can't. > > > > Maybe I am oblivious to the reason, but why is there a need for a special > > data type for GUID/UUIDs? Wouldn't you always be doing an "equality" > > anyways? Wouldn't a varchar suffice? > > > > -- > > Chad > > http://www.postgresqlforums.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Thu, Jan 18, 2007 at 01:09:15PM -0500, Jeremy Haile wrote: > I second the desire for a UUID type in PostgreSQL! I'm aware of the > pguuid project, but it's not the same as having it in core and isn't > very well maintained. Yeah, but it's not going to be added to core until there's some agreement about *what* needs to be added. The point of the external project is that once it has acheived a level of support *then* it can be incorporated. > This is such a common database paradigm that it seems reasonable to > promote it to first-class citizen status in PostgreSQL. It would be helpful if people who wanted UUIDs used the existing projects and pointed any problems so that they can be dealt with. Only then would there be a chance of getting it into the core. > I currently use varchars for UUIDs, but the table size, index size, > integrity (validation), and performance would be better if PostgreSQL > supported UUIDs directly. Obviously the benefits are not so great given you don't use one of the existing UUID types... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> Yeah, but it's not going to be added to core until there's some > agreement about *what* needs to be added. The point of the external > project is that once it has acheived a level of support *then* it can > be incorporated. That's fair. In truth, I only found that pguuid existed fairly recently and haven't had a chance to try it out. I've been scared away a bit from using it do to threads I've read about problems and limitations with it - also there are bug reports listed on the site which haven't been updated in over a year. Still - I'd like to give it a try myself at some point. Has anyone here had a great experience using pguuid?