Обсуждение: How can I build OSSP UUID support on Windows to avoid duplicate UUIDs?

Hello,

# Sorry, Saito san' address doesn't seem to exist, so excuse for sending 
again

For a certain reason, I need to build PostgreSQL on Windows with OSSP UUID 
support to use UUID for primary keys.  I have to use Visual Studio 2010.

The original source code for OSSP UUID and its fork for Windows can be 
downloaded from:

http://www.ossp.org/pkg/lib/uuid/
http://sourceforge.jp/projects/sfnet_osspuuidwin32/

I built the latest version 1.6.2 and used uuid_generate_v4() as the default 
value for a primary key column of type uuid.  However, when I insert 200-300 
rows, an unique constraint violation error is reported.  It occurs 
repeatedly in several retries.

I found some people hit the same problem in the past, but they didn't seem 
to get the solution:

http://www.postgresql.org/message-id/039e01c8b63c$d0c95e90$60096cdb@IBMC9A0F63B40D
http://www.postgresql.org/message-id/018d01c8dec6$2a14b470$0b01a8c0@IBMC9A0F63B40D
http://www.postgresql.org/message-id/98B20744191F464DB598A25AB00BC125@acer08f817a9b5

When I copy lib\uuid_ossp.dll in the PostgreSQL community package, which I 
guess Dave Page san maintains, to my installation, the error does not occur. 
So, OSSP UUID library is the cause.

Saito san published a patch for OSSP UUID on his site:

http://winpg.jp/~saito/pg_work/OSSP_win32/msvc/

And Dave san showed interest in it:

http://www.postgresql.org/message-id/SNT127-W18C42125E5FD719BA514CEFF1F0@phx.gbl

How can I build the OSSP UUID library that functions properly on Windows? Is 
OSSP UUID 1.6.2 + Saito-san's patch the exact source for the uuid-ossp.dll 
in thecommunity binary?


Regards
MauMau

Hi MauMau-san

it my bug patch applied to ver 1.6.2....sorry..
then, I made the next patch is there, please see,
http://winpg.jp/~saito/pg_work/OSSP_win32/

I will be adjusted and Ralf-san again.

best regards,
Hiroshi Saito

(2013/10/30 21:45), MauMau wrote:
> Hello,
> 
> # Sorry, Saito san' address doesn't seem to exist, so excuse for sending
> again
> 
> For a certain reason, I need to build PostgreSQL on Windows with OSSP
> UUID support to use UUID for primary keys.  I have to use Visual Studio
> 2010.
> 
> The original source code for OSSP UUID and its fork for Windows can be
> downloaded from:
> 
> http://www.ossp.org/pkg/lib/uuid/
> http://sourceforge.jp/projects/sfnet_osspuuidwin32/
> 
> I built the latest version 1.6.2 and used uuid_generate_v4() as the
> default value for a primary key column of type uuid.  However, when I
> insert 200-300 rows, an unique constraint violation error is reported. 
> It occurs repeatedly in several retries.
> 
> I found some people hit the same problem in the past, but they didn't
> seem to get the solution:
> 
> http://www.postgresql.org/message-id/039e01c8b63c$d0c95e90$60096cdb@IBMC9A0F63B40D
> 
> http://www.postgresql.org/message-id/018d01c8dec6$2a14b470$0b01a8c0@IBMC9A0F63B40D
> 
> http://www.postgresql.org/message-id/98B20744191F464DB598A25AB00BC125@acer08f817a9b5
> 
> 
> When I copy lib\uuid_ossp.dll in the PostgreSQL community package, which
> I guess Dave Page san maintains, to my installation, the error does not
> occur. So, OSSP UUID library is the cause.
> 
> Saito san published a patch for OSSP UUID on his site:
> 
> http://winpg.jp/~saito/pg_work/OSSP_win32/msvc/
> 
> And Dave san showed interest in it:
> 
> http://www.postgresql.org/message-id/SNT127-W18C42125E5FD719BA514CEFF1F0@phx.gbl
> 
> 
> How can I build the OSSP UUID library that functions properly on
> Windows? Is OSSP UUID 1.6.2 + Saito-san's patch the exact source for the
> uuid-ossp.dll in thecommunity binary?
> 
> 
> Regards
> MauMau
> 
> 
> 

Hiroshi Saito escribió:
> Hi MauMau-san
> 
> it my bug patch applied to ver 1.6.2....sorry..
> then, I made the next patch is there, please see,
> http://winpg.jp/~saito/pg_work/OSSP_win32/
> 
> I will be adjusted and Ralf-san again.

