On Mon, 2006-12-04 at 17:53, Raymond O'Donnell wrote: > On 4 Dec 2006 at 15:40, Bob Pawley wrote: > > > When a row is deleted the serial number and oid are also deleted. The > > sequence then has gaps which are inadmissible. > > This is an issue which has come up at various times on this list in > the past - it may be worth having a look through the archives. Here's > one recent thread: > > http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php Yeah. The requirement for no gaps is a real no starter for any kind of large data set. Imagine modelling all the people in the US. 350 million entries. No big deal. I can create that in minutes. You each one give a number. Again, no big deal. A couple of minutes. For everyone that dies, you remove the name. No big deal. a couple seconds to run a delete. For every one that is born, you add it to the list, giving it the next number. again, no big deal. My workstation could probably handle the load. Now, turn that on its head. Every time you delete someone, you have to renumber the data set, and for everyone added you have to make sure there are no gaps. Suddenly, you've got a problem that could bring even big iron to its knees. All because some buearocrat (sp) hadn't the imagination to think of non-sequential numbering systems. While there are occasional systems where it is reasonable to actually have no sequential gaps, most of the time the only justification is "I don't like them." If that is the case, you should rethink your design. If you're stuck with them because of some idiotic rule from on high, then at least abstract the numbers to some degree to improve performance and keep you from having to update about half of an entire table several times a minute.
