Re: [GENERAL] GUID/UUID Support
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 [EMAIL PROTECTED] wrote: On 1/17/07, Patrick Earl [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] GUID/UUID Support
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] wrote: On 1/17/07, Patrick Earl [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] GUID/UUID Support
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. signature.asc Description: Digital signature
Re: [GENERAL] GUID/UUID Support
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? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] GUID/UUID Support
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] GUID/UUID Support
On 1/17/07, Patrick Earl [EMAIL PROTECTED] 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/
[GENERAL] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] GUID/UUID Support
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) [EMAIL PROTECTED] Cell phone: +1 586-873-8813 Work email: [EMAIL PROTECTED] pgp2rCeCe9oZv.pgp Description: PGP signature
Re: [GENERAL] GUID/UUID Support
On 1/16/07, David Lee Lambert [EMAIL PROTECTED] 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/
