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:

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

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

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

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

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

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.

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

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

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

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