Re: ALTER TABLE modifications

Peter Eisentraut kirjutas R, 14.11.2003 kell 18:51:
> Tom Lane writes:
>
> > I believe the consensus was that automating what you could do by hand
> > is still a step forward.
>
> I don't recall that, but if so, I would like to revisit that consensus.
>
> AFAICT, this patch does not buy us anything at all. It's just a different
> spelling of existing functionality. We have never done that before.

what about DROP COLUMN - this is also just a different spelling for

SELECT INTO, migrate all constraints, DROP OLD TABLE, RENAME.

> It just makes the system harder to maintain and use. All commands should be
> reasonably independent, or at least add some functionality of their own.
>
> > It clearly would be better if we could relabel the logical column
> > position after finishing the whole process, but I agree with Rod that
> > that is an independent patch. Combining them into one mega-patch
> > doesn't sound like good engineering.
>
> Good engineering would be if the logical column number patch comes first.

Agreed, it would be nice.

> We cannot possibly leave this patch as is. People expect in-place column
> changes.

Does SQL spec even require that SELECT * always returns columns in the
same order ? I don't think that relational model assigns any 'order' to
columns.

BTW, SELECT * is just a different spelling of existing functionality ;)

> Things will break left and right, users will complain all over
> the place if we offer a way to change a column, but yeah, by the way it
> changes the structure of the table as well.

ALTER TABLE ADD/DROP COLUMN would also break SELECT *.

as would type change in many cases (query expects int but gets string)

> We've had these kinds of good
> idea/right direction/better than nothing approaches in areas like DROP
> COLUMN and CLUSTER already, and they were no good. Except in this case,
> "better than nothing" doesn't even apply, because there is already
> something.

We have always had DUMP -> sed -> LOAD too.

This patch is *MUCH* better than nothing for performance. Instead of
being N times SEQSCAN (+ intervening VACUUM FULLs to reclaim space), it
can do it all in one pass. If that pass takes 1.5 hours, we have a very
big win here.

-----------------
Hannu