Re: looking for a globally unique row ID

On Thu, Sep 14, 2017 at 12: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).
>

​I don't see how PostgreSQL can provide an absolute guarantee here. As
others have been saying you can get very close, though. Any of them have
the same basic property - you need to convert client SQL into "requests for
changes" and perform the actual changes within system-managed code while
restricting any possibility for applications to make those changes
themselves. You can get a basic version of this using triggers. Or you
can go all-out and write API functions for every one of these tables and
have the application perform CRUD only via these functions. These
functions then, and not the application, would control key generation.​
You disallow updating IDs and when inserting an ID you insert it into a
"id log" table that has a unique index on it and if that insertion succeeds
you can then associate it with the record being insert into the main
table. You could setup an FK as well but it wouldn't provide the needed
guarantee.

Nothing will stop an administrator, or a user with incorrectly configured
permissions, from bypassing all of that but properly configured application
roles will be unable to do so.

David J.