Re: column ordering, was Re: [PATCHES] Enums patch v2

Russell Smith wrote:
> Tom Lane wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>
>>> Force references to go through macros which implement the lookup for
>>> the
>>> appropriate type? ie: LOGICAL_COL(table_oid,2) vs.
>>> PHYSICAL_COL(table_oid,1) Perhaps that's too simplistic.
>>>
>>
>> It doesn't really address the question of how you know which one to
>> use at any particular line of code; or even more to the point, what
>> mechanism will warn you if you use the wrong one.
>>
>> My gut feeling about this is that we could probably enforce such a
>> distinction if we were using C++, but while coding in C I have no
>> confidence in it. (And no, that's not a vote to move to C++ ...)
>>
> What about a comprimise...
>
> The 8.1 documentation for ALTER TABLE states the following.
>
> Adding a column with a non-null default or changing the type of an
> existing column will require the entire table to be rewritten. This
> may take a significant amount of time for a large table; and it will
> temporarily require double the disk space.
>
>
> Now, we are rewriting the table from scratch anyway, the on disk
> format is changing. What is stopping us from switching the column
> order at the same time. The only thing I can think is that the
> catalogs will need more work to update them. It's a middle sized
> price to pay for being able to reorder the columns in the table. One
> of the problems I have is wanting to add a column in the middle of the
> table, but FK constraints stop me dropping the table to do the
> reorder. If ALTER TABLE would let me stick it in the middle and
> rewrite the table on disk, I wouldn't care. It's likely that I would
> be rewriting the table anyway. And by specifying AT POSITION, or
> BEFORE/AFTER you know for big tables it's going to take a while.
>

This isn't really a compromise. Remember that this discussion started
with consideration of optimal record layout (minimising space use by
reducing or eliminating alignment padding). The above proposal really
does nothing for that.

cheers

andrew