> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
