Thread: 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@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 > >************ >
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