I agree that they are very handy. They become a major pain in
the butt when you start doing replication between servers.
For instance if you fail over to a standby server and you
forget to update it's sequence first, merging data later
becomes a nightmare. I'd like to have int8 sequences and
basically give each server it's own block of numbers to work
with.
Alex.
On Fri, 2 Mar 2001, Gregory Wood wrote:
> > IMHO, automatically incremented number fields used for primary keys are
> > both a blessing and a curse. It is almost always better to use some
> > other data that *means something* for a primary key. If there's no
> > possible candidate key, *then* maybe an autonumber key is appropriate.
>
> Just wanted to say, I disagree strongly here (also MHO). I see quite a few
> benefits and very few drawbacks to using an auto-incrementing field for a
> primary key. In fact, the only drawback I can think of would be that it
> takes up a little more space per record to add a field used solely to
> uniquely identify that record. I can think of several drawbacks to a
> non-auto-incrementing primary key though:
>
> 1. Less efficient joins. Comparing integers is about as easy as it gets...
> text, char, and varchar require string comparisons, while floating point
> numbers are not good as keys because of rounding errors.
> 2. Discourages value changes. A value that "means something" might need to
> be modified in some manner. Sure you can define foreign keys with CASCADEs,
> but if you are using an auto-increment, you don't need to!
> 3. No value is guaranteed to be unique (well, when doing an INSERT or
> UPDATE... it only gets into the database if it *is* unique) unless all
> queries go through a critical section. To the best of my knowledge, the only
> way to do this inside the database is to use nextval either implicitly or
> explicitly.
>
> The only time I don't use auto-incrementing fields is when I have a
> many-to-many join table with two foreign keys that are both
> auto-incrementing fields, in which case the primary key is a combination of
> those two fields. Other than a bit of extra space, I don't see any reason
> not to.
>
> Greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
