Re: looking for a globally unique row ID

On 15/09/17 06:15, Kenneth Marshall wrote:
> On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote:
>> As I said, I'm not looking for performance or "fair probability" of
>> planetary-wide uniqueness.
>>
>> My main objective is the "guarantee". Which I've tried to indicate
>> referring to "future UPDATEs".
>>
>> What I mean here is functionality similar to "primary key", or "unique
>> constraint". Whenever somebody (application, like faulty application
>> IMPORTANT!) tries to INSERT or UPDATE a not unique value there (which in
>> fact could possibly be generated earlier by UUID algorithms, or even a
>> sequence), if that value is among table that uses that (misterious)
>> "global primary key"; that application just fails the transaction like
>> any other "not unique" constraint failing.
>>
>> That's the goal.
>>
>> that's why I have an impression, that I'm going into entirely wrong
>> direction here.
> Hi Rafal,
>
> How many tables do you need to support a unique key across? My approach
> to problems like this is to to provide constraints that will allow normal
> DB functions to provide these assurances. For example, give each table
> its on serial generator as a primary key, but make the sequences disjoint.
> There are 9,592 prime numbers less than 100,000. Give each table one of
> those as the increment and within that table you will never hit the sequence
> value generated for a second table. This will at least allow you to prevent
> any table from ever using the value for another table. Obviously, this may
> not fit your use case, but it provides another way to attack the problem.
> Good luck.
>
> Regards,
> Ken
>
>
Hmm...

2 4 6 8 10

3 6

5 10

Adding a prime as an increment is not sufficient to guarantee uniqueness!

You have to ensure that the product of the 2 smallest primes you use is
greater than any number you'd need to generate.  With such large primes
you may run out of sequence numbers faster than you would like!

Cheers,
Gavin