Thread

1. Re: [GENERAL] using ID as a key

   davidb@vectormath.com — 2000-02-05T00:49:14Z

   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@austin.rr.com>
   To: davidb@vectormath.com <davidb@vectormath.com>
   Cc: sheila bel <sheilabel@hotmail.com>; pgsql-general@postgreSQL.org
   <pgsql-general@postgreSQL.org>
   Date: Friday, February 04, 2000 4:59 PM
   Subject: Re: [GENERAL] using ID as a key
   
   
   >davidb@vectormath.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
   >
   >************
   >
   
   
   
   

2. Re: [GENERAL] using ID as a key

   Ed Loehr <eloehr@austin.rr.com> — 2000-02-05T02:41:03Z

   davidb@vectormath.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