The problem here is that you're trying to make the relational model do something it was exactly designed *not* to do. Rows are supposed to be wholly independent of each other, but in this table, if you update row 200 of 700, you suddenly make 500 rows wrong. The implications of that are really bad. It means whenever you do an INSERT, UPDATE, or DELETE, you need to lock the whole table. And since SELECT statements would be accessing bad data during the table rebuild process, you have to go as far as to lock the whole table from SELECT, too. So you have to do an ACCESS EXCLUSIVE table lock. The linked list approach I mentioned is not that bad. You can easily find the beginning of the list (OUTER JOIN WHERE ParentID IS NULL) and the end of the list (OUTER JOIN WHERE ChildID IS NULL). You can easily INSERT/UPDATE anywhere (insert record, new record becomes parent of parent's old child and child of parent) and DELETE anywhere (parent becomes parent of child, delete record). The only problem is if you need to say "show me the 264th item in the list" because you have to iterate through the list. You could use numeric IDs, I suppose, instead of integers. Then you just pick a number between the two items around it and use that. So if you need to insert an item between 1 and 2, you add in 1.5. If you need one between 1.5 and 2, you pick 1.75, etc. Deletes are transparent. You'll only get into trouble if your values get smaller than 10^-1000, which, of course, they eventually will without reordering things periodically. It circles back to what you're trying to do with this sequence. Why are gaps bad? Why must the database handle order instead of control code or view code? What is the significance of the order to the data model? In any case, I would not use the order key as a primary key. It should be unique, to be sure, but primary keys should be very stable. You may wish to use a serial field as the primary key just for that sake. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: Bob Pawley [mailto:rjpawley@xxxxxxx] Sent: Monday, September 25, 2006 12:00 PM To: Brandon Aiken; pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] serial column Actually, I am not trying to "force keys" nor, I don't beleive, am I trying to force an hierarchal structure within the database. The numbers I want to assign to devices are nothing more than merely another attribute of the device - perhaps akin to a number in a street address. The problem, from my viewpoint, is that this attribute needs to always start at 1 and be sequential without gaps. (I am however, partly relying on an hierarchal order within the database. When I assign numbers to devices, the lowest number is assigned, sequentially, to the device that has the lowest serial ID number. ) Thanks for your comments - everything helps at my stage. Bob Pawley ----- Original Message ----- From: "Brandon Aiken" <BAiken@xxxxxxxxxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> Sent: Monday, September 25, 2006 7:47 AM Subject: Re: [GENERAL] serial column I would tend to agree with Tom. A table is by definition an unordered set of records. Forcing keys to have meaning of this type implies that there is a relationship between each record in the set. That's information you should be storing as part of the record. If order is important, design the database so that it knows that order relationship exists. An ordered list is just a hierarchal database wherein every record has exactly one parent (or none if it's root) and exactly one child (or none if it's end leaf), but the relational model does a rather poor job of handling hierarchal relationships. You might consider the two-way linked list approach. That is, each record knows the item before it and the item after it, like so: TABLE mainTable { id serial PRIMARY KEY, foo text, bar integer, zen numeric } TABLE mainTableRelationships { parentID integer, childID integer, CONSTRAINT "mainTableRelationships_pkey" PRIMARY KEY ("parentID", "childID"), CONSTRAINT "parentID_key" UNIQUE ("parentID"), CONSTRAINT "childID_key" UNIQUE ("childID"), CONSTRAINT "mainTable_parentID_fkey" FOREIGN KEY ("parentID") REFERENCES "mainTable" ("id"), CONSTRAINT "mainTable_childID_fkey" FOREIGN KEY ("childID") REFERENCES "mainTable" ("id") } Of course, there's really little difference between doing things this way and ordering by the SERIAL field and numbering them appropriately on output, except that this above way is hideously more complex. Another option would be to create a temporary table ordered correctly, truncate the existing table, delete the sequence (or change the default on the primary key), copy the data back, and then re-create the sequence (or change default back to nextval) and then set nextval to MAX()+1. This is rather ugly, however, since you're still forcing the database to do relationships it doesn't know about, so you technically violate first normal form by having a multi-valued field (it identifies uniqueness and order). -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Sunday, September 24, 2006 7:31 PM To: Bob Pawley Cc: Ragnar; Postgresql Subject: Re: [GENERAL] serial column Bob Pawley <rjpawley@xxxxxxx> writes: > I am using the numbers to identify devices. > If a device is deleted or replaced with another type of device I want the > numbering to still be sequential. It sounds to me like you oughtn't be storing these numbers in the database at all. You just want to attach them at display time --- they are certainly utterly meaningless as keys if they can change at any moment. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
