Re: [GENERAL] looking for a globally unique row ID - Mailing list pgsql-general

W dniu 14.09.2017 o 15:54, Merlin Moncure pisze:
> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak <rafal@ztk-rp.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.

That's the goal.

Multitude of tablas using a single sequence does not give that guarantee.

As I've said, a solution closest to my target is a separate table with
just one column of that "global primary key", which get inserted/updated
within trigger on insert/update of the "client tables" ... only I'm not
so sure how to "cleanly" manage multitude of tables using the same key
of that "global table of keys"... that is its "back references".

So I'm stuck with seriously incomplete solution.

that's why I have an impression, that I'm going into entirely wrong
direction here.

> this over GUID.   GUIDs are twice the size, and randomized which has
> some unpleasant performance characteristics.   The advantage they
> bring is being able to set up multiple generation points (say, across
> multiple database servers) without fear of conflict.
> 
> Sequence generation is very fast so there is no performance related
> argument not to use the approach.
> 
> merlin
> 
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general