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