Re: PG Admin

The problem here is that you've defined in business logic a relationship
between the records in your table, then *not defined that relationship
in the database*. Now you're complaining that the database doesn't do
what you want. How can it? You're not telling it everything.

Remember, in a relational database:
1. A relation (or table) is a collection of items with the same
properties.
2. The items are not related to each other directly. This is why order
is not important. Relations (tables) are unordered sets, not ordered
lists. Here's the fundamental point: records in a table are, by
default, not related to each other!

What determines the ordering sequence here? I have to think it's based
on time? It can't be arbitrary, because SERIAL is an arbitrary
sequence. If you weren't designing a database, how would you be
assigning and reassigning numbers?

You need a field in each record that *knows* this assigning order. The
database needs to know how to figure out what the order is, since the
actual number you assign to it is exactly related to that order and only
that order. All we know at the moment is that you need a field
"ordinal" such that for every record k, ordinal(k) = ordinal(k-1)+1 (and
with some fixed value for ordinal(0)). What determines the relationship
between k and k-1? Why is k-1 not k-2 or k+4?

Once you do this you simply need to re-sequence this ordinal field on
In/Up/De. You can probably even construct a rather elaborate VIEW to
automatically calculate the ordinals declaratively, or use a separate
table to track the parent-child relationship of each object and use that
for numbering.

Brandon Aiken

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bob Pawley
Sent: Monday, December 04, 2006 10:40 PM
To: Berend Tober
Cc: pgsql general
Subject: Re: [GENERAL] PG Admin

I am talking about designing the control system.

No one makes a perfect design at first go. Devices are deleted and
others
added. Until the end of the design stage the numbers need to be
sequential
with no gaps. After the design the numbers of each device are static and
new
devices are added to the sequence or fill in for abandoned devices - but

that is another, separate problem.

But that is beside the point. What I am looking for is a gapless
sequence
generator which has the ability to justify for deletions as well as
additions.

What I am looking for is a very simple adaptation of the serial
function.
All that I need it to do is to justify for design changes and not care
that
if it is reassinged to a different device. The fact that a particular
device
may, by happenstance, change it's assigned number - once twice or
multiple
times, during the design stage, is of no consequence - as long as the
totallity of numbers assigned are sequential and gapless.

Bob

----- Original Message -----
From: "Berend Tober" <btober(at)seaworthysys(dot)com>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "pgsql general" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, December 04, 2006 7:15 PM
Subject: Re: [GENERAL] PG Admin

> Bob Pawley wrote:
>> Your missing the point.
>>
>> I am creating a design system for industrial control.
>>
>> The control devices need to be numbered. The numbers need to be
>> sequential. If the user deletes a device the numbers need to
regenerate
>> to again become sequential and gapless.
> Could you explain what it is about industrial control that requires
the
> reassignment of numbers? Seems to me to make for confusion because
over
> time, you then have a particular instrument referred to by different
> identifiers. So if you had other data, such as written logs, shop
floor
> design diagrams, or other data not included in the data base, for
example,
> you'ld have the problem of keeping track of which instruments were
really
> being talked about because the names (identifying number, that is)
keep
> changing.
>
>

---------------------------(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