At this point, I think we need to consider ossp-uuid as dead code.
Last release was in 2008 and there are lots of bugs reports still open,
even some with trivial patches attached.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> At this point, I think we need to consider ossp-uuid as dead code.

Yeah, but what shall we replace it with?  And can we preserve the
API contrib/uuid-ossp offers?  (Maybe we shouldn't even try, but
just deprecate that module and start fresh.)
        regards, tom lane

On 10/30/2013 12:43 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> At this point, I think we need to consider ossp-uuid as dead code.
> Yeah, but what shall we replace it with?  And can we preserve the
> API contrib/uuid-ossp offers?  (Maybe we shouldn't even try, but
> just deprecate that module and start fresh.)
>
>             


An alternative would be for someone to put up a fork on, say, github, 
and maintain it. (No, I'm not volunteering.)

cheers

andrew

From: "Hiroshi Saito" <hiroshi@winpg.jp>
> it my bug patch applied to ver 1.6.2....sorry..
> then, I made the next patch is there, please see,
> http://winpg.jp/~saito/pg_work/OSSP_win32/
>
> I will be adjusted and Ralf-san again.

Thanks.  Yes, I wrote the wrong URL and meant this one.  Is this patch 
(uuid-1.6.2_win32_patch2) the one that EnterpriseDB is using to publish the 
community PostgreSQL package?

Regards
MauMau

From: "Tom Lane" <tgl@sss.pgh.pa.us>
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> At this point, I think we need to consider ossp-uuid as dead code.
>
> Yeah, but what shall we replace it with?  And can we preserve the
> API contrib/uuid-ossp offers?  (Maybe we shouldn't even try, but
> just deprecate that module and start fresh.)

Would it be welcomed in this community if a patch for built-in UUID 
generation functions are provided?  (I'm not saying I can provide them 
soon.)  According to the following page, Linux has libuuid.so.  Microsoft 
provides UuidCreate() as a Win32 API.  I don't know about other UNIXes.

MySQL, Oracle (and perhaps other DBMSs) offer UUID generation functions.  I 
feel it convenient if I can use that function as the default value for a 
column.

Regards
MauMau

"MauMau" <maumau307@gmail.com> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> Yeah, but what shall we replace it with?  And can we preserve the
>> API contrib/uuid-ossp offers?  (Maybe we shouldn't even try, but
>> just deprecate that module and start fresh.)

> Would it be welcomed in this community if a patch for built-in UUID 
> generation functions are provided?

Based on what?  The reason we've held this code at arms length (in a
contrib module) is exactly that we weren't too satisfied with the
portability or adequacy of the generation methods.  Moving the functions
into core doesn't do anything to make that better; rather, it raises the
portability bar even higher, as well as making it almost impossible to
contemplate further API changes.

Note the lack of enthusiasm for taking on maintainership of the OSSP
code.  Pushing it into core would mean that we're buying into that
maintainership, hook line and sinker.  I don't think that such a
proposal would fly.
        regards, tom lane

On Wed, Oct 30, 2013 at 5:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "MauMau" <maumau307@gmail.com> writes:
>> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>>> Yeah, but what shall we replace it with?  And can we preserve the
>>> API contrib/uuid-ossp offers?  (Maybe we shouldn't even try, but
>>> just deprecate that module and start fresh.)
>
>> Would it be welcomed in this community if a patch for built-in UUID
>> generation functions are provided?
>
> Based on what?  The reason we've held this code at arms length (in a
> contrib module) is exactly that we weren't too satisfied with the
> portability or adequacy of the generation methods.  Moving the functions
> into core doesn't do anything to make that better; rather, it raises the
> portability bar even higher, as well as making it almost impossible to
> contemplate further API changes.
>
> Note the lack of enthusiasm for taking on maintainership of the OSSP
> code.  Pushing it into core would mean that we're buying into that
> maintainership, hook line and sinker.  I don't think that such a
> proposal would fly.

ISTM that the biggest problem is that we don't have a random number
generator which generates enough bits of randomness to implement
uuid_generate_v3.  I think relatively few people would cry if we
didn't support uuid_generate_v1(), and the others all look simple
enough, provided there's somewhere to get lots of random bits.

