Re: [GENERAL] using ID as a key

Hello Ed,

None of my examples are Postgres examples, because I foreswore and
forebade the use of serials before I began using Postgres. Maybe they're
just great now (I still won't use them).

Examples hmmm? Let's see . . . such a long time ago . . .

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.

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.

David Boerwinkle
-----Original Message-----
From: Ed Loehr <eloehr(at)austin(dot)rr(dot)com>
To: davidb(at)vectormath(dot)com <davidb(at)vectormath(dot)com>
Cc: sheila bel <sheilabel(at)hotmail(dot)com>; pgsql-general(at)postgreSQL(dot)org
<pgsql-general(at)postgreSQL(dot)org>
Date: Friday, February 04, 2000 4:59 PM
Subject: Re: [GENERAL] using ID as a key

>davidb(at)vectormath(dot)com wrote:
>>
>> Hi Sheila,
>>
>> For general database design considerations (not specific to Postgres) I
>> disagree with the others on the use of serials and sequences. These
>> things never migrate well from platform to platform, they often break,
and
>> dealing with them is a nightmare if you ever have to do any bulk data
>> copying.
>> ID generation ought to be handled programmatically.
>
>I'm ignorant of these problems with serials, but would like to
>consider how to avoid them. Can you give examples of these 3 cases?
>
>Cheers,
>Ed Loehr
>
>************
>