Le 14 septembre 2017 19:11:19 GMT+02:00, Rafal Pietrak <rafal@ztk-rp.eu> a écrit :
>
>
>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.
I wrote something about this some years ago, this might do the trick for you, maybe with some adjustments depending on
yourschema. The main idea should help anyway. See:
http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html
(no, I never wrote the second part about fk :-/)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general