Обсуждение: Altering a table - positioning new columns

In MySQL, when adding a new column to a table, you could
position it relative to another column already in the DB.  IOW,
you could do something like this:

ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.

Is something like this possible in PG?  Or are _all_ new columns
added to the end of the column list?
I've searched through the docs and couldn't find that this was
possible and some tests have come up with errors.  That said,
I'm still new enough to where I can't be sure I'm doing it the right
way for PG and am hoping one of you guys can give me a
definitive yes or no.

thnx,
Chris

On Mon, 20 Jan 2003 07:24:57 -0600, "Chris Boget" <chris@wild.net>
wrote:
>give me a
>definitive yes or no.

No.  Sorry.  If column order is important, you have to create a new
table and copy over the data, then drop the old table and rename the
new one.  Of course this has its drawbacks, if there are triggers,
foreign keys, ...

Servus
 Manfred

> No.  Sorry.

*sigh*  That kind of bites.
I'm curious how difficult this would be to implement...

>  If column order is important, you have to create a new table and copy
> over the data, then drop the old table and rename the new one.

Yeah, that's what I've been doing all weekend.

> Of course this has its drawbacks, if there are triggers, foreign keys, ...

Yeah, these are pretty serious drawbacks.  It took me 30+ minutes to do
this for just _one_ table.  I don't like new fields trailing the column list because
I prefer all similar columns to be grouped.  That way if you are viewing the
data through a UI, it's easier to see/read.
But I guess unless I want to spend hours adding a few columns to some
tables, I'm just going to have to learn to live with it... :|

Chris

"Chris Boget" <chris@wild.net> writes:
> Is something like this possible in PG?  Or are _all_ new columns
> added to the end of the column list?

New columns always go at the end of the list.

            regards, tom lane

On Mon, 20 Jan 2003, Chris Boget wrote:

> > Of course this has its drawbacks, if there are triggers, foreign keys, ...
>
> Yeah, these are pretty serious drawbacks.  It took me 30+ minutes to do
> this for just _one_ table.  I don't like new fields trailing the column list because
> I prefer all similar columns to be grouped.  That way if you are viewing the
> data through a UI, it's easier to see/read.
> But I guess unless I want to spend hours adding a few columns to some
> tables, I'm just going to have to learn to live with it... :|
>

Of course that is purely presentation of the data, nothing to do with integrity
of the data, therefore just as the order of rows returned from a query are
undefined, unless explicitly ordered, so are the columns.

MySQL might be seen as having a good feature if it can manage the column
ordering bit but imo it's fud, detracting from the real job of a DB.


--
Nigel J. Andrews

> Date: Mon, 20 Jan 2003 07:24:57 -0600
> From: "Chris Boget" <chris@wild.net>
>
> In MySQL, when adding a new column to a table, you could
> position it relative to another column already in the DB.  IOW,
> you could do something like this:
>
> ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.
>
> Is something like this possible in PG?  Or are _all_ new columns
> added to the end of the column list?
>
AFAIK there is no concept like "column order of a relation" in the
relational model; thus the above MySQL option should not be supported by
a relational database. What does the SQL standard say about this?

Christoph Dalitz

> > Yeah, these are pretty serious drawbacks.  It took me 30+ minutes to do
> > this for just _one_ table.  I don't like new fields trailing the column list because
> > I prefer all similar columns to be grouped.  That way if you are viewing the
> > data through a UI, it's easier to see/read.
> > But I guess unless I want to spend hours adding a few columns to some
> > tables, I'm just going to have to learn to live with it... :|
> Of course that is purely presentation of the data, nothing to do with integrity
> of the data,

You are absolutely correct.

> therefore just as the order of rows returned from a query are undefined, unless
> explicitly ordered, so are the columns.

Indeed.  "Unless explicitly ordered" is the key phrase.  As you can "explicity order"
the rows in a query, it would be nice if you could "explicity order" the layout of your
table when altered.

