looking for a globally unique row ID

From: Rafal Pietrak <rafal(at)ztk-rp(dot)eu>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: looking for a globally unique row ID
Date: 2017-09-14 07:45:59
Message-ID: 560176cd-db03-ae60-5718-6878c6f27c7b@ztk-rp.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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).

on the other hand having a dedicated table just for the purpose of
holding that UNIQUE column with all the "client tables" having FK
pointing to it does not guarantee that multiple "client tables" want use
the same ID from that table. ... and I don't know a way to make an FK
"backfireing" from that table into a multitude of client tables.

I thought of using OIDs on tables that need this unique ID ... but
querying the web returns warnings of OID "rollover" problem. As I'm not
quite sure how to manage that, and if (3) is guaranteed (not just being
lucky during couple of tests), I think this is not an option here.

I'd appreciate any hints, as I'm bitting my head against this problem
for a long time, now.

Thenx in advance,

-R

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2017-09-14 07:57:09 Re: looking for a globally unique row ID
Previous Message George Neuner 2017-09-14 06:26:12 Re: equivalent for md5, clobs and varchar2 list