Re: ALTER TABLE DROP COLUMN

On Mon, 9 Oct 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > hrmm .. mvcc uses a timestamp, no? is there no way of using that
> > timestamp to determine which columns have/haven't been cleaned up
> > following a crash? maybe some way of marking a table as being in a 'drop
> > column' mode, so that when it gets brought back up again, it is scan'd for
> > any tuples older then that date?
>
> WAL would provide the framework to do something like that, but I still
> say it'd be a bad idea. What you're describing is
> irrevocable-once-it-starts DROP COLUMN; there is no way to roll it back.
> We're trying to get rid of statements that act that way, not add more.

Hrmmmm ... this one I can't really argue, or, at least, can't think of
anything right now :(

> I am not convinced that a 2x penalty for DROP COLUMN is such a huge
> problem that we should give up all the normal safety features of SQL
> in order to avoid it. Seems to me that DROP COLUMN is only a big
> issue during DB development, when you're usually working with
> relatively small amounts of test data anyway.

Actually, I could see DROP COLUMN being useful in a few other places
... recently, I spent several hours re-structuring a clients database that
had been built by someone else who didn't know what 'relational' means in
RDBMS ... or how about an application developer that decides to
restructure their schema's in a new release and provides an 'upgrade.sql'
script that is designed to do this?

A good example might be the UDMSearch stuff, where you have tables that
are quite large, but they decide that they want to remove the 'base URL'
component' of one table and put it into another table? a nice update
script could go something like (pseudo like):

ADD COLUMN base_url int;
INSERT INTO new_table SELECT base_url_text FROM table;
DROP COLUMN base_url_text;

That would make for a very painful upgrade process if I have to go through
the trouble of upgrading my hardware to add more space ...