Re: looking for a globally unique row ID

On Thu, Sep 14, 2017 at 12:11 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
> W dniu 14.09.2017 o 15:54, Merlin Moncure pisze:
>> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>>> Hello everybody,
>>>
>>> Can anybody help me find a way to implement an ID which:
>>>
>>> 1. guarantees being unique across multiple tables.
>>>
>>> 2. guarantees its uniqueness not only during INSERT, but also during the
>>> lifetime of the database/application (e.i. during future UPDATES).
>>>
>>> 3. guarantees persistence of value across database backup/restore/upgrade.
>>>
>>> an obvious candidate - a single SERIAL() (same serial) used in every
>>> table that needs that ID does not guarantee (2).
>>
>> A shared sequence meets all of those requirements. I tend to prefer
>
> Not really.
>
> 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.

hm, I don't understand you. Are you trying to defend against forged
or erroneous data? With both sequences and GUIDs, the assumption is
that the application will use the generated id and send it back to the
database. With sequences, the database *guarantees* that the
identifier is unique with no exceptions; there never will be a unique
value. Can you give a hypothetical example of how you think they
wouldn't work?

merlin