> MySQL might be seen as having a good feature if it can manage the column
> ordering bit but imo it's fud, detracting from the real job of a DB.

You are correct.  The job of the DB is to keep/hold/serve data.  However, you can't
overlook the person managing that data.  Especially when it comes to large(ish)
tables.  It make managing the tables/data harder if you have to look all over the
place for the fields in a table.  Unless you create a table to be 100% first time (and
that never happens), you will need to search all over the place.  It is nice if you
can have all your date fields in one area, flag fields in another as opposed to 3
date fields in the middle of the table, 2 towards the end and another at the very
end.
If you (general "you") have added a bunch of fields to a table, go into phpPGAdmin,
pgAdmin II (or some other UI) and look at it.  You'll see what I mean.

Again, this is something I could learn to live with.  But after using mySQL for 4
years and adding tons of fields to various tables to incorporate new functionality,
let me tell you how nice it is to be able to place new fields where you want them
in your table.

Chris

On Mon, 20 Jan 2003 09:28:50 -0600, "Chris Boget" <chris@wild.net>
wrote:
>I'm curious how difficult this would be to implement...

The current implementation has the benefit that when you add a column
existing tuples don't have to be touched.  Each tuple header stores
the number of attributes (natts) at the time of its creation.  If you
query for an attribute with a higher number, you get NULL.  I don't
think this can be changed easily without breaking lots of things.

Mid-2002 there has been rudimentary discussion about metadata
versioning, but it didn't get beyond its brainstorming phase ...

Servus
 Manfred

> >I'm curious how difficult this would be to implement...
> The current implementation has the benefit that when you add a column
> existing tuples don't have to be touched.

Hmm, I was under the (obvious) (mis)understanding that a tuple was a
record.  Is that not the case?  If not, what is it, then?

> Each tuple header stores the number of attributes (natts) at the time of
> its creation.  If you query for an attribute with a higher number, you get
> NULL.  I don't think this can be changed easily without breaking lots of
> things.

How do the new columns fit into the above scheme?

Chris

After:

Create table t ( f1 int, f2 int, f3 int);
Alter table t add f4 int;

If you do not like the default order, you can:

create table tmp as select f1, f4, f2, f3 from t;
drop table t;
alter table tmp rename to t;


JLL


Chris Boget wrote:
>
> > > Yeah, these are pretty serious drawbacks.  It took me 30+ minutes to do
> > > this for just _one_ table.  I don't like new fields trailing the column list because
> > > I prefer all similar columns to be grouped.  That way if you are viewing the
> > > data through a UI, it's easier to see/read.
> > > But I guess unless I want to spend hours adding a few columns to some
> > > tables, I'm just going to have to learn to live with it... :|
> > Of course that is purely presentation of the data, nothing to do with integrity
> > of the data,
>
> You are absolutely correct.
>
> > therefore just as the order of rows returned from a query are undefined, unless
> > explicitly ordered, so are the columns.
>
> Indeed.  "Unless explicitly ordered" is the key phrase.  As you can "explicity order"
> the rows in a query, it would be nice if you could "explicity order" the layout of your
> table when altered.
>
> > MySQL might be seen as having a good feature if it can manage the column
> > ordering bit but imo it's fud, detracting from the real job of a DB.
>
> You are correct.  The job of the DB is to keep/hold/serve data.  However, you can't
> overlook the person managing that data.  Especially when it comes to large(ish)
> tables.  It make managing the tables/data harder if you have to look all over the
> place for the fields in a table.  Unless you create a table to be 100% first time (and
> that never happens), you will need to search all over the place.  It is nice if you
> can have all your date fields in one area, flag fields in another as opposed to 3
> date fields in the middle of the table, 2 towards the end and another at the very
> end.
> If you (general "you") have added a bunch of fields to a table, go into phpPGAdmin,
> pgAdmin II (or some other UI) and look at it.  You'll see what I mean.
>
> Again, this is something I could learn to live with.  But after using mySQL for 4
> years and adding tons of fields to various tables to incorporate new functionality,
> let me tell you how nice it is to be able to place new fields where you want them
> in your table.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

