Re: [GENERAL] using ID as a key

davidb(at)vectormath(dot)com wrote:
>
> Often break - MS-SQL 6.5 has some equivalent of SERIAL. We used it as
> the primary key on a table written to and read from by several different
> mini-applications (it stored personnel information). Everything would be
> fine
> for months on end. But every now and then the sequencer would get stuck
> trying to insert the same id as the last one that was already in the table.
> We would have to turn the ID insert thingy off, delete the record, and then
> turn it
> back on. This problem would then re-occur 2 or 3 times over the next month,
> and then go away 6 months. We never could determine what caused this
> problem.

Having seen MS-SQL crash and burn for unresolvable reasons recently,
I'm tempted to dismiss this one as an MS-SQL problem rather than a
generalized serial or sequence problem. But I'll store that one away
for future reference...

> Never migrate well - I don't remember. I think one problem was in moving
> between MSAccess autonumber and MS-SQL via StarDesignor.
>
> Bulk copy - This is not really a problem if you remember to disable the
> sequencer before making your copy. However, newcomers (as Ms. Bel claims to
> be) often forget to do this. The result is that the primary keys will
> change, but their foreign references will not and your data gets trashed.

Ok. I appreciate the info. I wonder if postgresql' serial "type" is
susceptible to bulk copy problems...

It may be relevant w/r/t your concerns that the pg serial type doesn't
appear to really be a 1st class pg type. Rather, its really just an
integer (int4?) whose default value is a call to a sequence function
(nextval) that grabs the next value from a sequence object (if you
dump a table created with a serial column, you'll notice there's no
reference to serial). I suspect the sequence only comes into play
when no value is supplied for the primary key column. Seems like the
only room for trouble there is in not specifying the primary key at
all?

For what its worth, my backup restore process for postgres does bulk
copies without my doing anything whatsoever to deal with this issue
(though the process is encountering some other unrelated fatal
errors!).

Cheers,
Ed Loehr