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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general