Re: serial column

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(at)shaw(dot)ca]
Sent: Monday, September 25, 2006 12:00 PM
To: Brandon Aiken; pgsql-general(at)postgresql(dot)org
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(at)winemantech(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] 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(at)shaw(dot)ca> 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