On Linux, it seems like we could get those bits from /dev/urandom,
though I'm not sure how efficient that would be for the case where
many UUIDs are being generated at once.  But that wouldn't be very
portable.  It's tempting to think that we'd need a PRNG that generates
wider values, for which we might find other application also.  But I'm
not volunteering to be the one to create such a thing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2013-10-31 08:22:14 -0400, Robert Haas wrote:
> On Wed, Oct 30, 2013 at 5:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "MauMau" <maumau307@gmail.com> writes:
> > Note the lack of enthusiasm for taking on maintainership of the OSSP
> > code.  Pushing it into core would mean that we're buying into that
> > maintainership, hook line and sinker.  I don't think that such a
> > proposal would fly.
> 
> ISTM that the biggest problem is that we don't have a random number
> generator which generates enough bits of randomness to implement
> uuid_generate_v3.  I think relatively few people would cry if we
> didn't support uuid_generate_v1(), and the others all look simple
> enough, provided there's somewhere to get lots of random bits.

Yea, I think restricting ourselves to v3/4/5 is a sensible thing.

> On Linux, it seems like we could get those bits from /dev/urandom,
> though I'm not sure how efficient that would be for the case where
> many UUIDs are being generated at once.  But that wouldn't be very
> portable.  It's tempting to think that we'd need a PRNG that generates
> wider values, for which we might find other application also.  But I'm
> not volunteering to be the one to create such a thing.

We could copy ossp's implementation, it's just 200 lines and seems to
have a compatible license.
util-linux's libuuid is BSD as well and seems to have some windows
support although that's by memory.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

From: "Robert Haas" <robertmhaas@gmail.com>
> ISTM that the biggest problem is that we don't have a random number
> generator which generates enough bits of randomness to implement
> uuid_generate_v3.  I think relatively few people would cry if we
> didn't support uuid_generate_v1(), and the others all look simple
> enough, provided there's somewhere to get lots of random bits.
>
> On Linux, it seems like we could get those bits from /dev/urandom,
> though I'm not sure how efficient that would be for the case where
> many UUIDs are being generated at once.  But that wouldn't be very
> portable.  It's tempting to think that we'd need a PRNG that generates
> wider values, for which we might find other application also.  But I'm
> not volunteering to be the one to create such a thing.

OpenSSL provides rand_bytes() which generates random bytes of any length. 
It uses /dev/urandom or /dev/random on UNIX/Linux and Crypto API of 
Microsoft on Windows.

Regards
MauMau

On 10/30/13, 12:43 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> At this point, I think we need to consider ossp-uuid as dead code.
> 
> Yeah, but what shall we replace it with?

One possibility:

https://github.com/petere/pglibuuid

Not sure whether that has a chance of working on Windows.

On Thu, Oct 31, 2013 at 09:54:14PM +0900, MauMau wrote:
> From: "Robert Haas" <robertmhaas@gmail.com>
>> ISTM that the biggest problem is that we don't have a random number
>> generator which generates enough bits of randomness to implement
>> uuid_generate_v3.  I think relatively few people would cry if we
>> didn't support uuid_generate_v1(), and the others all look simple
>> enough, provided there's somewhere to get lots of random bits.
>>
>> On Linux, it seems like we could get those bits from /dev/urandom,
>> though I'm not sure how efficient that would be for the case where
>> many UUIDs are being generated at once.  But that wouldn't be very
>> portable.  It's tempting to think that we'd need a PRNG that generates
>> wider values, for which we might find other application also.  But I'm
>> not volunteering to be the one to create such a thing.
>
> OpenSSL provides rand_bytes() which generates random bytes of any length. 
> It uses /dev/urandom or /dev/random on UNIX/Linux and Crypto API of  
> Microsoft on Windows.

What about using a cipher here as the PRNG? It seems like using openssl
rand_bytes() to seed aes in ctr would work ok without starving the system of
entropy when making a lot of uuids.

Garick

