Re: [HACKERS] Well, then you keep your darn columns

At 12:13 PM 1/24/00 -0600, Ed Loehr wrote:
>Tom Lane wrote:
>
>> Let's see: DROP COLUMN would have to mark the column invisible, remove
>> any associated constraints (particularly NOT NULL) and indexes, and
>> it'd be done.  The parser would then have to ignore the column when
>> doing column name lookups or expansion of '*', and it would have to
>> insert a NULL value for the column when transforming INSERT or UPDATE.
>> And that'd be just about it.  I like it.
>
>How would you handle multi-column indices that included the column
>being dropped?  E.g.,
>
>    create unique index foobar on mytable(foo,bar);
>
>where the 'bar' column is then dropped...
>
>Dropping all of that index would seem to be problematic.

Hmmm...dropping the index is what Oracle does, or so claims their
documentation.  It makes sense because getting rid of "bar"
may well mean that the uniquness constraint will no longer be
satisfied, right?  In fact, odds are it won't for a multi-column
index.  Anyway, Oracle drops all indices which reference the 
column.

Also, it turns out that "drop column" in Oracle does reclaim the
space occupied by the data, but there's a "set unused" variant that
does EXACTLY what's being talked about - i.e. marks the column as
unused and makes it invisible to queries.

Interesting.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.