I think this is a question of which functionality goes into the actual
database engine, and which functionality is implemented by a front-end tool
like pgAdmin. Obviously, it's possible for the back-end database to handle
this, as MySQL has. This would be through non-standard extensions to SQL.
Another option would be to let the front-end tool let you specify how you
want to see your columns, and remember what order you like to see them in.
This would have the potential advantage of allowing different users to see
the columns in different orders, according to the column order and
groupings that make the most sense to the individual users. Since this is a
feature that's primarily useful for database administration, it might make
more sense to actually put it into the database administration tools.

Wes Sheldahl



"Chris Boget" <chris@wild.net>@postgresql.org on 01/20/2003 10:51:32 AM

Sent by:    pgsql-general-owner@postgresql.org


To:    "Nigel J. Andrews" <nandrews@investsystems.co.uk>
cc:    "Manfred Koizar" <mkoi-pg@aon.at>, "PGSql General"
       <pgsql-general@postgresql.org>
Subject:    Re: [GENERAL] Altering a table - positioning new columns


(snip)
> therefore just as the order of rows returned from a query are undefined,
unless
> explicitly ordered, so are the columns.

Indeed.  "Unless explicitly ordered" is the key phrase.  As you can
"explicity order"
the rows in a query, it would be nice if you could "explicity order" the
layout of your
table when altered.

> MySQL might be seen as having a good feature if it can manage the column
> ordering bit but imo it's fud, detracting from the real job of a DB.

You are correct.  The job of the DB is to keep/hold/serve data.  However,
you can't
overlook the person managing that data.  Especially when it comes to
large(ish)
tables.  It make managing the tables/data harder if you have to look all
over the
place for the fields in a table.  Unless you create a table to be 100%
first time (and
that never happens), you will need to search all over the place.  It is
nice if you
can have all your date fields in one area, flag fields in another as
opposed to 3
date fields in the middle of the table, 2 towards the end and another at
the very
end.
If you (general "you") have added a bunch of fields to a table, go into
phpPGAdmin,
pgAdmin II (or some other UI) and look at it.  You'll see what I mean.

Again, this is something I could learn to live with.  But after using mySQL
for 4
years and adding tons of fields to various tables to incorporate new
functionality,
let me tell you how nice it is to be able to place new fields where you
want them
in your table.

Chris


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
> AFAIK there is no concept like "column order of a relation" in the
> relational model; thus the above MySQL option should not be supported by
> a relational database. What does the SQL standard say about this?

You would think so, but SQL92 does say

            b) ... the <select list> "*" is equivalent to a <value
              expression> sequence in which each <value expression> is a
              <column reference> that references a column of T and each
              column of T is referenced exactly once. The columns are ref-
              erenced in the ascending sequence of their ordinal position
              within T.

(I didn't bother to look it up, but I would imagine there's something
similar defining the behavior of INSERT without a column name list,
which is the only other place that column order matters in SQL, AFAIR.)

ALTER ADD COLUMN says

         4) In all other respects, the specification of a <column defi-
            nition> in an <alter table statement> has the same effect as
            specification of the <column definition> in the <table defi-
            nition> for T would have had. In particular, the degree of T
            is increased by 1 and the ordinal position of that column is
            equal to the new degree of T as specified in the General Rules
            of Subclause 11.4, "<column definition>".

and ALTER DROP COLUMN says

         6) The degree of T is reduced by 1. The ordinal position of all
            columns having an ordinal position greater than the ordinal
            position of C is reduced by 1.

So the Postgres behavior is per spec.  Whether MySQL's extension is
worth the (nontrivial) trouble it'd be to implement is in the eye of
the beholder.

            regards, tom lane

On Mon, 20 Jan 2003 10:15:29 -0600, "Chris Boget" <chris@wild.net>
wrote:
>Hmm, I was under the (obvious) (mis)understanding that a tuple was a
>record.  Is that not the case?  If not, what is it, then?