On Thu, Oct 31, 2013 at 1:02 PM, Garick Hamlin <ghamlin@isc.upenn.edu> wrote:
> On Thu, Oct 31, 2013 at 09:54:14PM +0900, MauMau wrote:
>> From: "Robert Haas" <robertmhaas@gmail.com>
>>> ISTM that the biggest problem is that we don't have a random number
>>> generator which generates enough bits of randomness to implement
>>> uuid_generate_v3.  I think relatively few people would cry if we
>>> didn't support uuid_generate_v1(), and the others all look simple
>>> enough, provided there's somewhere to get lots of random bits.
>>>
>>> On Linux, it seems like we could get those bits from /dev/urandom,
>>> though I'm not sure how efficient that would be for the case where
>>> many UUIDs are being generated at once.  But that wouldn't be very
>>> portable.  It's tempting to think that we'd need a PRNG that generates
>>> wider values, for which we might find other application also.  But I'm
>>> not volunteering to be the one to create such a thing.
>>
>> OpenSSL provides rand_bytes() which generates random bytes of any length.
>> It uses /dev/urandom or /dev/random on UNIX/Linux and Crypto API of
>> Microsoft on Windows.
>
> What about using a cipher here as the PRNG? It seems like using openssl
> rand_bytes() to seed aes in ctr would work ok without starving the system of
> entropy when making a lot of uuids.

There are two good reasons for us NOT to rely on OpenSSL:

1. Not all builds are done --with-openssl.

2. OpenSSL is rickety and poorly-documented, and depending on it for
more things will surely increase the pain we suffer on some great
future day when we begin supporting some other SSL library alongside
it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Oct 31, 2013 at 01:59:04PM -0400, Robert Haas wrote:
> On Thu, Oct 31, 2013 at 1:02 PM, Garick Hamlin <ghamlin@isc.upenn.edu> wrote:
> > On Thu, Oct 31, 2013 at 09:54:14PM +0900, MauMau wrote:
> >> From: "Robert Haas" <robertmhaas@gmail.com>
> >>> ISTM that the biggest problem is that we don't have a random number
> >>> generator which generates enough bits of randomness to implement
> >>> uuid_generate_v3.  I think relatively few people would cry if we
> >>> didn't support uuid_generate_v1(), and the others all look simple
> >>> enough, provided there's somewhere to get lots of random bits.
> >>>
> >>> On Linux, it seems like we could get those bits from /dev/urandom,
> >>> though I'm not sure how efficient that would be for the case where
> >>> many UUIDs are being generated at once.  But that wouldn't be very
> >>> portable.  It's tempting to think that we'd need a PRNG that generates
> >>> wider values, for which we might find other application also.  But I'm
> >>> not volunteering to be the one to create such a thing.
> >>
> >> OpenSSL provides rand_bytes() which generates random bytes of any length.
> >> It uses /dev/urandom or /dev/random on UNIX/Linux and Crypto API of
> >> Microsoft on Windows.
> >
> > What about using a cipher here as the PRNG? It seems like using openssl
> > rand_bytes() to seed aes in ctr would work ok without starving the system of
> > entropy when making a lot of uuids.
> 
> There are two good reasons for us NOT to rely on OpenSSL:

Right, that makes sense.  openssl is a non-starter here.  In which case that
approach is no easier than any other prng.

I think using /dev/urandom directly would be surprising.  At least it would
have probably have taken me a while to figure out what was depleting the
entropy pool here.

Garick

On Thu, Oct 31, 2013 at 2:44 PM, Garick Hamlin <ghamlin@isc.upenn.edu> wrote:
> On Thu, Oct 31, 2013 at 01:59:04PM -0400, Robert Haas wrote:
>> On Thu, Oct 31, 2013 at 1:02 PM, Garick Hamlin <ghamlin@isc.upenn.edu> wrote:
>> > On Thu, Oct 31, 2013 at 09:54:14PM +0900, MauMau wrote:
>> >> From: "Robert Haas" <robertmhaas@gmail.com>
>> >>> ISTM that the biggest problem is that we don't have a random number
>> >>> generator which generates enough bits of randomness to implement
>> >>> uuid_generate_v3.  I think relatively few people would cry if we
>> >>> didn't support uuid_generate_v1(), and the others all look simple
>> >>> enough, provided there's somewhere to get lots of random bits.
>> >>>
>> >>> On Linux, it seems like we could get those bits from /dev/urandom,
>> >>> though I'm not sure how efficient that would be for the case where
>> >>> many UUIDs are being generated at once.  But that wouldn't be very
>> >>> portable.  It's tempting to think that we'd need a PRNG that generates
>> >>> wider values, for which we might find other application also.  But I'm
>> >>> not volunteering to be the one to create such a thing.
>> >>
>> >> OpenSSL provides rand_bytes() which generates random bytes of any length.
>> >> It uses /dev/urandom or /dev/random on UNIX/Linux and Crypto API of
>> >> Microsoft on Windows.
>> >
>> > What about using a cipher here as the PRNG? It seems like using openssl
>> > rand_bytes() to seed aes in ctr would work ok without starving the system of
>> > entropy when making a lot of uuids.
>>
>> There are two good reasons for us NOT to rely on OpenSSL:
>
> Right, that makes sense.  openssl is a non-starter here.  In which case that
> approach is no easier than any other prng.
>
> I think using /dev/urandom directly would be surprising.  At least it would
> have probably have taken me a while to figure out what was depleting the
> entropy pool here.

There's something arguably duplicative about this, but I'd be kind of
inclined to
implement an entropy pool, and enable introducing entropy using whatever
means are wished.

That way, we can "scale" reasonably well for all the cases.

By default, we seed using a timestamp (or similar) and use some stream cipher
that is of suitable portability to "stir the pot."  That's obviously
not perfect, but it
will *work*, and offer reasonable results on systems that don't offer better
facilities.

Having a pool lends itself to "stirring" extra ingredients in with
reasonable ease.

- If you have a better PRNG, you can stir that in.  Perhaps it replaces the default; I'm not sure it's important.
- If you have /dev/random or /dev/urandom, you can stir some of that into  the mix.
- Someone that is feeling paranoid about their PRNG could stir in  data from an entropy key (like
<http://www.entropykey.co.uk/>)

The latter options aren't much use to someone without suitable hardware or
OS support, but they're not imperative.

Doing funky things to the pool seems like an example of something useful to
do using the background worker facility of 9.3+.  And if you have an application
where you're using really a lot of UUIDs, the burden of configuring that may be
reasonable to pay.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Thu, Oct 31, 2013 at 2:44 PM, Garick Hamlin <ghamlin@isc.upenn.edu> wrote:
> I think using /dev/urandom directly would be surprising.  At least it would
> have probably have taken me a while to figure out what was depleting the
> entropy pool here.

Perhaps so; a bigger problem IMHO is that it's not portable.  I think
the only way to solve this problem is to import (or have an option to
link with) a strong, sophisticated PRNG with much larger internal
state than pg_lrand48, which uses precisely 48 bits of internal state.For this kind of thing, I'm fairly sure that we
needsomething with
 
at least 128 bits of internal state (as wide as the random value we
want to generate) and I suspect it might be advantageous to have
something a whole lot wider, maybe a few kB.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

<p dir="ltr">On Thu, Oct 31, 2013 at 3:42 PM, Robert Haas <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> > On Thu, Oct 31, 2013 at 2:44 PM,
GarickHamlin <<a href="mailto:ghamlin@isc.upenn.edu">ghamlin@isc.upenn.edu</a>> wrote:<br /> >> I think
using/dev/urandom directly would be surprising. At least it would<br /> >> have probably have taken me a while to
figureout what was depleting the<br /> >> entropy pool here.<br /> ><br /> > Perhaps so; a bigger problem
IMHOis that it's not portable. I think<br /> > the only way to solve this problem is to import (or have an option
to<br/> > link with) a strong, sophisticated PRNG with much larger internal<br /> > state than pg_lrand48, which
usesprecisely 48 bits of internal state.<br /> > For this kind of thing, I'm fairly sure that we need something
with<br/> > at least 128 bits of internal state (as wide as the random value we<br /> > want to generate) and I
suspectit might be advantageous to have<br /> > something a whole lot wider, maybe a few kB.<p dir="ltr">I mentioned
thenotion of building an entropy pool, into which one might<br /> add various sorts of random inputs, under separate
cover...<pdir="ltr">The last time I had need of a rather non-repeating RNG, I went with<br /> a Fibonacci-based one,
namelyMersenne Twister...<p dir="ltr"><<a
href="http://en.wikipedia.org/wiki/Mersenne_twister">http://en.wikipedia.org/wiki/Mersenne_twister</a>><p
dir="ltr">Thesample has 624 integers (presumably that means 624x32 bits) as<br /> its internal state. Apparently not
terriblysuitable for cryptographic purposes,<br /> but definitely highly non-repetitive, which is what we're notably<br
/>worried about for UUIDs.<br /> -- <br /> When confronted by a difficult problem, solve it by reducing it to the<br />
question,"How would the Lone Ranger handle this?" 

高增琦 wrote:
> : (
> 
> still don't know how to build ossp-uuid on windows with MSVC.
> Saito san's patch doesn't fix all errors during compiling...

I don't understand why you want to build that specific module on Windows.
Doesn't Windows have its own UUID generator that you can access, using a
smaller extension?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services