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

At 12:53 PM 1/24/00 -0500, Tom Lane wrote:
>"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> My idea is as follows.
>
>> 1)add a visibile/invisible flag to pg_attribute
>> 2)DROP COLUMN marks the column as invisible
>> 3)user interface ignores the columns which are marked invisible
>> 4)heap_formtuple() etc treats the column as NULL internally
>
>That could be a really good idea.

I'd been thinking along these lines as a possibility, too, but 
didn't want to rock the boat any more than I've done already.  Now
that it's on the table, though...

>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.

Yep.  

>The only drawback of this scheme is that the space occupied by the
>deleted column wouldn't go away immediately (in any given tuple,
>it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
>you could construe that as a feature --- you don't have to wait around
>for a DROP COLUMN to finish.  Anyone who did want to reclaim space
>immediately could do
>    UPDATE table SET someothercolumn = someothercolumn;
>followed by a VACUUM.

Or perhaps vacuum could be made smart enough to remove deleted columns?
Perhaps optionally?

>  But I bet a lot of people would be just as happy to let it happen
>  in background.

Well...one reason why this idea arose in my mind is because I'm actually
doing it for a web-based table definer that's part of the (tiresomely
overmentioned) arsDigita Community System.  It lets you add and drop
columns via the web (you're actually defining auxillary tables used
by some canned code so it's not quite as weird as it may sound).  I
simply enforce that user-defined table names begin with a character,
and when a user "drops" a column rename it to an "illegal" name in
a special form, which the rest of the web interface simply doesn't
display.  The user can't tell that the columns not really dropped,
maintaining the illusion that the system's 100% compatible with the
Oracle-based version.

It works just great for this application, though it's a royal kludge.



- 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.