> IMHO, the current ADD COLUMN mechanism is a hack. Besides requiring
> redundant on-disk data (t_natts), it isn't SQL compliant (because
> default values or NOT NULL can't be specified), and depends on
> a low-level kludge (that the storage system will return NULL for
> any attnums > the # of the attributes stored in the tuple).
>
> While instantaneous ADD COLUMN is nice, I think it's counter-
> productive to not take advantage of a storage space optimization
> just to preserve a feature that is already semi-broken.
I actually started working on modifying ADD COLUMN to allow NOT NULL and
DEFAULT clauses. Tom's idea of having col > n_atts return the default
instead of NULL is cool - I didn't think of that. My changes would have
basically made the plain add column we have at the moment work instantly,
but if they specified NOT NULL it would touch every row. That way it's up
to the DBA which one they want (as good HCI should always do).
However, now that my SET/DROP NOT NULL patch is in there, it's easy to do
the whole add column process, just in a transaction:
BEGIN;
ALTER TABLE foo ADD bar int4;
UPDATE foo SET bar=3;
ALTER TABLE foo ALTER bar SET NOT NULL;
ALTER TABLE foo SET DEFAULT 3;
ALTER TABLE foo ADD FOREIGN KEY (bar) REFERENCES (noik);
COMMIT;
With the advantage that you have full control over every step...
Chris