Re: [GENERAL] serial column
Title: RE: [GENERAL] serial column
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:[EMAIL PROTECTED]]
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 [EMAIL PROTECTED]
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
Re: [GENERAL] 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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED]
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
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
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 [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] serial column
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] serial column
I need to develop a serial column that always starts at 1 and is sequential evenafter deletes. Any ideas??? Bob
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
Re: [GENERAL] serial column
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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] serial column
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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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
Re: [GENERAL] serial column
It's the behavior I expect - but the gaps aren't acceptable. Bob - Original Message - From: Gevik Babakhani [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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
Re: [GENERAL] serial column
Do you have a for instance?? Bob - Original Message - From: Ragnar [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] serial column
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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] serial column
Bob Pawley [EMAIL PROTECTED] 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
Re: [GENERAL] serial column
Thanks
I'll give that a try.
Bob
- Original Message -
From: Gevik Babakhani [EMAIL PROTECTED]
To: Bob Pawley [EMAIL PROTECTED]
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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] serial column
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 [EMAIL PROTECTED] To: pgsql-general@postgresql.org Cc: Bob Pawley [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] serial column
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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Ragnar [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Sunday, September 24, 2006 4:30 PM Subject: Re: [GENERAL] serial column Bob Pawley [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] serial column
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 [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Ragnar [EMAIL PROTECTED]; Postgresql pgsql-general@postgresql.org Sent: Sunday, September 24, 2006 4:30 PM Subject: Re: [GENERAL] serial column Bob Pawley [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Serial column has suddenly stopped working
I have been using a serial column in my most important postgres table for a couple of years. But today it has suddenly started assigning zero instead of the next number, which clearly is causing chaos. I have restarted Postgres, but it has not solved the problem. I cannot re-assign the serial number because it is tied to other tables. Anybody had this problem?? I have reached number 14954 which we can't exactly call a high number. The column description is as follows: Name srl type bigint modifiersdefault nextval(booking_srl_seq::text) Any help greatly appreciated. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Serial column has suddenly stopped working
Malcolm Warren wrote:
I have been using a serial column in my most important postgres table
for a couple of years.
But today it has suddenly started assigning zero instead of the next
number, which clearly is causing chaos.
I have restarted Postgres, but it has not solved the problem. I cannot
re-assign the serial number because it is tied to other tables.
Anybody had this problem??
I have reached number 14954 which we can't exactly call a high number.
The column description is as follows:
Name srl
type bigint
modifiersdefault nextval(booking_srl_seq::text)
What happens if you go SELECT nextval('booking_srl_seq') - do you get
the expected number?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Serial column has suddenly stopped working
The answer is 3. Mistake of mine. I put in a field of the same name to
display it, and it was also sending 0 with the statement.
Thank you for helping me get to the bottom of it.
Malcolm Warren
Richard Huxton wrote:
Malcolm Warren wrote:
Thank you for your reply.
Yes, I get the next number and the number goes up one, as it should.
The rest of the table seems to function normally incidentally, just
the serial column gets filled with 0.
By the way my version is 7.3 which I forgot to mention.
Hmm - since you say you've dumped/restored to another machine that
leaves two things I can think of:
1. Schema issue (maybe it's not seeing the sequence).
2. Trigger is altering the value supplied.
3. The application isn't sending the query you think it is.
What happens if you manually:
INSERT INTO my_table (..., srl, ...) VALUES (...,
nextval('booking_srl_seq',...)
If that works, it's not #2, and it's time to look at the application
(and turn statement logging on).
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
