Thread: PG Admin

Bob Pawley wrote:
> I just installed PostgreSQL 8.1 and PG Admin 1.6.1 .
>
> These versions have a sequential column, that is not part of the
> table, identifying the rows.
>
> Is there any method of accessing those numbers and identifying them
> with elements within the table??

Are you sure it's not just numbering the rows as it displays them?

--
   Richard Huxton
   Archonet Ltd

That's what they are doing.

That is also what I am looking for, if it is accessable. If so, I can use
that information to add a sequential numerical element to my information
that doesn't have the restrictions of a serial column.

Bob
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 9:39 AM
Subject: Re: [GENERAL] PG Admin


> Bob Pawley wrote:
>> I just installed PostgreSQL 8.1 and PG Admin 1.6.1 .
>>
>> These versions have a sequential column, that is not part of the
>> table, identifying the rows.
>>
>> Is there any method of accessing those numbers and identifying them
>> with elements within the table??
>
> Are you sure it's not just numbering the rows as it displays them?
>
> --
>   Richard Huxton
>   Archonet Ltd
>

Bob Pawley wrote:
> That's what they are doing.
>
> That is also what I am looking for, if it is accessable. If so, I can
> use that information to add a sequential numerical element to my
> information that doesn't have the restrictions of a serial column.
>
> Bob
>
Hi Bob,

Well, if you create your tables WITH OIDs then each row has a OID
associated with it.

What exactly are the restrictions of a serial column?  It's just
standard integer value with a default value that calls the nextval function.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

When a row is deleted the serial number and oid are also deleted. The
sequence then has gaps which are inadmissible.

Bob
----- Original Message -----
From: "Tony Caduto" <tony_caduto@amsoftwaredesign.com>
To: "Bob Pawley" <rjpawley@shaw.ca>; <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 10:43 AM
Subject: Re: [GENERAL] PG Admin