There may be subtle differences, but for the sake of this conversation
tuple, row, and record mean more or less the same.

>> Each tuple header stores the number of attributes (natts) at the time of
>> its creation.  If you query for an attribute with a higher number, you get
>> NULL.  I don't think this can be changed easily without breaking lots of
>> things.
>
>How do the new columns fit into the above scheme?

Oh, and attribute = column.

CREATE TABLE t (c1 int, c2 int);
INSERT INTO t VALUES (1, 1);
INSERT INTO t VALUES (2, 2);

-- In reality SELECT natts doesn't work
SELECT natts,* FROM t;
natts | c1 | c2
------+----+----
    2 |  1 |  1
    2 |  2 |  2

ALTER TABLE t ADD COLUMN c3 int;
-- returns immediately without touching any existing row/tuple/record.
-- You'll love this feature, if you have millions of rows.

INSERT INTO t VALUES (1, 2, 3);
SELECT natts,* FROM t;
natts | c1 | c2 | c3
------+----+----+----
    2 |  1 |  1 |
    2 |  2 |  2 |
    3 |  1 |  2 |  3

On the other hand, ALTER TABLE t ADD COLUMN c3 int AFTER c1;
would require Postgres to convert existing tuples:
natts | c1 | c3 | c2
------+----+----+----
    2 |  1 |    |  1
    2 |  2 |    |  2

Servus
 Manfred

Chris,

See comments inline below,

>>No.  Sorry.
>
>
> *sigh*  That kind of bites.
> I'm curious how difficult this would be to implement...
>
>
>> If column order is important, you have to create a new table and copy
>>over the data, then drop the old table and rename the new one.
>

Sorry to jump in the middle as I may have lost a bit of what you are
trying to accomplish, but if you are merely worried about how the
columns are grouped visually you could set up views and have them appear
in any order you want.  You can go as far as making the view updatable
as well, but that would really be no different than updating/inserting
into the table.

>
> Yeah, that's what I've been doing all weekend.
>
>
>>Of course this has its drawbacks, if there are triggers, foreign keys, ...
>
>
> Yeah, these are pretty serious drawbacks.  It took me 30+ minutes to do
> this for just _one_ table.  I don't like new fields trailing the column list because
> I prefer all similar columns to be grouped.  That way if you are viewing the
> data through a UI, it's easier to see/read.
> But I guess unless I want to spend hours adding a few columns to some
> tables, I'm just going to have to learn to live with it... :|
>

I use to get hung up on the table layout, but now I do my best to group
things as well as I can at the time it is created, and if more columns
are needed in the future, I either recreate the entire structure or live
with the "out of order" columns.  In the end when you are inserting with
the field/value list it doesn't matter anyways.

So, views can be your friend when you require a certain "view" of your
data ;)

Tim

> >Hmm, I was under the (obvious) (mis)understanding that a tuple was a
> >record.  Is that not the case?  If not, what is it, then?
> There may be subtle differences, but for the sake of this conversation
> tuple, row, and record mean more or less the same.

Ok.  What is the subtle difference in the grand scale of things?

> >> Each tuple header stores the number of attributes (natts) at the time of
> >> its creation.  If you query for an attribute with a higher number, you get
> >> NULL.  I don't think this can be changed easily without breaking lots of
> >> things.
> >How do the new columns fit into the above scheme?
> Oh, and attribute = column.

Ok

> ALTER TABLE t ADD COLUMN c3 int;
> -- returns immediately without touching any existing row/tuple/record.
> -- You'll love this feature, if you have millions of rows.

So I see.

> INSERT INTO t VALUES (1, 2, 3);
> SELECT natts,* FROM t;
> natts | c1 | c2 | c3
> ------+----+----+----
>     2 |  1 |  1 |
>     2 |  2 |  2 |
>     3 |  1 |  2 |  3

