Re: uuid type for postgres

On Tue, Sep 06, 2005 at 05:31:43PM -0400, Greg Stark wrote:
> Just do something like this for every sequence:
> ALTER SEQUENCE foo INCREMENT BY 100
> And then choose a particular initial value for each server.

*shudder*

But you are right. That would work. :-) (I shudder from the maintenance
head-ache - if I re-cloned the test database, and reset the sequence
to the wrong value, I would have overlap)

> I agree with the others that uuid seems to be overused in lots of cases where
> a simple serial would serve just as well.

Yes.

In March I only used serial columns (actually, I originally used the
hacky MySQL auto_increment type before I migrated the database to
PostgreSQL). I found problems under actual usage with regard to
testing, and with exposing the numbers to the users. I started by
introducing the uuid along side the serial column, but shortly after
realized that it was ridiculous. I effectively had two primary keys,
with two unique indexes. Drop the serial column, and I'm left with
one that does everything I want.

I now used mixed serial and uuid. I retained serial columns for space
reasons. Serial makes it more common for some of my table primary key
indexes or even tables themselves (enumerated type tables) to fit in
fewer pages, improving search time. Some tables may cluster rows more
naturally on a serial column (would depend if the uuid type was sorted
by embedded time stamp, or by literal byte array value - memcmp()).

For the objects represented by a collection of rows, that I expose
to external interfaces, however, I only use the uuid.

My point in this long and winded set of paragraphs, is that although I
agree that uuid might be overused in some situations, I think people
may be currently underusing it in others. They're both ways of
generating unique identifiers. Depending on the context, one will be
better than the other. They may both work for most circumstances.

> However I don't see why a good uuid type is any less of a valid
> addition to the set of data types than any of the others. inet,
> macaddr, all the geometric types, for example.

I've never used geometric types, and don't see myself doing so any
time soon. :-)

> Given the 3-phase commit work going into 8.2 (8.1?) it seems like a pretty
> fundamental component of that whole ball of wax. A note in the documentation
> that it's designed for use as part of a multiple-database system like that
> might be helpful.

Yep.

Cheers,
mark

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/