Re: looking for a globally unique row ID

On Sep 14, vinny modulated:

> If it is only one database, on one server, then couldn't you just
> use one sequence?
> If oyu prefix the value with some identifier of the current table
> then you cannot get duplicates
> across tables even if you reset the sequence.
>

I didn't follow the whole thread, so I apologize if I'm repeating
earlier suggestions.

We use a shared sequence to issue the new identifiers, and in fact
limited the sequence to 43 bits so they can serialize as JSON numbers
for the benefit of our clients. We disabled wrapping, so it will fail
if we exhaust the range.

If you rapidly churn through identifiers and could envision exhausting
64 bits in your database's lifetime, you should probably just use
UUIDs instead of a sequence. A timestamp-based UUID still has
reasonably sorting and indexing properties.

To "guarantee" uniqueness with a shared sequence or UUID generator,
you can use a trigger to prevent override of identifiers from SQL. As
long as you always use the correct value generator during INSERT and
disallow mutation of identifiers during UPDATE, the rows will not
share identifiers.

Karl