This was an excellent illustration/example, thank you.  However, this brings
up one thing.  Isn't PG going to have to touch all the existing records in one
way or another at some point in time?  What if you do a SELECT c3 from
one of the records above that doesn't have a value?  If the attributes are set
for each tuple/record at creation, wouldn't you get an error saying that there
is no such attribute/column?  When in fact there is?  And what happens if
you try to update that record to set a value for that column?  Will it update?
Or will there be an error?  If it will update and/or if you can select the value
for that column and get a NULL/non value (and not get an error), isn't that
existing tuple getting touched?  And if that's the case, what's the difference
between the illustration above and the illustration below?

> On the other hand, ALTER TABLE t ADD COLUMN c3 int AFTER c1;
> would require Postgres to convert existing tuples:
> natts | c1 | c3 | c2
> ------+----+----+----
>     2 |  1 |    |  1
>     2 |  2 |    |  2

Chris

On Mon, 20 Jan 2003 12:15:03 -0600, "Chris Boget" <chris@wild.net>
wrote:
>> There may be subtle differences, but for the sake of this conversation
>> tuple, row, and record mean more or less the same.
>
>Ok.  What is the subtle difference in the grand scale of things?

That's not my realm.  I tend to be sloppy regarding choice of words.
Mostly depends on what crosses my mind first when I ploddingly
translate my thoughts into English ...

>> INSERT INTO t VALUES (1, 2, 3);
>> SELECT natts,* FROM t;
>> natts | c1 | c2 | c3
>> ------+----+----+----
>>     2 |  1 |  1 |
>>     2 |  2 |  2 |
>>     3 |  1 |  2 |  3
>
>This was an excellent illustration/example, thank you.  However, this brings
>up one thing.  Isn't PG going to have to touch all the existing records in one
>way or another at some point in time?

No, if touch = change.

>  What if you do a SELECT c3 from
>one of the records above that doesn't have a value?

You simply get NULL.

>  If the attributes are set

The *number* of attributes ...

>for each tuple/record at creation, wouldn't you get an error saying that there
>is no such attribute/column?

