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.