full featured alter table/column ordering - a summary

hi,

so here's a small summery of all the we wrote about changing a column's
definition and/or column-ordering.

column definition change:
- changing a column's defintion is possible in a few situations, but the
main problem is the conversion of the data from one type into another.
- chaning a column's definition is not part of the the SQL-Standard an
therfor is not well defined - even if other DBMS implement such a
feature, they all do it in a different way - so there is no practical
standard
- this feature will not be implemented in near future, and a macro or
something within a tool would also be sufficient (i asked the
pgadmin-team and they promised to think about it - what ever that means)
- still there is some problem:
even if there is a macro within a tool to change a columns definition,
it needs to create a new column which will appear at the end of the
column-list. so one small feature is still needed: user-defined column order

user defined column order:

let's define two things:
- physical column order
the column order with which the column-data is ordered within a table
row when writing it to disk
- logical column order
the column order that postgresql presents to it's clients
it does not need to match te physical order. it is currently equal to
the creation order and also equal the physical order (as far as i know).

WHY?
- defining the column order of a table is part of the creation of the
table. after that, the column order can only be changed by deleting and
creating new columns or by creating new complete new table
- in order to have a full equivalent of changing a column definition,
setting a columns position is required
- many users create a table with a certain column order to help
themselfs or just to keep a certain tidiness, adding a column or
changing it's type would break that order

WHY NOT?
- having a a logical order different from the physical order only
affects "select *" and "select *" should not be used.
- views could be uses to achieve a certain column order
- it's not an important feature and needs a new statement, because users
cannot update the pg_attribute table directly
- any admin-tool could store the its own column ordering in separate tables

WHY ANYWAY?
- having a logical column order is an advantage over other DBMS
- the logical column order should affect "select *" to avoid confusion
("select *" is not the reason the have a logical column order)
- beeing unabled to define the column order makes it look like chaos
after some years. table recreation is needed to compensate that. that's
unacceptable
- the tools will never agree how they store the column order information
if postgresql doesn't provide any way to do that.

SUGGESTIONS:
- add a column to the pg_attribute table to store the logical order
- make "select *" use that new column instead of the old physical order
column
- add a new command to the query interpreter

1. ALTER TABLE <table> ALTER COLUMN <column> POSITION <i>
2. ALTER TABLE <table> POSITIONS <i> <column>,<column>,...

the first statement would be sufficient to do all things, the second is
just a more powerfull shortcut.

ADVANTAGES:
- if there is any advantage in having this or that physical order,
postgresql could optimize the physical order and keep the logical
- MySQL supports inserting a new column at a given position. MySQL
changes the physical order of the columns. Postgresql can do better by
optimizing the physical order and maintaining the logical order separatly.