Oh, I think I see what the misunderstanding is.  Column *names* are
not stored per tuple.  If you SELECT c3 FROM t WHERE c1 = 2;
the column name c3 is looked up in the catalogs, we get the
information that it is the 3rd column, then PG fetches the tuple with
c1 = 2 (it's the tuple with natts = 2 and attribute values 2 and 2).
Now it tries to extract the 3rd attribute from this tuple, sees that
there are less than 3 attributes, and therefore returns NULL.

>  When in fact there is?  And what happens if
>you try to update that record to set a value for that column?  Will it update?

Yes, because in Postgres an UPDATE is basically a DELETE of the old
version and an INSERT with the new values.  The new version will have
natts = 3.

Servus
 Manfred

On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote:
> ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.
>
> Is something like this possible in PG?  Or are _all_ new columns
> added to the end of the column list?

This is exactly the reason why you should almost always acces your data
through views. Makes life so much easier.

Michael
--
Michael Meskes
Email: Michael@Fam-Meskes.De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Manfred Koizar <mkoi-pg@aon.at> writes:
> On the other hand, ALTER TABLE t ADD COLUMN c3 int AFTER c1;
> would require Postgres to convert existing tuples:

It could actually be done without that.  If we distinguished logical and
physical column numbers (this'd require a new attlognum column in
pg_attribute), then we could reorganize the apparent column ordering
at will, without touching the contents of any existing tuple.  ADD
COLUMN would still assign N+1 as the physical column number of a new
column, but the logical numbering could be relabeled to insert the
new column anywhere.

We discussed this last summer, IIRC, and decided that we didn't want to
get into it because of the probability of introducing ugly new bugs
as a result of using logical attnum in places where physical attnum
is needed, or vice versa.  Since in simple test cases the numbers would
be the same, such bugs could escape detection for a long time (just long
enough to fatally trash someone's data :-().  But maybe some day we'll
tackle it.

            regards, tom lane

On Mon, Jan 20, 2003 at 08:38:18PM +0100, Michael Meskes wrote:
> On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote:
> > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.
> >
> > Is something like this possible in PG?  Or are _all_ new columns
> > added to the end of the column list?
>
> This is exactly the reason why you should almost always acces your data
> through views. Makes life so much easier.

i've been wondering about this philosophy for a while, now.
maybe we should ALWAYS use views as the API, and never the
underlying tables --

    create table _things (
        stamp  timestamp(0),
        fld    bigint,
        other  varchar(10)
    );

    create function show_fld(bigint)returns text as '
    ...something like make digits into ##-######-### part number
    ' language 'plpgsql';

    create view things as
    select
        show_fld( fld ),
        show_other( other ),
        something_else( yada yada )
    from
        _things
    ;

    create rule things_add as
    on insert to things
    do instead (
        insert into _things (
            stamp,
            fld,
            other
        ) values (
            current_timestamp,
            store_fld( NEW.fld ),
            store_fld( NEW.other )
        );
    );

    create rule things_edit as
    on update to things
    do instead (
    ...
    );

i'm beginning to think that this "always use a view" should be
done for ALL tables, even the lookup/validation tables. is it a
serious performance issue? is there a good reason NOT to do
this?

this would also facilitate changes in the future, i'd think:
relying on views in the application code, we can change the
underlying tables (add some, remove some, alter they way they
interconnect) but the program logic could stay the same.

in some instances. :)

whaddya think?

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

Speaking only for myself, we suffer enough already from ODI dependency
hell, because of the largish number of functions, triggers and views
that are interdependent in one of our systems.

Just having to change the base table schemas slightly leads to a whole
round of drops and re-creates of the above objects. On a heavy
production system, this tends to be a pain.

Perhaps there is a way around this issue that we are not knowing about?

In terms of needing the columns to be in a certain order, while it may
look nice for documentation purposes, in the main scheme of things, we
have gotten used to column displays showing in the order that we made
them. If we have the time at any point, we tend to backup the table,
adjust the schema by hand, and re-import the data once again.

- Ericson Smith
http://www.did-it.com
http://weightlossfriends.com

On Tue, 2003-01-21 at 18:43, will trillich wrote:
> On Mon, Jan 20, 2003 at 08:38:18PM +0100, Michael Meskes wrote:
> > On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote:
> > > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.
> > >
> > > Is something like this possible in PG?  Or are _all_ new columns
> > > added to the end of the column list?
> >
> > This is exactly the reason why you should almost always acces your data
> > through views. Makes life so much easier.
>
> i've been wondering about this philosophy for a while, now.
> maybe we should ALWAYS use views as the API, and never the
> underlying tables --
>
>     create table _things (
>         stamp  timestamp(0),
>         fld    bigint,
>         other  varchar(10)
>     );
>
>     create function show_fld(bigint)returns text as '
>     ...something like make digits into ##-######-### part number
>     ' language 'plpgsql';
>
>     create view things as
>     select
>         show_fld( fld ),
>         show_other( other ),
>         something_else( yada yada )
>     from
>         _things
>     ;
>
>     create rule things_add as
>     on insert to things
>     do instead (
>         insert into _things (
>             stamp,
>             fld,
>             other
>         ) values (
>             current_timestamp,
>             store_fld( NEW.fld ),
>             store_fld( NEW.other )
>         );
>     );
>
>     create rule things_edit as
>     on update to things
>     do instead (
>     ...
>     );
>
> i'm beginning to think that this "always use a view" should be
> done for ALL tables, even the lookup/validation tables. is it a
> serious performance issue? is there a good reason NOT to do
> this?
>
> this would also facilitate changes in the future, i'd think:
> relying on views in the application code, we can change the
> underlying tables (add some, remove some, alter they way they
> interconnect) but the program logic could stay the same.
>
> in some instances. :)
>
> whaddya think?
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will@serensoft.com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html