> Bob Pawley wrote:
>> That's what they are doing.
>>
>> That is also what I am looking for, if it is accessable. If so, I can use
>> that information to add a sequential numerical element to my information
>> that doesn't have the restrictions of a serial column.
>>
>> Bob
>>
> Hi Bob,
>
> Well, if you create your tables WITH OIDs then each row has a OID
> associated with it.
>
> What exactly are the restrictions of a serial column?  It's just standard
> integer value with a default value that calls the nextval function.
>
> Later,
>
> --
> Tony Caduto
> AM Software Design
> http://www.amsoftwaredesign.com
> Home of PG Lightning Admin for Postgresql
> Your best bet for Postgresql Administration
>
> ---------------------------(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
>

On 4 Dec 2006 at 15:40, Bob Pawley wrote:

> When a row is deleted the serial number and oid are also deleted. The
> sequence then has gaps which are inadmissible.

This is an issue which has come up at various times on this list in
the past - it may be worth having a look through the archives. Here's
one recent thread:

http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php


--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

On Mon, 2006-12-04 at 17:53, Raymond O'Donnell wrote:
> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
> > When a row is deleted the serial number and oid are also deleted. The
> > sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php

Yeah.  The requirement for no gaps is a real no starter for any kind of
large data set.

Imagine modelling all the people in the US.  350 million entries.  No
big deal.  I can create that in minutes.

You each one give a number.  Again, no big deal.  A couple of minutes.

For everyone that dies, you remove the name.  No big deal.  a couple
seconds to run a delete.

For every one that is born, you add it to the list, giving it the next
number.  again, no big deal.  My workstation could probably handle the
load.

Now, turn that on its head.  Every time you delete someone, you have to
renumber the data set, and for everyone added you have to make sure
there are no gaps.

Suddenly, you've got a problem that could bring even big iron to its
knees.  All because some buearocrat (sp) hadn't the imagination to think
of non-sequential numbering systems.

While there are occasional systems where it is reasonable to actually
have no sequential gaps, most of the time the only justification is "I
don't like them."  If that is the case, you should rethink your design.
If you're stuck with them because of some idiotic rule from on high,
then at least abstract the numbers to some degree to improve performance
and keep you from having to update about half of an entire table several
times a minute.

I'm doing something similar - using a control table, dropping and creating
the serial column and updating in a manner that does the job.

It works - barely. I am seeking a more elegent and stable method. Having a
simple update recognizing the row numbers (in version 1.6.1) would be
better - perhaps.

Bob


----- Original Message -----
From: "Raymond O'Donnell" <rod@iol.ie>
To: <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 3:53 PM
Subject: Re: [GENERAL] PG Admin


> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
>> When a row is deleted the serial number and oid are also deleted. The
>> sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php
>
>
> --Ray.
>
>
> ----------------------------------------------------------------------
>
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod@iol.ie
> ----------------------------------------------------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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.

Bob
----- Original Message -----
From: "Scott Marlowe" <smarlowe@g2switchworks.com>
To: "Raymond O'Donnell" <rod@iol.ie>
Cc: "pgsql general" <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 4:09 PM
Subject: Re: [GENERAL] PG Admin


> On Mon, 2006-12-04 at 17:53, Raymond O'Donnell wrote:
>> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>>
>> > When a row is deleted the serial number and oid are also deleted. The
>> > sequence then has gaps which are inadmissible.
>>
>> This is an issue which has come up at various times on this list in
>> the past - it may be worth having a look through the archives. Here's
>> one recent thread:
>>
>> http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php
>
> Yeah.  The requirement for no gaps is a real no starter for any kind of
> large data set.
>
> Imagine modelling all the people in the US.  350 million entries.  No
> big deal.  I can create that in minutes.
>
> You each one give a number.  Again, no big deal.  A couple of minutes.
>
> For everyone that dies, you remove the name.  No big deal.  a couple
> seconds to run a delete.
>
> For every one that is born, you add it to the list, giving it the next
> number.  again, no big deal.  My workstation could probably handle the
> load.
>
> Now, turn that on its head.  Every time you delete someone, you have to
> renumber the data set, and for everyone added you have to make sure
> there are no gaps.
>
> Suddenly, you've got a problem that could bring even big iron to its
> knees.  All because some buearocrat (sp) hadn't the imagination to think
> of non-sequential numbering systems.
>
> While there are occasional systems where it is reasonable to actually
> have no sequential gaps, most of the time the only justification is "I
> don't like them."  If that is the case, you should rethink your design.
> If you're stuck with them because of some idiotic rule from on high,
> then at least abstract the numbers to some degree to improve performance
> and keep you from having to update about half of an entire table several
> times a minute.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>

On Dec 4, 2006, at 3:53 PM, Raymond O'Donnell wrote:

> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
>> When a row is deleted the serial number and oid are also deleted. The
>> sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php

Or, better, http://archives.postgresql.org/pgsql-general/2006-09/
msg00948.php

I'm sure that PG Admin just generates the numbers in the GUI as it
displays them (as they're meaningless as persistent data).

Cheers,
   Steve

Perhaps - but they aren't necessarily meaningless as pure information.

Bob


----- Original Message -----
From: "Steve Atkins" <steve@blighty.com>
To: "PgSQL General" <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 4:33 PM
Subject: Re: [GENERAL] PG Admin


>
> On Dec 4, 2006, at 3:53 PM, Raymond O'Donnell wrote:
>
>> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>>
>>> When a row is deleted the serial number and oid are also deleted. The
>>> sequence then has gaps which are inadmissible.
>>
>> This is an issue which has come up at various times on this list in
>> the past - it may be worth having a look through the archives. Here's
>> one recent thread:
>>
>> http://archives.postgresql.org/pgsql-general/2006-08/msg00535.php
>
> Or, better, http://archives.postgresql.org/pgsql-general/2006-09/
> msg00948.php
>
> I'm sure that PG Admin just generates the numbers in the GUI as it
> displays them (as they're meaningless as persistent data).
>
> Cheers,
>   Steve
>
>
> ---------------------------(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
>

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

Is it a bill of material line number, an ISA instrument number, or a JIC component tag number?

Regards,

Richard Broersma Jr.

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.

On Monday 04 December 2006 04:17 pm, 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.
>
> Bob
I am trying to figure how you keep track of the physical devices. Do they get
renumbered also?

--
Adrian Klaver
aklaver@comcast.net

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@seaworthysys.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "pgsql general" <pgsql-general@postgresql.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.
>
>

>
> ----- Original Message -----
> From: "Berend Tober" <btober@seaworthysys.com>
> To: "Bob Pawley" <rjpawley@shaw.ca>
> Cc: "pgsql general" <pgsql-general@postgresql.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
On Monday 04 December 2006 07:39 pm, Bob Pawley wrote:
> 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
>
I see now. My thought would to hold the device numbers in a regular integer
column. Have an AFTER INSERT/UPDATE/DELETE trigger that does a count on the
table and renumbers all  the rows in the id column. This is the brute force
method. The alternative would be to search for the gaps and renumber from the
first gap up.


--
Adrian Klaver
aklaver@comcast.net

This is basically what I have done. However it is not particularly stable
and is inelegant.

The serial number is  close to what I need except it becomes tied to the
information.

The row numbering on the PG Admin version 1.6.1 performs the same operation
that I am looking for. Is there some way of using that? For instance the
devices would be numbered coresponding to the row number that is already
being generated.

Bob
----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Berend Tober"
<btober@seaworthysys.com>
Sent: Monday, December 04, 2006 7:58 PM
Subject: Re: [GENERAL] PG Admin


> >
>> ----- Original Message -----
>> From: "Berend Tober" <btober@seaworthysys.com>
>> To: "Bob Pawley" <rjpawley@shaw.ca>
>> Cc: "pgsql general" <pgsql-general@postgresql.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
> On Monday 04 December 2006 07:39 pm, Bob Pawley wrote:
>> 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
>>
> I see now. My thought would to hold the device numbers in a regular
> integer
> column. Have an AFTER INSERT/UPDATE/DELETE trigger that does a count on
> the
> table and renumbers all  the rows in the id column. This is the brute
> force
> method. The alternative would be to search for the gaps and renumber from
> the
> first gap up.
>
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

The physical devices don't get numbered until the design is established and
stable. This is known as the construction stage.

Bob


----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>; "Scott Marlowe"
<smarlowe@g2switchworks.com>; "Raymond O'Donnell" <rod@iol.ie>
Sent: Monday, December 04, 2006 7:43 PM
Subject: Re: [GENERAL] PG Admin


> On Monday 04 December 2006 04:17 pm, 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.
>>
>> Bob
> I am trying to figure how you keep track of the physical devices. Do they
> get
> renumbered also?
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>

On Dec 5, 2006, at 13:08 , Bob Pawley wrote:

> The physical devices don't get numbered until the design is
> established and stable. This is known as the construction stage.

I guess I would set up a couple of tables to track this ordering
independently of the devices themselves. Rough schema:

create table devices
(
     device_id serial primary key
     device_name text not null unique
);

create table plans
(
     plan_id serial primary key
     , plan_name text not null unique
);

create table plan_devices
(
     plan_id integer not null
         references plans
     , device_id integer not null
         references devices
     , device_order serial not null
     , unique (plan_id, device_id)
     , unique (plan_id, device_order)
);

This idea is based around the idea that every time you make a change
to the plan, it's in essence a new plan. You insert a new plan in
plans, reset the plan_devices_device_order_seq (created by the
device_order serial column), and insert the devices for the new plan
into plan_devices in the order they should be. Of course, sequences
aren't transaction safe, but unless others are pulling from the
sequence while the new devices are being assigned to the plan, it
should be safe. You can also check the integrity of the device_order
column after the insert to make sure it's gapless.

Michael Glaesemann
grzm seespotcode net

Bob Pawley wrote:
> This is basically what I have done. However it is not particularly
> stable and is inelegant.
>
> The serial number is  close to what I need except it becomes tied to the
> information.
>
> The row numbering on the PG Admin version 1.6.1 performs the same
> operation that I am looking for. Is there some way of using that? For
> instance the devices would be numbered coresponding to the row number
> that is already being generated.

No way at all. They are simply row numbers that are displayed in the
grid row headers that are generated on the fly in the client code.

Regards, Dave

Bob Pawley wrote:
>> I'm sure that PG Admin just generates the numbers in the GUI as it
>> displays them (as they're meaningless as persistent data).

> Perhaps - but they aren't necessarily meaningless as pure information.

Can't you just do the same - generate the numbers within your
application - and only store them once this "construction phase" ends?

It seems weird to keep some arbitraty numbering scheme in the database.

What does it signify in that phase? Does order matter at all - or is
that what it's supposed to be?

You could also use a mixed solution:
- use a sequence during the construction phase to order by,
- generate the "real" numbering in your client application based on that
order,
- store the generated numbers instead of the sequence values once the
construction phase is finished.

PS. please don't top post, I had to reconstruct your message to let it
make sense.
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Perhaps I can - it will be learning curve for me. However, the development
would be so much easier to apply if it were available in PostgreSQL in a
form similar to generating a serial column.

Bob

----- Original Message -----
From: "Alban Hertroys" <alban@magproductions.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Steve Atkins" <steve@blighty.com>; "PgSQL General"
<pgsql-general@postgresql.org>
Sent: Tuesday, December 05, 2006 1:31 AM
Subject: Re: [GENERAL] PG Admin


> Bob Pawley wrote:
>>> I'm sure that PG Admin just generates the numbers in the GUI as it
>>> displays them (as they're meaningless as persistent data).
>
>> Perhaps - but they aren't necessarily meaningless as pure information.
>
> Can't you just do the same - generate the numbers within your
> application - and only store them once this "construction phase" ends?
>
> It seems weird to keep some arbitraty numbering scheme in the database.
>
> What does it signify in that phase? Does order matter at all - or is
> that what it's supposed to be?
>
> You could also use a mixed solution:
> - use a sequence during the construction phase to order by,
> - generate the "real" numbering in your client application based on that
> order,
> - store the generated numbers instead of the sequence values once the
> construction phase is finished.
>
> PS. please don't top post, I had to reconstruct your message to let it
> make sense.
> --
> Alban Hertroys
> alban@magproductions.nl
>
> magproductions b.v.
>
> T: ++31(0)534346874
> F: ++31(0)534346876
> M:
> I: www.magproductions.nl
> A: Postbus 416
>   7500 AK Enschede
>
> // Integrate Your World //
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>

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.

How many control devices are there, and how often do deletes happen?  If
there are only 30 devices, and deletes only happen on the order of one
per week, then you could simply run a delete trigger to renumber them.

--
Guy Rouillier

On Tue, Dec 05, 2006 at 07:55:35AM -0800, Bob Pawley wrote:
> Perhaps I can - it will be learning curve for me. However, the development
> would be so much easier to apply if it were available in PostgreSQL in a
> form similar to generating a serial column.

Your assertion that it would be easy is incorrect. If what you were
asking was easy, it would have been done long ago... Sequences without
holes is nowhere near as trivial as people think it is.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

> What exactly are the restrictions of a serial column?  It's just standard
> integer value with a default value that calls the nextval function.

The deletion process produces gaps and/or voids. The only way to resurect
the gapless sequence, starting at 1, is to drop and re-create the table or
serial column.

Dropping and recreating does the job but it can be unstable and requires
either on or two extra tables for each sequence you wish to generate.

Bob



----- Original Message -----
From: "Tony Caduto" <tony_caduto@amsoftwaredesign.com>
To: "Bob Pawley" <rjpawley@shaw.ca>; <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 10:43 AM
Subject: Re: [GENERAL] PG Admin


> Bob Pawley wrote:
>> That's what they are doing.
>>
>> That is also what I am looking for, if it is accessable. If so, I can use
>> that information to add a sequential numerical element to my information
>> that doesn't have the restrictions of a serial column.
>>
>> Bob
>>
> Hi Bob,
>
> Well, if you create your tables WITH OIDs then each row has a OID
> associated with it.
>
> What exactly are the restrictions of a serial column?  It's just standard
> integer value with a default value that calls the nextval function.
>
> Later,
>
> --
> Tony Caduto
> AM Software Design
> http://www.amsoftwaredesign.com
> Home of PG Lightning Admin for Postgresql
> Your best bet for Postgresql Administration
>

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@seaworthysys.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "pgsql general" <pgsql-general@postgresql.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

On 12/4/06, Bob Pawley <rjpawley@shaw.ca> 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.

if that's the case then you need to simply renumber the tables after
deletion.  serial column is ok. method is after record deletion:

1. acquire lock to prevent race (use advisory lock or some other lock,
table for example)
2. reset sequence to initial state (read setval in docs)
3. update foo set col=nextval('s')
4. release lock

this may be done on trigger if necesary. if using advisory lock, make
sure to catch sql exception and release lock just in case.

records will be renumberd and sequence will be pointed at next
allocation slot.  if your design can accomidate short term gaps in the
sequence, simply keep a freelist table maintained by a trigger upon
record deletions and check that first before grabbing sequence.

also, do not even cotemplate using this column as primary key (use
unique constraint).

merlin