Thread: serial column
I need to develop a serial column that always starts at 1 and is sequential even after deletes.
Any ideas???
Bob
On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:
> I need to develop a serial column that always starts at 1 and is
> sequential even after deletes.
>
> Any ideas???
>
Did you try the:
create table tbl
(
id SERIAL
);
or even with primary key...
create table tbl
(
id SERIAL primary key
);
--
Regards,
Gevik Babakhani
Yes But the only way of insuring that the serial starts at 1 and is sequential is to recreate the table. I've tried creating and dropping the table but this generates other issues which I haven't been able to resolve. Bob ----- Original Message ----- From: "Gevik Babakhani" <pgdev@xs4all.nl> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Sunday, September 24, 2006 2:00 PM Subject: Re: [GENERAL] serial column > On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote: >> I need to develop a serial column that always starts at 1 and is >> sequential even after deletes. >> >> Any ideas??? >> > > Did you try the: > > create table tbl > ( > id SERIAL > ); > > or even with primary key... > > create table tbl > ( > id SERIAL primary key > ); > > > -- > Regards, > Gevik Babakhani > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote: > I need to develop a serial column that always starts at 1 and is > sequential even after deletes. what exactly do you mean? say you have rows where your columns has values 1,2,3 and 4. you now delete the row where the value is 2. what do you want to happen? a) the rows with values 3 and 4 are changed tocontain 2 and 3 ? b) the next 2 values to be inserted to be 2 and then 5 ? c) something else ? gnari
Choice a. 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. Bob ----- Original Message ----- From: "Ragnar" <gnari@hive.is> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Sunday, September 24, 2006 2:24 PM Subject: Re: [GENERAL] serial column > On sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote: >> I need to develop a serial column that always starts at 1 and is >> sequential even after deletes. > > what exactly do you mean? > > say you have rows where your > columns has values 1,2,3 and 4. > > you now delete the row where > the value is 2. > > what do you want to happen? > > a) the rows with values 3 and 4 > are changed tocontain 2 and 3 ? > > b) the next 2 values to be inserted > to be 2 and then 5 ? > > c) something else ? > > > gnari > > > >
The SERIAL is always sequential. SERIAL internally creates a SEQUENCE and *binds* it to your table. even if you delete a record and insert a new one , the sequence will continue to increment. however there will be gaps between the values. Isn't this the behavior you expect? On Sun, 2006-09-24 at 14:19 -0700, Bob Pawley wrote: > Yes > > But the only way of insuring that the serial starts at 1 and is sequential > is to recreate the table. > > I've tried creating and dropping the table but this generates other issues > which I haven't been able to resolve. > > Bob > > ----- Original Message ----- > From: "Gevik Babakhani" <pgdev@xs4all.nl> > To: "Bob Pawley" <rjpawley@shaw.ca> > Cc: "Postgresql" <pgsql-general@postgresql.org> > Sent: Sunday, September 24, 2006 2:00 PM > Subject: Re: [GENERAL] serial column > > > > On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote: > >> I need to develop a serial column that always starts at 1 and is > >> sequential even after deletes. > >> > >> Any ideas??? > >> > > > > Did you try the: > > > > create table tbl > > ( > > id SERIAL > > ); > > > > or even with primary key... > > > > create table tbl > > ( > > id SERIAL primary key > > ); > > > > > > -- > > Regards, > > Gevik Babakhani > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > >
It's the behavior I expect - but the gaps aren't acceptable. Bob ----- Original Message ----- From: "Gevik Babakhani" <pgdev@xs4all.nl> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Sunday, September 24, 2006 2:42 PM Subject: Re: [GENERAL] serial column > The SERIAL is always sequential. SERIAL internally creates a SEQUENCE > and *binds* it to your table. even if you delete a record and insert a > new one , the sequence will continue to increment. however there will be > gaps between the values. > > Isn't this the behavior you expect? > > > On Sun, 2006-09-24 at 14:19 -0700, Bob Pawley wrote: >> Yes >> >> But the only way of insuring that the serial starts at 1 and is >> sequential >> is to recreate the table. >> >> I've tried creating and dropping the table but this generates other >> issues >> which I haven't been able to resolve. >> >> Bob >> >> ----- Original Message ----- >> From: "Gevik Babakhani" <pgdev@xs4all.nl> >> To: "Bob Pawley" <rjpawley@shaw.ca> >> Cc: "Postgresql" <pgsql-general@postgresql.org> >> Sent: Sunday, September 24, 2006 2:00 PM >> Subject: Re: [GENERAL] serial column >> >> >> > On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote: >> >> I need to develop a serial column that always starts at 1 and is >> >> sequential even after deletes. >> >> >> >> Any ideas??? >> >> >> > >> > Did you try the: >> > >> > create table tbl >> > ( >> > id SERIAL >> > ); >> > >> > or even with primary key... >> > >> > create table tbl >> > ( >> > id SERIAL primary key >> > ); >> > >> > >> > -- >> > Regards, >> > Gevik Babakhani >> > >> > >> > >> > >> > ---------------------------(end of >> > broadcast)--------------------------- >> > TIP 3: Have you checked our extensive FAQ? >> > >> > http://www.postgresql.org/docs/faq >> > >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
On sun, 2006-09-24 at 14:29 -0700, Bob Pawley wrote: > Choice a. > > 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. have you tried to implement ths using triggers? gnari
On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote: > It's the behavior I expect - but the gaps aren't acceptable. > > Bob Then using the SERIAL or SEQUENCE won't do you any good. A possible solution for this would be to regenerate the entire column's values every time a record gets deleted starting form 1. but then again this would be very slow if you have a very large table Another solution would be to have two triggers, one for delete and one for insert plus an extra *reserve* table to reserve the deleted value. The delete trigger would save the *deleted* values in the reserve table and when a new record is inserted the insert trigger first would check the reserve table for deleted values (that are stored by the delete trigger) if a value exist then it would use that value or increment that last value. However if you want to use a *no gap* sequence as a primary key, you should be aware that you will destroy the integrity of you data. -- Regards, Gevik Babakhani
Do you have a for instance?? Bob ----- Original Message ----- From: "Ragnar" <gnari@hive.is> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Sunday, September 24, 2006 3:10 PM Subject: Re: [GENERAL] serial column > On sun, 2006-09-24 at 14:29 -0700, Bob Pawley wrote: >> Choice a. >> >> 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. > > have you tried to implement ths using > triggers? > > gnari > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"A possible solution for this would be to regenerate the entire column's values every time a record gets deleted starting form 1. but then again this would be very slow if you have a very large table" I don't anticipate the table to be more than a few hundred rows - certainly fewer than 1,000. Could you point to some documentation for regenerating a column's values other than manual?? Bob ----- Original Message ----- From: "Gevik Babakhani" <pgdev@xs4all.nl> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Sunday, September 24, 2006 3:19 PM Subject: Re: [GENERAL] serial column > On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote: >> It's the behavior I expect - but the gaps aren't acceptable. >> >> Bob > > Then using the SERIAL or SEQUENCE won't do you any good. > > A possible solution for this would be to regenerate the entire column's > values every time a record gets deleted starting form 1. but then again > this would be very slow if you have a very large table > > Another solution would be to have two triggers, one for delete and one > for insert plus an extra *reserve* table to reserve the deleted value. > The delete trigger would save the *deleted* values in the reserve table > and when a new record is inserted the insert trigger first would check > the reserve table for deleted values (that are stored by the delete > trigger) if a value exist then it would use that value or increment that > last value. > > However if you want to use a *no gap* sequence as a primary key, you > should be aware that you will destroy the integrity of you data. > > > > > -- > Regards, > Gevik Babakhani > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
On Sun, 2006-09-24 at 15:29 -0700, Bob Pawley wrote:
> "A possible solution for this would be to regenerate the entire column's
> values every time a record gets deleted starting form 1. but then again
> this would be very slow if you have a very large table"
>
> I don't anticipate the table to be more than a few hundred rows - certainly
> fewer than 1,000.
>
> Could you point to some documentation for regenerating a column's values
> other than manual??
>
> Bob
>
I am afraid there is no built-in way to do that.
perhaps you could create a function that:
step1: creates a sequence (with random name)....
step2: update table set field=netval('random_seq_name');
step3: drop sequence...
--
Regards,
Gevik Babakhani
Bob Pawley <rjpawley@shaw.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
Thanks
I'll give that a try.
Bob
----- Original Message -----
From: "Gevik Babakhani" <pgdev@xs4all.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, September 24, 2006 4:13 PM
Subject: Re: [GENERAL] serial column
> On Sun, 2006-09-24 at 15:29 -0700, Bob Pawley wrote:
>> "A possible solution for this would be to regenerate the entire column's
>> values every time a record gets deleted starting form 1. but then again
>> this would be very slow if you have a very large table"
>>
>> I don't anticipate the table to be more than a few hundred rows -
>> certainly
>> fewer than 1,000.
>>
>> Could you point to some documentation for regenerating a column's values
>> other than manual??
>>
>> Bob
>>
> I am afraid there is no built-in way to do that.
> perhaps you could create a function that:
> step1: creates a sequence (with random name)....
> step2: update table set field=netval('random_seq_name');
> step3: drop sequence...
>
> --
> Regards,
> Gevik Babakhani
>
>
>
>
On Sunday 24 September 2006 02:29 pm, Bob Pawley wrote: > Choice a. > > 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. > > Bob > Do you have some other way of tracking a device? I am just trying to figure out how you know which device number 2 (as an example) you are looking at. I am assuming these devices exist as actual entities. So are these numbers applied to the actual device and if so are you going to be constantly renumbering them? -- Adrian Klaver aklaver@comcast.net
To some degree I don't care about the actual number other than roughly following the device ID. At some point later in the design the numbers will be updated to project numbers and then frozen. Bob ----- Original Message ----- From: "Adrian Klaver" <aklaver@comcast.net> To: <pgsql-general@postgresql.org> Cc: "Bob Pawley" <rjpawley@shaw.ca> Sent: Sunday, September 24, 2006 6:11 PM Subject: Re: [GENERAL] serial column > On Sunday 24 September 2006 02:29 pm, Bob Pawley wrote: >> Choice a. >> >> 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. >> >> Bob >> > Do you have some other way of tracking a device? I am just trying to > figure > out how you know which device number 2 (as an example) you are looking at. > I > am assuming these devices exist as actual entities. So are these numbers > applied to the actual device and if so are you going to be constantly > renumbering them? > > -- > Adrian Klaver > aklaver@comcast.net > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
The numbering system is more complex than just assigning a number. It invloves about thirty procedures which I have put together and find that it works well. I would like to keep the numbering as a database system which will be possible if I can figure out a way of generating sequential numbers without possibility of a gap. Perhaps a manually built table is the answer?? Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Ragnar" <gnari@hive.is>; "Postgresql" <pgsql-general@postgresql.org> Sent: Sunday, September 24, 2006 4:30 PM Subject: Re: [GENERAL] serial column > Bob Pawley <rjpawley@shaw.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 >
You might want to take a look at- http://www.varlena.com/GeneralBits/ The procedure as shown does not account for renumbering after a delete, but it might serve as a starting point. On Sunday 24 September 2006 07:03 pm, Bob Pawley wrote: > The numbering system is more complex than just assigning a number. It > invloves about thirty procedures which I have put together and find that it > works well. > > I would like to keep the numbering as a database system which will be > possible if I can figure out a way of generating sequential numbers without > possibility of a gap. > > Perhaps a manually built table is the answer?? > > Bob > > > ----- Original Message ----- > From: "Tom Lane" <tgl@sss.pgh.pa.us> > To: "Bob Pawley" <rjpawley@shaw.ca> > Cc: "Ragnar" <gnari@hive.is>; "Postgresql" <pgsql-general@postgresql.org> > Sent: Sunday, September 24, 2006 4:30 PM > Subject: Re: [GENERAL] serial column > > > Bob Pawley <rjpawley@shaw.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 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Adrian Klaver aklaver@comcast.net
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@shaw.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
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@winemantech.com>
To: <pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@shaw.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
On Mon, 2006-09-25 at 00:19 +0200, Gevik Babakhani wrote:
> On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote:
> > It's the behavior I expect - but the gaps aren't acceptable.
> >
> > Bob
>
> Then using the SERIAL or SEQUENCE won't do you any good.
>
> A possible solution for this would be to regenerate the entire column's
> values every time a record gets deleted starting form 1. but then again
> this would be very slow if you have a very large table
>
It doesn't have to be slow. It seems to me this is more of a
presentation issue than a data issue. He can just have a serial column,
when you select from that table order by the serial, and then assign the
numbers on the client side.
If the original poster really needs to do it in the database server I
guess he could use a procedural language and create a table function
(i.e. returns setof ...) . There's nothing relational about what he
wants to do, and PostgreSQL has procedural languages to handle
procedural tasks.
Regards,
Jeff Davis
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@shaw.ca]
Sent: Monday, September 25, 2006 12:00 PM
To: Brandon Aiken; pgsql-general@postgresql.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@winemantech.com>
To: <pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@shaw.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
Yes, but if I tear down the house at 245 Main St, we don't renumber 247 Main St and on down the line, do we?
The problem here is that even if you get the sequencing to work, your table is dangerously unstable. If you have 700 rows and you delete row #200, suddenly rows 201-700 are wrong. That means you can't just lock the row you're working on. You'll need to lock the whole table from INSERT/UPDATE/SELECT/DELETE until you've rebuilt the table because the whole thing is suddenly false.
I still believe the best method is going to be the linked list method I suggested, and it's the only one I can think of that meets relational model requrements. The problem with it is that while finding the first item (the one with NULL parent) and last item (the one with NULL child) are easy, and deleting any item is easy (parent becomes parent of child, delete record), and even inserting an item anywhere is easy (insert new record, new record becomes child of parent and parent of parent's child), it's more difficult to ask for item #4 in the order or item #261 in the order. You need an index for your linked list, which I'm guessing is precisely the problem. :) External indices to linked lists is another thing an SQL database doesn't precisely handle very well, since it's all metadata and that adds to physical overhead.
So we return to the question: what purpose does this sequential order serve? Why are gaps bad? What problems are gaps causing? Why does the database need to know the exact order? Why can't your control code be aware of it instead?
You're asking the RDBMS to do something it was exactly designed *not* to do. Rows are supposed to be unrelated objects or entries. A table is *not* a tuple or an array. Ordering them relates them, and makes your data less independent and your database less normalized.
In any case, I strongly recommend against using the ordering field as the primary key simply because you're planning to change them so much. Make it a unique key to enforce the constraint, but primary keys should generally be very stable fields.
Brandon Aiken
-----Original Message-----
From: Bob Pawley [mailto:rjpawley@shaw.ca]
Sent: Mon 9/25/2006 11:59 AM
To: Brandon Aiken; pgsql-general@postgresql.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@winemantech.com>
To: <pgsql-general@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@shaw.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