Simon Riggs wrote:
On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
I'd expect the system being able to reoder the columns to the most
efficient order possible (performance-wise and padding-saving-wise),
automatically. When you create a table, sort the columns to the most
efficient order; ALTER TABLE ADD COLUMN just puts the new columns at the
end of the tuple; and anything that requires a rewrite of the table
(ALTER TABLE ... ALTER TYPE for example; would be cool to have CLUSTER
do it as well; and do it on TRUNCATE also) again recomputes the most
That's exactly what I'm proposing. On table creation, the system
chooses an efficient column order for you.
That's fairly straightforward and beneficial. I much prefer Alvaro's
approach rather than the storage position details originally described.
Moreover, you'd need to significantly re-write lots of ALTER TABLE and I
really don't think you want to go there.
There is a problem: If people do a CREATE TABLE and then issue SELECT *
they will find the columns in a different order. That could actually
break some programs, so it isn't acceptable in all cases. e.g. COPY
without a column-list assumes that the incoming data should be assigned
to the table columns in the same order as the incoming data file.
But the display order (and hence the COPY order) of columns would still
be determinted by attnum, not by some attstoragepos, no?
The column reordering would only apply to the physical storage of
columns, not to how it's presented to the user I'd think.
The original idea was to add a third column, attdisplaypos, and let the
user choose the display ordering independent from the unique id
(attnum), which in turn is independent from the storage position.
For simplicity, the OP said he omitted the display-position part here,
because it's really orthogonal to being able to modify the storage position.
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster