Re: full featured alter table?

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> OK, so how do we handle things like converting a varchar to a timestamp
> with time zone?  What if one of the rows has invalid date syntax?  Do we
> convert the column anyway, or throw the whole change out with an error?

I think this particular point is a red herring.  We have data conversion
functions.  My thought would be to apply the same cast function we
would if you were doing an implicit-cast assignment.  That is, the
behavior would be exactly like

    ALTER TABLE tab ADD COLUMN newcol newtype;
    UPDATE tab SET newcol = oldcol;

If there is no cast function, or the cast function burps at any row,
then the command fails and rolls back ... then it's up to you to fix
the data and try again, or use a manual process with some more-complex
conversion function.

However, there are plenty of much-more-subtle semantic issues to worry
about.  Here are a couple:

* Indexes.  How do you translate an index definition involving one
datatype into an index involving another?  There may be no index opclass
at all for the given index type and the new datatype, or there might be
multiple opclasses. Consider for example the recent discussions about
providing reverse-sort opclasses standardly.  I'm not sure how ALTER
TABLE could pick the right opclass if there's more than one choice.

* Constraints.  If the old column has, say, CHECK (foo(x) > 0), how do
we translate this to a new datatype?  Should we assume that if we can
find a function named foo() on the new datatype, it's the right thing
to use?  Seems like a mighty dangerous assumption to me.

Functional indexes present *both* of these sets of problems, and I think
there are probably other issues lurking in the advanced features.

Now a human working through the conversion process by hand probably
wouldn't have a lot of problems deciding what to do, but I'm unconvinced
that an automatic ALTER command would get these things right.

What would make more sense to me than a tightly-wrapped-up ALTER command
at the SQL level is some sort of interactive conversion wizard, perhaps
as part of phppgadmin or Red Hat's graphical tool suite.  It could walk
you through all these considerations, get your okay on each nontrivial
semantic change, and then apply all the operations within a single
transaction.  AFAIK we have all the ALTER functionality needed to
support such a tool.

            regards, tom lane